Using PDO queries in place of mysql i.e deprecated

Question

As we all know Mysql has been removed in php v7.0 i am trying to use pdo for fetching data (server side) using datatables using the following example but its in mysql need it in pdo: CODE:

COLUMNS:

/* Array of database columns which should be read and sent back to DataTables. Use a space where
 * you want to insert a non-database field (for example a counter or static image)
 */
$aColumns = array( 'first_name', 'last_name', 'position', 'office', 'salary' );

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = "datatables_demo";

Creating PDO CONNECTION:

$db_host = "localhost";
$db_name = "sadad";
$db_user = "root";
$db_pass = "root";

try{
    $db_con = new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_pass);
    $db_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e){
    echo $e->getMessage();
}

THE FOLLOWING CODE:

$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
    $sLimit = "LIMIT ".$db_con->quote( $_GET['iDisplayStart'] ).", ".
        $db_con->quote( $_GET['iDisplayLength'] );
}


/*
 * Ordering
 */
if ( isset( $_GET['iSortCol_0'] ) )
{
    $sOrder = "ORDER BY  ";
    for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
    {
        if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
        {
            $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                ".$db_con->quote( $_GET['sSortDir_'.$i] ) .", ";
        }
    }

    $sOrder = substr_replace( $sOrder, "", -2 );
    if ( $sOrder == "ORDER BY" )
    {
        $sOrder = "";
    }
}


/* 
 * Filtering
 * NOTE this does not match the built-in DataTables filtering which does it
 * word by word on any field. It's possible to do here, but concerned about efficiency
 * on very large tables, and MySQL's regex functionality is very limited
 */
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
    $sWhere = "WHERE (";
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        $sWhere .= $aColumns[$i]." LIKE '%".$db_con->quote( $_GET['sSearch'] )."%' OR ";
    }
    $sWhere = substr_replace( $sWhere, "", -3 );
    $sWhere .= ')';
}

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
    if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
    {
        if ( $sWhere == "" )
        {
            $sWhere = "WHERE ";
        }
        else
        {
            $sWhere .= " AND ";
        }
        $sWhere .= $aColumns[$i]." LIKE '%".$db_con->quote($_GET['sSearch_'.$i])."%' ";
    }
}
$my = str_replace(" , ", " ", implode(", ", $aColumns));

/*
 * SQL queries
 * Get data to display
 */
$sQuery = $db_con->query("SELECT {$my} FROM   {$sTable} {$sWhere} {$sOrder} {$sLimit}")->fetchAll(); 
//$rResult = ( $sQuery );

/* Data set length after filtering */
$sQuery = "
    SELECT FOUND_ROWS()
";
//$rResultFilterTotal = $sQuery;
$aResultFilterTotal = $sQuery;
$iFilteredTotal = $aResultFilterTotal[0];

/* Total data set length */
$sQuery = "
    SELECT COUNT(".$sIndexColumn.")
    FROM   $sTable
";
$rResultTotal = $db_con->query( $sQuery ) or die(mysql_error());
$aResultTotal = $rResultTotal->fetchAll();
$iTotal = $aResultTotal[0];


/*
 * Output
 */
$output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData" => array()
);

while ( $aRow = $rResult->fetchAll() )
{
    $row = array();
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( $aColumns[$i] == "version" )
        {
            /* Special output formatting for 'version' column */
            $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
        }
        else if ( $aColumns[$i] != ' ' )
        {
            /* General output */
            $row[] = $aRow[ $aColumns[$i] ];
        }
    }
    $output['aaData'][] = $row;
}

echo json_encode( $output );

ERROR: but i am getting the error ,i am unaware what to change in the above ,kind of getting started in pdo,an updated answer with code would be appreciated:

UPDATED THE CODE NOW RECEIVING FOLLOWING ERROR

[28-Aug-2018 16:58:39 UTC] PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''asc' LIMIT '0', '50'' at line 2' in C:\MAMP\htdocs\backend\my.php:131
Stack trace:
#0 C:\MAMP\htdocs\backend\my.php(131): PDO->query('SELECT first_na...')
#1 {main}
  thrown in C:\MAMP\htdocs\backend\my.php on line 131
**ERROR:**

If you're switching to PDO (great!), you need to switch _everything_ to PDO. You can't mix it with mysql. — Don't Panic, Aug 28 '18 at 16:30
You can't have any `mysql_` code. You need to choose `mysqli` or `pdo` they aren't the same. `mysqli` also isn't just interchangeable with `mysql_`. — user3783243, Aug 28 '18 at 16:31
@Don'tPanic yeah i know that but how can i do that i dont know what to place in the place of mysql ,currently a beginner in pdo — MR_AMDEV, Aug 28 '18 at 16:32
http://php.net/manual/en/pdo.query.php would be the same...but that is not a good function to use. To really take advantage of what PDO offers you should use `prepare` and `execute` and bind your parameters. — user3783243, Aug 28 '18 at 16:33
Oh, I see. Sorry, I didn't realize, and I thought you'd misunderstood that. Well, there's quite a bit of code there to convert to PDO. Is there any specific part of it you're having trouble with? — Don't Panic, Aug 28 '18 at 16:33
The closest equivalent to `mysql_real_escape_string()` in PDO is `$db_con->quote()`. But you should switch to prepared statements. — Barmar, Aug 28 '18 at 16:34
Clarification: mysql is completely removed in PHP 7, it was deprecated several years earlier. — Barmar, Aug 28 '18 at 16:35
what should i change `mysql_real_escape_string` and `$rResult = mysqli_query( $sQuery, $db_con ) or die(mysql_error());` — MR_AMDEV, Aug 28 '18 at 16:35
No. You don't mix in `mysqli_*` atop. Only use PDO and `?` parameter binding. See [Prevent SQL injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Albeit you can keep `intval` conversions; they're perfectly fine. — mario, Aug 28 '18 at 16:37
@Barmar can you provide me an answer with the updated code ,because i always use prepare statement but as this is different in mysql i dont know how to change this into prepared statements — MR_AMDEV, Aug 28 '18 at 16:38
Seeing your existing code (looks like some CRUD abstraction already) and the optional LIMIT and ORDER BY attributes; you probably should pick an abstraction library atop PDO. It's less effort than managing optional params for `?` placeholders and `->execute([…])`, or typecast LIMIT scalars or whitelist column names (can't be bound really). — mario, Aug 28 '18 at 16:41
https://stackoverflow.com/a/60496/2191572 Has a handy sample of a parameterized PDO query which should be used instead of any escape string crutch. You will have to build your SQL dynamically and dynamically add parameters as needed and execute them together at the end. — MonkeyZeus, Aug 28 '18 at 16:50

Bill Karwin · Accepted Answer · 2018-08-28T18:45:25.863

I see a problem here:

$sWhere .= $aColumns[$i]." LIKE '%".$db_con->quote($_GET['sSearch_'.$i])."%' ";

The PDO::quote() function has a different output than the old deprecated mysql_real_escape_string() function.

Suppose your string is "O'Reilly" and you need the apostrophe character escaped.

mysql_real_escape_string("O'Reilly") will return:

    O\'Reilly

Whereas $db_con->quote("O'Reilly") will return:

    'O\'Reilly'

The quote() function adds string delimiters to the beginning and end of the string. This makes it work the same as the MySQL builtin function QUOTE()

So when you use PDO::quote() the way you're doing:

$sWhere .= $aColumns[$i]." LIKE '%".$db_con->quote($_GET['sSearch_'.$i])."%' ";

The resulting SQL clause looks like:

... WHERE mycolumn LIKE '%'search'%' ...

This is not going to work. You need it to be:

... WHERE mycolumn LIKE '%search%' ...

One solution is to add the % wildcards and then quote the result:

$sWhere .= $aColumns[$i]." LIKE ".$db_con->quote('%'.$_GET['sSearch_'.$i].'%') ;

Now it takes advantage of PDO::quote() adding the string delimiters.

By the way, I find all the . string concatenation makes PHP look awful. It's hard to write the code, and hard to read and debug it. I prefer using variables directly inside the string. And don't be afraid of doing the work in two lines of code. It's not always the best thing for code readability to stuff too much into one line.

$pattern = $db_con->quote("%{$_GET["sSearch_$i"]}%");

$sWhere .= "{$aColumns[$i]} LIKE {$pattern}";

But there's another way that is easier AND more secure.

Use query parameters instead of escaping/quoting.

$params[] = "%{$_GET["sSearch_$i"]}%";

$sWhere .= "{$aColumns[$i]} LIKE ?";

Then later...

$stmt = $db_con->prepare($sQuery);
$stmt->execute($params);
while ($row = $stmt->fetchAll()) {
    ...
}

Using parameters is simpler than using escaping/quoting. You don't have to mess around wondering if your quotes are balanced properly, because the parameter placeholder ? doesn't need string delimiters around it.

If you're learning PDO, I suggest doing some reading:

https://phpdelusions.net/pdo - a good tutorial.
http://php.net/pdo - reference documentation.

Both are important and useful. Reference docs are not as good for learning, but they're useful after you do the tutorial, to remind yourself of syntax, arguments, return values, etc. I've done plenty of PDO coding, but I open reference docs frequently.

Thanks for such a nice information! finally made it – MR_AMDEV Aug 28 '18 at 18:26 — MR_AMDEV, Aug 28 '18 at 18:26

Using PDO queries in place of mysql i.e deprecated

1 Answers1