Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:125833
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51.11.1\))
Subject: [PHP-DEV] Interest in a binary parameter type in PDO?
Message-ID: <193E3B72-AE8B-4686-8ACB-9CB68E3947BC@cmpct.info>
Date: Wed, 23 Oct 2024 12:36:31 -0300
To: php internals <internals@lists.php.net>
From: calvin@cmpct.info (Calvin Buckley)

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