Hi internals,
I'm currently working on an RFC to deal with binary parameters in PDO.
As outlined in the RFC, dealing with binary data is fraught with
database-specific issues. For example, a user asked me this morning
about such a quirk with an ODBC driver and PDO_ODBC that required some
workaround involving bin2hex(). Having native explicit support would
reduce a lot of this confusion.
RFC: https://wiki.php.net/rfc/pdo_binary
I would appreciate discussion and in particular, the implications with
databases you're familiar with (I'm not as familiar with MySQL and
Postgres, so I'd appreciate feedback there), as well as narrowing down
how it should be handled on the PDO data type side, before it comes
down to a vote. I would want this hashed out before coming to a vote,
as doing so would allow me to refine the code for it.
Regards,
Calvin
Hi Calvin,
Had a chance to refresh my memory on PDO, so a few points from reading the
RFC against the current drivers.
-
PARAM_LOB with a string is already binary in some drivers, not all:
pdo_pgsql (binary format natively, PQescapeByteaConn() when emulating) and
pdo_sqlite (sqlite3_bind_blob()). pdo_mysql is binary only when emulating;
natively it binds like PARAM_STR. pdo_odbc uses SQL_LONGVARBINARY only when
SQLDescribeParam() fails. Postgres needs a stream only on fetch, not input.
Please spell out what PARAM_BINARY adds over PARAM_LOB per driver. -
The fallback leaves each driver to choose STR or LOB, and STR corrupts
data: pdo_pgsql reads text-format parameters as C strings (truncated at the
first NUL), pdo_mysql loses the _binary introducer. A per-driver table
(native bind, emulated quote, fetch) would pin this down. -
Neither representation degrades safely. An unknown type 6 falls to text
in the bundled drivers and pdo_oci, IM001 in pdo_ibm, and a fatal error in
pdo_sqlsrv. A flag only survives where the type is masked with
PDO_PARAM_TYPE(); the emulated-prepare parser, PDO::quote(), the pgsql
quoter, fetch_value() and pdo_sqlsrv all compare the raw value. The flag
works only if core masks it before quoting and fetch overrides; the RFC
should require that, and define what PARAM_INT | PARAM_BINARY does
(ValueError, I'd say). -
The ODBC hex case is a driver issue: odbc_stmt.c fetches binary columns
as SQL_C_CHAR. Choosing SQL_C_BINARY by column type fixes it without a new
constant (it changes bug80783.phpt's output, as the POC already does). Raw
fetch of character columns (EBCDIC/UTF-16) is the genuinely new capability.
The dblib quoting case overlaps your #10343. -
Changing getColumnMeta()'s pdo_type breaks strict comparisons either way
(6 or STR | BINARY). A separate key would avoid that. -
Nits: PDO::PARAM_STRING should be PDO::PARAM_STR; the doodles are closed
with a 2026-01-01 closeon; the POC dblib quoter uses an exact paramtype ==
PDO_PARAM_BINARY check, so any added flag skips it.
--
Ilia Alshanetsky
Technologist, CTO, Entrepreneur
E: ilia@ilia.ws
T: @iliaa
B: http://ilia.ws
Hi Calvin,
Had a chance to refresh my memory on PDO, so a few points from reading the RFC against the current drivers.
Thanks for taking a look at this. It's getting late here, I'll try to address some of this stuff, things that require a bit more research I'll aim to do over next week.
RFC: https://wiki.php.net/rfc/pdo_binary
- PARAM_LOB with a string is already binary in some drivers, not all: pdo_pgsql (binary format natively, PQescapeByteaConn() when emulating) and pdo_sqlite (sqlite3_bind_blob()). pdo_mysql is binary only when emulating; natively it binds like PARAM_STR. pdo_odbc uses SQL_LONGVARBINARY only when SQLDescribeParam() fails. Postgres needs a stream only on fetch, not input. Please spell out what PARAM_BINARY adds over PARAM_LOB per driver.
I had left an open issue for PARAM_LOB inconsistencies, but I think it needs addressed further up front. I'll also try to take a look at making a a table for it.
I did mention PARAM_LOB and binary conflation in the intro. I think it may be possible to use PARAM_LOB for most cases, but it might make some more niche cases complicated instead (i.e. if PARAM_LOB would return a stream resource representing a LOB), and it'd be perhaps confusingly named for the binary string use case. If people feel that PARAM_LOB is sufficient for binary strings in spite of that, I'd be OK with making changes for drivers to make that more consistent with binaries.
- The fallback leaves each driver to choose STR or LOB, and STR corrupts data: pdo_pgsql reads text-format parameters as C strings (truncated at the first NUL), pdo_mysql loses the _binary introducer. A per-driver table (native bind, emulated quote, fetch) would pin this down.
For drivers that use emulated prepares, perhaps we could check string contents instead and use binary quoting based on that, but that feels slow and what characters need binary quoting would differ per driver.
- Neither representation degrades safely. An unknown type 6 falls to text in the bundled drivers and pdo_oci, IM001 in pdo_ibm, and a fatal error in pdo_sqlsrv. A flag only survives where the type is masked with PDO_PARAM_TYPE(); the emulated-prepare parser, PDO::quote(), the pgsql quoter, fetch_value() and pdo_sqlsrv all compare the raw value. The flag works only if core masks it before quoting and fetch overrides; the RFC should require that, and define what PARAM_INT | PARAM_BINARY does (ValueError, I'd say).
Marking this a breaking change for drivers seems reasonable then.
- The ODBC hex case is a driver issue: odbc_stmt.c fetches binary columns as SQL_C_CHAR. Choosing SQL_C_BINARY by column type fixes it without a new constant (it changes bug80783.phpt's output, as the POC already does). Raw fetch of character columns (EBCDIC/UTF-16) is the genuinely new capability. The dblib quoting case overlaps your #10343.
For ODBC, it'll only bind SQL_C_BINARY for columns with is_unicode set (so, some Windows specific behaviour), for params it does so unconditionally for binary (but not blob) columns. The latter behaviour could be improved here regardless of this RFC.
- Changing getColumnMeta()'s pdo_type breaks strict comparisons either way (6 or STR | BINARY). A separate key would avoid that.
I suspect a BC break for things relying on getColumnMeta would happen regardless of strategy. We could try something like pdo_sqlite's blob key perhaps if we really want to avoid it.
- Nits: PDO::PARAM_STRING should be PDO::PARAM_STR; the doodles are closed with a 2026-01-01 closeon; the POC dblib quoter uses an exact paramtype == PDO_PARAM_BINARY check, so any added flag skips it.
--
Ilia Alshanetsky
Technologist, CTO, Entrepreneur
E: ilia@ilia.ws
T: @iliaa
B: http://ilia.ws
Hi Calvin,
Here are a couple of follow-up notes.
- The fallback leaves each driver to choose STR or LOB, and STR
corrupts data: pdo_pgsql reads text-format parameters as C strings
(truncated at the first NUL), pdo_mysql loses the _binary introducer. A
per-driver table (native bind, emulated quote, fetch) would pin this down.For drivers that use emulated prepares, perhaps we could check string
contents instead and use binary quoting based on that, but that feels slow
and what characters need binary quoting would differ per driver.
Content sniffing can't settle this. A bytea or varbinary value can be valid
text, and text can contain a NUL, so the driver has to be told. It also
doesn't help native prepares: pdo_pgsql sends text-format parameters as C
strings, so they truncate at the first NUL regardless of quoting. That's
the strongest argument for the RFC imho.
- Neither representation degrades safely. An unknown type 6 falls to
text in the bundled drivers and pdo_oci, IM001 in pdo_ibm, and a fatal
error in pdo_sqlsrv. A flag only survives where the type is masked with
PDO_PARAM_TYPE(); the emulated-prepare parser, PDO::quote(), the pgsql
quoter, fetch_value() and pdo_sqlsrv all compare the raw value. The flag
works only if core masks it before quoting and fetch overrides; the RFC
should require that, and define what PARAM_INT | PARAM_BINARY does
(ValueError, I'd say).Marking this a breaking change for drivers seems reasonable then
The driver break decides between using a type versus a flag. A new type 6
is a fatal error in pdo_sqlsrv and IM001 in pdo_ibm until each driver ships
an update. A flag that core masks before quoting allows fetch to fall back
to STR/LOB in any driver that ignores it. I'd go with the flag, and
reject combinations
with INT/BOOL/NULL with a ValueError.
- Changing getColumnMeta()'s pdo_type breaks strict comparisons either
way (6 or STR | BINARY). A separate key would avoid that.I suspect a BC break for things relying on getColumnMeta would happen
regardless of strategy. We could try something like pdo_sqlite's blob key
perhaps if we really want to avoid it.
pdo_sqlite already marks blobs in getColumnMeta()'s "flags" array, which is
a good precedent for leaving pdo_type alone.
--
Ilia Alshanetsky
Technologist, CTO, Entrepreneur
E: ilia@ilia.ws
T: @iliaa
B: http://ilia.ws
Sorry for the later response and not doing the follow-ups on the previous email, I got incredibly busy with PHP stuff at work... you can guess why.
Content sniffing can't settle this. A bytea or varbinary value can be valid text, and text can contain a NUL, so the driver has to be told. It also doesn't help native prepares: pdo_pgsql sends text-format parameters as C strings, so they truncate at the first NUL regardless of quoting. That's the strongest argument for the RFC imho.
Agreed a binary param type is unavoidable for quoters.
The driver break decides between using a type versus a flag. A new type 6 is a fatal error in pdo_sqlsrv and IM001 in pdo_ibm until each driver ships an update. A flag that core masks before quoting allows fetch to fall back to STR/LOB in any driver that ignores it. I'd go with the flag, and reject combinations with INT/BOOL/NULL with a ValueError.
We have shipped breaking changes in PDO before (i.e. zend_stringification), so expecting these drivers to update for newer releases isn't unreasonable. But easing compatibility is another small point in favour of the flag.
pdo_sqlite already marks blobs in getColumnMeta()'s "flags" array, which is a good precedent for leaving pdo_type alone.
Sounds like we can just make it a standard part of the array's expected values then.