# Interest in a binary parameter type in PDO?

- Thread: https://externals.io/message/125833
- Original archive: https://news-web.php.net/php.internals/125833
- Started by Calvin Buckley on 2024-10-23T15:36:31+00:00
- Messages: 5
- Reply by email to internals@lists.php.net
- More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt

---

## #125833 — Calvin Buckley — 2024-10-23T15:36:31+00:00

URL: https://externals.io/message/125833#125833

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

[issue]: https://github.com/php/php-src/issues/11462
[pr]: https://github.com/php/php-src/pull/11674

---

## #127663 — Gina P. Banyard — 2025-06-15T14:52:11+00:00

URL: https://externals.io/message/125833#127663 · In reply to: #125833

> 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
> 
> [issue]: https://github.com/php/php-src/issues/11462
> [pr]: https://github.com/php/php-src/pull/11674

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

---

## #127674 — Matteo Beccati — 2025-06-16T13:29:16+00:00

URL: https://externals.io/message/125833#127674 · In reply to: #127663

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.


Cheers


> 
> 
> > […]
> 
> 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

-- 
Matteo Beccati

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


-- 
Matteo Beccati

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

---

## #127677 — Calvin Buckley — 2025-06-16T14:21:38+00:00

URL: https://externals.io/message/125833#127677

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

---

## #127680 — Matteo Beccati — 2025-06-16T14:35:53+00:00

URL: https://externals.io/message/125833#127680 · In reply to: #127677

Hi Calvin,


> 
> > […]
> 
> 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/