PHP PDO问题具有经过整理的ORDER BY字段 [英] PHP PDO issue with sanitised ORDER BY fields

问题描述

我有一个"ajax脚本/处理程序"，可以将许多产品类别返回到我的jqGrid. sql最终看起来像这样:

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();

现在，我已经遇到了'$ start'的问题，因为PDO显然与LIMIT有问题，因此我必须明确地将其设置为(int)才能使上述工作正常.我的下一个问题是引用了ORDER BY字段.如何停止报价?我可以直接传递'$ sidx'和'$ sord'值而不进行消毒，但这很危险. 现在，上面的SQL生成为:

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

推荐答案

也许最好的解决方案是直接传递$sidx和$sord值而不进行消毒，但需要先进行验证.喜欢:

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();

这篇关于PHP PDO问题具有经过整理的ORDER BY字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！