0
print_r($sql);
echo "\n";
print_r($sql_params);
$result = db_query($sql, $sql_params); // Error happening here

Output:

select SQL_CALC_FOUND_ROWS *  from rocdocs_database_1318520218 where 1=1  order by ? ? limit ?, ?
Array
(
    [0] => c5
    [1] => desc
    [2] => 0
    [3] => 50
)

According to the documentation I can used ordered parameters by using an array and ? marks, but it seems to be erroring. Any ways to debug this? I have installed devel, but it doesn't show the query.

Chris Muench
  • 17,444
  • 70
  • 209
  • 362

1 Answers1

0

You can't use placeholders for anything that is "sql structure" like sort definitions, table/column names and so on. This is impossible.

If you need dynamic order by definitions, use db_select() and then orderBy(). Make sure to validate what you pass in through that.

Berdir
  • 6,881
  • 2
  • 26
  • 38
  • Do you know if there is a way to see raw sql? – Chris Muench Oct 24 '11 at 16:32
  • One way is to use something along the lines of `drupal_set_message((string)$query));`, if it is a db_select()/SelectQuery or anothe query builder. For db_query(), the query is sent to the server as-is except replacing {table} placeholders. The placeholder replacement is done within MySQL, the query is already parsed before the placeholders are inserted, that's what makes it very secure. And the reason why stuff like this doesn't work anymore and did in Drupal 6, where the replacement was done using regular expressions within PHP which didn't know about the context of placeholders. – Berdir Oct 24 '11 at 18:52
  • The most convenient way to see all actually executed queries with and without placeholders (although there the placeholder replacement is again done in PHP) is the query log from devel.module, which prints all executed queries below the page. – Berdir Oct 24 '11 at 18:54
  • Why is it not possible to use placeholders for limits and order by? You can do this in regular PDO? – Chris Muench Oct 24 '11 at 19:19
  • No you can't, not with prepared statements. And Drupal always uses prepared statements. See http://stackoverflow.com/questions/2542410/how-do-i-set-order-by-params-using-prepared-pdo-statement for example. – Berdir Oct 25 '11 at 09:14