Interest in a binary parameter type in PDO? View as Markdown

Hi internals,

I've been debugging some recent issues users have had, as well as
discussing some related ones. I've come to the conclusion there's a lot
issues with applications trying to access binary data via PDO drivers.

For example

• Binary data when quoting in SQL Server can easily get mangled, as the
  quoter can't handle binary data and truncates the string. It's
  possible other drivers are like this too.
• ODBC drivers can be unpredictable behaviour wise when binding binary
  data as SQL_C_CHAR (like what the get_col handler does). In the case
  of one driver, it will hex encode the data and can confuse the size
  handling in the get_col handler.

I've thought about overriding the LOB semantics for quick fixes, but
this isn't the right solution, as a LOB and binary data are orthogonal;
i.e. CLOBs are LOB strings and VARBINARY doesn't have LOB semantics. A
binary type (or modifier, i.e. like intl/natl, not to bikeshed tho) can
also be used w/ string columns in the database to possibly override i.e.
automatic charset encoding changes from an ODBC driver.

I had previously made an issue and PR (links below), but I'm discussing
on internals to revitalize the conversation after being motivated by
recent issues. I'd like to hear from users, database extn. developers,
and userland database library extension developers if this is the right
approach, and if I should move forward dusting off the PR and probably
writing an RFC. Said PR includes the changes needed for DBLIB, ODBC, and
SQLite driver wise, as well as some tests. MySQL/Postgres/Firebird
support would need to be added.

~calvinb

Sorry for the late reply,

I think this makes a lot of sense and would be in favour of including this in PDO.

Best regards,

Gina P. Banyard

Hi Calvin,

Hi,

as mentioned in https://github.com/php/php-src/pull/15949 I still think that PARAM_LOB can safely be used as identifier for binary data, as pdo_mysql and pdo_pgsql do.

I think PARAM_LOB and PARAM_LOB are orthogonal.

Using i.e. ODBC as an example:

       +----------+------------+
       |  string  |   binary   |

+---------+----------+------------+
| LOB | SQL_CLOB | SQL_BLOB |
+---------+----------+------------+
| not LOB | SQL_CHAR | SQL_BINARY |
+---------+----------+------------+

Especially since LOBs may have different semantics (i.e. needing to be
treated as a stream).

In an ideal world, yes.

But I fear that ship has sailed, as the most used drivers have been
using PARAM_LOB for binary fields since PDO has been designed, as far as
I know.

Perhaps we can make that clear by aliasing PARAM_BINARY to PARAM_LOB and
fix the implementation for broken drivers?

Cheers

Matteo Beccati

Development & Consulting - http://www.beccati.com/