1

After years of reading it's time to ask first question :)

My problem is that after migrating the code from mySQLi to PDO we have got a problem as it seems PDO adds the apostrophes to the query.

PHP code goes like that:

$sort   = $_GET['sort']; << table column name (mySQL VARCHAR only columns)

....
$query = 'SELECT * FROM table WHERE xxx > 0';
$query .= ' ORDER BY :sort ASC ;';

$qry_result= $db->prepare($query);  
$qry_result->execute(array(':sort'=>$sort));

mysqli version went smoothly but now queries (mysql log file) looks like this:

SELECT * FROM table where xxx > 0 ORDER BY 'SORT_VAR_VALUE' ASC;
                                           ^  2 problems  ^

So the table is NOT sorted, as sort order (from mySQL point of view) is wrong.

phpinfo() does not get any results for search on "magic" nor "quotes" btw.

Any idea ??

Paul Paku
  • 360
  • 2
  • 16

3 Answers3

2

by default pdo binds values as strings.

To fix this you will want to check that the column is actually a valid name and then add it to the query, you can do it the following way:

function validName($string){
    return !preg_match("/[^a-zA-Z0-9\$_\.]/i", $string);
}

if(validName($sort)){
    $db->prepare("SELECT * FROM table where xxx > 0 ORDER BY $sort ASC");
}
Get Off My Lawn
  • 34,175
  • 38
  • 176
  • 338
  • 1
    This won't work because it's not possible to bind anything other then variables in the where statement. – Guillermo Jan 16 '15 at 01:15
  • Will check but :sort is always varchar column – Paul Paku Jan 16 '15 at 01:16
  • It doesn't matter which type :sort is of. It won't work like said in this answer. – Guillermo Jan 16 '15 at 01:18
  • I have done it hundreds of times. Binding in the order clause works just fine. – Get Off My Lawn Jan 16 '15 at 01:18
  • 2
    Yup your right im stupid I was thinking of the `limit` clause. – Get Off My Lawn Jan 16 '15 at 01:20
  • 1
    @Guillermo: But you *can* bind values into expressions in the `SELECT` list. For example, it's perfectly valid to do `SELECT col + :val AS newcol FROM...`. It's also possible to bind values into expressions in the `GROUP BY` clause as well. The real issue is that you cannot supply **identifiers** or **keywords** or other parts of the SQL text as bind values. Values provided through bind parameters are *never* seen as SQL text, they are **values only**, just like string literals, date literals and integer literals are **values**. – spencer7593 Jan 16 '15 at 23:48
2

The placeholders in PDO statements are for values only. If you want to add actual SQL to the query you need to do it another way.

First, you should sanitize $sort and surround it with backticks in the query.

$sort = preg_replace('/^[a-zA-Z0-9_]/', '', $sort);

Then you could double quote the query string and PHP will replace $sort with it's value for you:

$query = "SELECT * FROM table WHERE xxx > 0 ORDER BY `$sort` ASC";

Or you could replace it with preg_replace like so:

$query = 'SELECT * FROM table WHERE xxx > 0 ORDER BY `:sort` ASC';
$query = preg_replace('/:sort/', $sort, $query, 1);

I would use the preg_replace method because it allows you to reuse the query if you assign the results from preg_replace to another variable instead of overwriting the original variable.

BrokenBinary
  • 7,731
  • 3
  • 43
  • 54
  • Can't be ... it was like that for mySQL ..Thanks @BrokenBinary and others – Paul Paku Jan 16 '15 at 01:22
  • One more detail, sort fields are mostly varchars but I have to sort them as poor numbers (someone made wrong decision to put numbers into varchars). So my sorting string is like 'FIELD+0' to get proper sort. Will this method work with such a string like this. What about '+ sign ... – Paul Paku Jan 16 '15 at 12:14
  • To allow the `+` sign you would have to alter my sanitization regex to be `'/^[a-zA-Z0-9_+]/'` (notice the `+` at the end). Everything else in my answer should work fine with the `+` sign. Or you could just add the `+0` after you sanitize `$sort`. Either way would work. – BrokenBinary Jan 16 '15 at 17:57
-1

With PDO it's not possible to bind other things that variables in the WHERE statement. So you have to hard code the names of the columns you order by. See How do I set ORDER BY params using prepared PDO statement? or Can PHP PDO Statements accept the table or column name as parameter? for further explanations.

Community
  • 1
  • 1
Guillermo
  • 764
  • 6
  • 15