PDO :: bindValue()在使用嵌套SELECT的查询上失败 [英] PDO::bindValue() fails on query with nested SELECT

问题描述

我正在使用通过ODBC连接的MSSQL数据库.

I am using an MSSQL database connected through ODBC.

在具有嵌套SELECT语句的查询上使用PDO::bindValue()时，它将无法绑定嵌套SELECT内的值(主SELECT上没有问题). 这是一段失败的示例代码:

When using PDO::bindValue() on a query that has a nested SELECT statement, it fails to bind values within the nested SELECT (no problem on the primary SELECT). This is a piece of sample code that fails:

$stmt = $cmdb->prepare("SELECT ci.CI FROM dbo.cmdb_ci AS ci " .
                       "INNER JOIN dbo.cmdb_model AS m ON m.ModelID = ci.Modelid " .
                       "INNER JOIN dbo.cmdb_class AS c ON c.ClassID = m.Classid " .
                       "WHERE (c.ClassID = :classid) " .
                       "AND (ci.CI IN (SELECT ci2.CI " .
                                      "FROM dbo.cmdb_ci AS ci2 " .
                                      "INNER JOIN dbo.cmdb_ci_status AS st2 ON st2.CI = ci2.CI " .
                                      "WHERE st2.LocationID = :locationid))");
$stmt->bindValue("classid", 13);
$stmt->bindValue("locationid", 1011);
$stmt->execute();
if ($rows = $stmt->fetchAll())
    $stmt->closeCursor();
foreach ($rows as $row)
    echo $row["CI"];

我得到的错误是:

SQLSTATE [22018]:转换规范的字符值无效:206 [Microsoft] [SQL Server Native Client 11.0] [SQL Server]操作符类型冲突:文本与int不兼容(/builddir/build/中的SQLExecute [206] BUILD/php-5.4.16/ext/pdo_odbc/odbc_stmt.c:254)

SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][SQL Server Native Client 11.0][SQL Server]Operand type clash: text is incompatible with int (SQLExecute[206] at /builddir/build/BUILD/php-5.4.16/ext/pdo_odbc/odbc_stmt.c:254)

如果我在bindValue()上省略了:locationid"并直接在查询中插入'1011'，则调用将成功完成，并且没有错误，并且结果正确.

If I leave out the bindValue() for ":locationid" and insert '1011' directly into the query, the call completes without errors and with the correct results.

这是PDO中的错误，还是我必须以不同的方式调用bindValue()?

Is this a bug in PDO, or do I have to call bindValue() differently?

推荐答案

正如(我从评论中读到的)告诉bindValue所传递的值是整数没有不能解决问题.

As (I read from comments) telling bindValue that the value passed is an integer doesn't solve the issue...

$stmt->bindValue( "locationid", 1011, PDO::PARAM_INT );

...我认为出于某种原因(

...I assume that for some reason ( a bug in pdo_odbc ? ) the parameter enters the query as a string no matter what you specify as third parameter on bindValue.

然后，我建议通过将值强制转换为整数 到查询中来解决此问题.

I would then suggest to work this around by casting the value to integer in place into the query.

在最后一行:

"WHERE st2.LocationID = CAST( :locationid, int ) ))"

这不是很优雅，但是可能适合，直到找到pdo_odbc的修复程序/补丁为止.

This is not very elegant but may be suitable until you find a fix/patch for pdo_odbc

如果这还行不通，那么还有一个更加优雅的解决方案(当然，这是临时解决方案).

If even this doesn't work there is an even more un-elegant solution (to be intended as temporary fix of course).

您写道:

如果我将bindValue()留给:locationid"并直接在查询中插入'1011'，则调用将正确无误地完成，并获得正确的结果.

If I leave out the bindValue() for ":locationid" and insert '1011' directly into the query, the call completes without errors and with the correct results.

因此，您可以直接将位置ID 放入查询中.

So you may just place location id directly into the query.

假设位置ID 存储在$locationId中，则查询的最后一行变为:

Assuming location id is stored into $locationId then the last line of the query becomes:

"WHERE st2.LocationID = $locationId))");

由于这容易导致sql注入，因此必须事先清理(或验证)c4.

As this is prone to sql-injection $locationId has to be sanitized (or verified) beforehand.

该值必须是一个正整数，所以建议您使用一个更简单，更防弹的方法避免转义:检查$locationId仅由数字组成...

The value must be a positive integer so instead of escaping it I suggest an easier and bulletproof approach: check $locationId is made only of numbers...

if( ! ctype_digit( (string) $locationId ) ) {
    // location id is invalid
    // do not proceed !
}

这篇关于PDO :: bindValue()在使用嵌套SELECT的查询上失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！