PDO::bindValue() fails on query with nested SELECT

Question

I am using an MSSQL database connected through ODBC.

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"];

The error I get is:

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)

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.

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

Answer 1

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.

On the last line:

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

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).

You wrote:

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.

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

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

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

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

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 !
}

Answer 2

Could it be that the Location ID is actually stored in MSSQL as a string and not an int? Does it work when you add quotes? $stmt->bindValue("locationid", "1011", PDO::PARAM_STR);