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?

How about `$stmt->bindValue("locationid", 1011,PDO::PARAM_INT);` as the error says, you cast int with text. bindValue with no PDO:PARAM_ becomes a string e.g. text. — JustOnUnderMillions, Apr 04 '17 at 12:22
its not `$stmt->bindValue("classid", 13);` its `$stmt->bindValue(":classid", 13);` — Masivuye Cokile, Apr 11 '17 at 09:26
What operating system are you using? Can you share a sample schema with some data for testing? Which specific ODBC drivers are you using? There are a lot of bugs in PDO ODBC for ye olde PHP 5.4.16, such as [this one](http://stackoverflow.com/q/38255659), [this one](http://stackoverflow.com/q/39088156), [this one](http://stackoverflow.com/q/39044034), and [this one](http://stackoverflow.com/q/40116280). Your usage looks fine, but I'm not surprised it doesn't work. If you can provide the reproducible circumstances, preferably a docker image, then I'm sure someone can find a workaround for you. — Jeff Puckett, Apr 11 '17 at 14:45
Do casting types. `$stmt->bindValue("locationid", (int)@$locationId);` — , Apr 12 '17 at 05:55
default 3rd parameter for the `bindValue` is `PDO::PARAM_STR`, so you have to specify the `PDO::PARAM_INT` — Deadooshka, Apr 12 '17 at 11:22
@Konstantin: I specify an integer value (1011) in the call to bindValue(), I do not use a variable. It would be silly to cast 1011 to an int, since it already is. — Dandorid, Apr 12 '17 at 13:54
http://stackoverflow.com/questions/16532694/pdo-odbc-doesnt-work-whit-bind-values-nvarchar-and-text-are-incompatible-in-th in doubt, I'd move to the latest stable release of php — Sebas, Apr 14 '17 at 13:23

score 5 · Accepted Answer · edited May 23 '17 at 12:02

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

score 1 · Answer 2 · answered Apr 12 '17 at 10:15

1

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

answered Apr 12 '17 at 10:15

delboy1978uk

10,948
2
14
31

No, that does not work, since the LocationID value is defined as an INT (see above) so this would render it an invalid SQL statement. – Dandorid Apr 12 '17 at 13:52

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

2 Answers2

Linked