0

I have an "ajax script/handler" that returns a bunch of product categories to my jqGrid. The sql ends up looking like so:

$sql = 'SELECT * FROM product_categories ORDER BY :sidx :sord LIMIT :start , :limit';
$sth = $dbh->prepare($sql);
$sth->bindParam(':sidx', $sidx);
$sth->bindParam(':sord', $sord);
$sth->bindParam(':start', $start, PDO::PARAM_INT);
$sth->bindParam(':limit', $limit, PDO::PARAM_INT);
$sth->execute();

Now, I've already had an issue with '$start' because PDO apparently has an issue with LIMIT so I had to explicity set it as an (int) so the above could work. My next issue is that the ORDER BY fields are being quoted. How do I stop the quotes? I could just pass the '$sidx' and '$sord' values directly without sanitising them, but this would be dangerous. Right now, the above SQL gets generated as:

SELECT * FROM product_categories ORDER BY 'product_category' 'asc' LIMIT 0 , 10

When I actually need it to look like:

SELECT * FROM product_categories ORDER BY product_category asc LIMIT 0 , 10
SupaMonkey
  • 876
  • 2
  • 9
  • 25
  • Are you giving users the ability to enter their own order by column and direction?? – David Barker Oct 21 '12 at 15:38
  • jqGrid sends the above script handler the column order field and direction, eg: script.php?_search=false&nd=1350834280848&rows=10&page=1&sidx=product_category&sord=desc would mean the script would order by product_category field in descending direction – SupaMonkey Oct 21 '12 at 15:43
  • Have you seen this? http://stackoverflow.com/questions/2542410/how-do-i-set-order-by-params-using-prepared-pdo-statement – AlexP Oct 21 '12 at 16:03
  • I have seen that Alex, but it doesnt help. The first answer tells you that you cannot bind column names; but then how would I sanitise the variables? The second answer tells you to use the variable directly, but in my case, thats dangerous. I need to manually sanitise $sidx and $sord and use them in the $sql directly - however, how do I sanitise "manually"? Is addslashes enough? – SupaMonkey Oct 21 '12 at 16:09
  • Just did some research and addslashes ISNT enough; so need to manually sanitise it some other way? mysql_real_escape_string only works with a valid mysql_connect preceding it; and manual pdo::quote() adds quotes anyway, so that doesnt help either! – SupaMonkey Oct 21 '12 at 16:17

1 Answers1

1

Maybe the best solution will be pass the $sidx and $sord values directly without sanitising them, but with validation before. Like:

$sidx = (!in_array($sidx,array('name','slug','description'))) ? 'name' : $sidx;
$sord = (!in_array($sord,array('asc','desc'))) ? 'asc' : $sord;
$sql = 'SELECT * FROM product_categories ORDER BY '.$sidx.' '.$sord.' LIMIT :start , :limit';
$sth = $dbh->prepare($sql);
$sth->bindParam(':start', $start, PDO::PARAM_INT);
$sth->bindParam(':limit', $limit, PDO::PARAM_INT);
$sth->execute();
Mikhail
  • 2,542
  • 4
  • 29
  • 40
  • That could be a solution, yes, but because I use this 'template of code' over many database tables; it would be a lot more work to get this setup each time. I dont understand why there isnt just a normal mysql_real_escape_string type function for PDO! Im starting to wish I stuck with MySQLi! – SupaMonkey Oct 22 '12 at 05:32
  • 1
    `mysql_real_escape_string()` doesn't used for these purposes. If I pass `sidx` like `name ASC; DROP TABLE product_categories;` `mysql_real_escape_string()` doesn't protect your query. If you use variables in the control constructs you should check them before like above. If you will find any other solution please let me know – Mikhail Oct 22 '12 at 06:23
  • Thanks, I'll have to do it like this until something better comes along - if it ever does :) – SupaMonkey Oct 22 '12 at 08:24