Binary column binding is broken with EMULATE_PREPARES

[david-garcia-garcia]

Something similar was originally reported (and fixed) here:

#63

See attached repro script (similar to the one posted in #121).

http://pastebin.com/GYNtYewC

Current result:

Uncaught PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Expected result:

Hello world!

The issue is again with EMULATE_PREPARES, if you comment out the following line:

$pdo_options[PDO::ATTR_EMULATE_PREPARES] = TRUE;

The script will work as expected.

[meet-bhagdev]

@david-garcia-garcia Thanks for reporting this. We are looking into it.

[david-garcia-garcia]

Any update on this? I am comming accross this issue all the time, it makes it impossible to mix EMULATE_PREPARES with binary columns....

[meet-bhagdev]

@david-garcia-garcia Thanks for pinging the thread. We are able to reproduce this and are looking into fixing this. Stay tuned for updates.

[Hadis-Knj]

@david-garcia-garcia We are looking into this issue and #92, I have a couple of questions about emulate prepare, and would appreciate if you can help me to understand how emulate prepare works in other PDO drivers.

• Are you able to set the encoding for parameters with emulated prepare in other PDO Drivers? As PDO replaces the placeholders with values itself.
• do other PDO drivers with emulated prepare provide $driver_options in PDOStatement::bindParam, if so what are those options?
• Are you able to use bind param without encoding options, and encode the values before using them in bindParam?

[david-garcia-garcia]

help me to understand how emulate prepare works in other PDO drivers.

I'll do my best, but the truth is that the PDO for MS SQL is the only one I've been involved with - ever. To my understanding, switching on EMULATE_PREPARES should only impact how the underlying sql statements are built and sent to the database engine, but not how the developer interacts with the PDO driver itself.

Are you able to set the encoding for parameters with emulated prepare in other PDO Drivers? As PDO replaces the placeholders with values itself.

Looks like MySQL will only let you do this at a driver level, and not for every single parameter that you bind:

http://stackoverflow.com/questions/10783997/php-pdo-utf8-and-mysql-not-playing-ball

So the answer would be NO, other PDO drivers do not have the ability to set encoding when binding parameters.

As per this post:

https://blogs.msdn.microsoft.com/brian_swan/2011/02/24/sql-server-driver-for-php-connection-options-characterset/

Looks like the MSSQL PDO driver is much more powerful in that sense.

I found this which might help (or not):

https://bugs.php.net/bug.php?id=72414

do other PDO drivers with emulated prepare provide $driver_options in PDOStatement::bindParam, if so what are those options?

See my previous answer. Looks like not at all. The driver_options parmeter is used to pass along any database driverr-specific options, so there is no clear specification about how this should work. I was not able to find any dirver options usage for neither MySQL or Postgre.

Are you able to use bind param without encoding options, and encode the values before using them in bindParam?

I've tried this for a while without success, but I guess it should be possible.

I know these responses are not very helpful. All in all, I understand that no changes should be done by the developer when switching emulate_prepares on, so you should try and match the behaviour in that situation to the one you get when emualte_prepares is off.

BTW, and to have some context on this, I'm using emulate_prepares mainly to bypass the 2100 parameter limit that is not present in other databases.

[Hadis-Knj]

Thanks a lot@david-garcia-garcia, your answer shed light to the use of emulate prepare feature we'll continue investigation on this issue and will get back to you.

[Hadis-Knj]

@david-garcia-garcia we fixed this issue in 4.1.9 release. could you give it a try and confirm?

[meet-bhagdev]

Closing as we fixed it. @david-garcia-garcia feel free to reopen if you still need help 👍