PDO binary parameter support View as Markdown

Introduction

Currently, PDO drivers behave inconsistently and unpredictably dealing with binary data. Sometimes, they require special quoting rules, or special binding behaviour, that is currently not possible to do from userland or is very unergonomic to do.

For example:

• SQL Server (used via i.e. PDO_DBLIB) requires special behaviour in the PDO quoter when handling binary data to escape it correctly in the SQL provided to the server.
• ODBC drivers may have different semantics for binding as a string (PDO::PARAM_STRING is mapped to SQL_C_CHAR) rather than as a binary value, which matters when working with i.e. blob/(var)binary columns. For instance, some drivers will return a binary column bound as regular string escaped as hex values.
• Sometimes databases do character conversions of the underlying data; i.e. the data is stored as UTF-16 or EBCDIC and gets converted to UTF-8 when fetched as a string, but you may want to get the raw data yourself.

It's also worth noting that a binary column is *not* inherently a LOB, which some might consider the possible way to map binary values. For example, ODBC has a family of binary string column types, and a distinction between character vs. binary LOB columns. Some drivers (i.e. Postgres) also expect a PDO::PARAM_LOB to be a stream resource.

This RFC proposes either an additional PDO data type *or* a flag for strings and LOBs for representing binary data. This makes the intent clear for both databases and programmers, and should force consistent behaviour across drivers.

<?php
 
$sql = "select id, my_blob from calvin.blob_stream";
 
$dbconn = new PDO("odbc:*LOCAL", "", "");
 
// Proposal 1: Separate data type
$stmt = $dbconn->prepare($sql);
$stmt->execute();
$data = "";
$stmt->bindColumn(2, $data, PDO::PARAM_BINARY);
$stmt->fetch(PDO::FETCH_BOUND);
echo bin2hex($data) . "\n";
// Proposal 2: Flag for PARAM_STRING or PARAM_LOB
$stmt = $dbconn->prepare($sql);
$stmt->execute();
$data = "";
$stmt->bindColumn(2, $data, PDO::PARAM_STRING | PDO::PARAM_BINARY);
$stmt->fetch(PDO::FETCH_BOUND);
echo bin2hex($data) . "\n";
 
?>

Proposal

As PHP has no differentiation between binary and non-binary strings (since PHP supports binary data in strings; the ill-fated PHP 6 would have had them, but it's no more), this is primarily intended for communicating to the database when the difference matters behaviour wise.

There are two approaches for how it gets represented in PDO:

A dedicated data type. This would use the next unassigned PDO data type in the enum (6 as of writing). The benefit here is that it simplifies implementations and maps more cleanly to how many databases represent binary data (as a separate data type).

A modifier flag. This would be a modifier for both strings and also LOBs. This is similar to how “national” strings are implemented already in some drivers, and uses a free high value in the enum for masking (likely 0x10000000). LOBs themselves can be strings (CLOBs) or binaries (BLOBs), so this would allow it to compose with existing LOB support (hence why it should not be called i.e. PDO::PARAM_STR_BINARY).

If discussions are unclear on what approach is better, then it should be held to a vote; if discussions are clear on what approach should be taken, the others will be dropped.

Interaction with other PDO features

Emulated prepares

If binary values require special handling as literal values in queries, then quoters will have to convert binary parameters. For example, SQL Server requires hex-encoding binary data that can't be put into a normal string such as null bytes.

Attributes

• PDO::ATTR_DEFAULT_STR_PARAM: If a flag approach is taken, this will likely have to consider the new additional binary flag.
• PDO::ATTR_STRINGIFY_FETCHES: This coerces fetches to PDO::PARAM_STR, so if drivers differentiate between binary vs. non-binary strings for fetches (i.e. the previous example of an ODBC driver hex-encoding binaries when bound as string), this will affect fetches of binary data.

Examples

Variant with a dedicated data type:

<?php
 
$sql = "select id, my_blob from calvin.blob_stream";
 
$dbconn = new PDO("odbc:*LOCAL", "", "");
$stmt = $dbconn->prepare($sql);
$stmt->execute();
$data = "";
$stmt->bindColumn(2, $data, PDO::PARAM_BINARY);
$stmt->fetch(PDO::FETCH_BOUND);
echo bin2hex($data) . "\n";
 
?>

Variant with a flag for a string:

<?php
 
$sql = "select id, my_blob from calvin.blob_stream";
 
$dbconn = new PDO("odbc:*LOCAL", "", "");
$stmt = $dbconn->prepare($sql);
$stmt->execute();
$data = "";
$stmt->bindColumn(2, $data, PDO::PARAM_STRING | PDO::PARAM_BINARY);
$stmt->fetch(PDO::FETCH_BOUND);
echo bin2hex($data) . "\n";
 
?>

Variant with a flag for a LOB:

<?php
 
$sql = "select id, my_blob from calvin.blob_stream";
 
$dbconn = new PDO("odbc:*LOCAL", "", "");
$stmt = $dbconn->prepare($sql);
$stmt->execute();
$fp = fopen("/tmp/binary", 'wb');
$stmt->bindColumn(2, $data, PDO::PARAM_LOB | PDO::PARAM_BINARY);
$stmt->fetch(PDO::FETCH_BOUND);
 
?>

Backward Incompatible Changes

• Drivers may return the PDO binary parameter type for binary/blob columns in getColumnMeta.
• We may also want to rationalize PARAM_LOB behaviour for binary data as it is inconsistent between drivers; alternatively this could be punted to a future RFC (see Future Scope).

Proposed PHP Version(s)

Next PHP 8.x.

RFC Impact

To the Ecosystem

Database libraries such as ORMs may want to include support for the new binary support. For example, Illuminate in Laravel has a notion of binary data types at least in migrations.

To Existing Extensions

PDO drivers will need to include support. If a driver has no distinction between binary vs. regular strings, then they can just be simply treated as a regular PDO::PARAM_STRING; likewise if they conflate LOBs with binaries for PDO::PARAM_LOB. This may require a small bit of code to do so, likely an additional case.

To SAPIs

None.

Open Issues

• It would be ideal to discuss with userland libraries that wrap PDO what difficulties they face and what approach should be taken.
• It would also be best to consult experts with other databases how it should be implemented in those. I (Calvin) am more familiar with PDO_ODBC/DBLIB/SQLite.

Future Scope

• Rationalize PDO::PARAM_LOB behaviour between drivers, since some (MySQL?) seem to use it for binary data specifically.

Voting Choices

Primary Vote requiring a 2/3 majority to accept the RFC:

Vote: Should PDO have special support for binary values? — Yes: 0 · No: 0 · Abstain: 0 (closed)

What approach should be taken if approved:

Vote: How should PDO binary support be implemented? — Separate data type: 0 · Flag: 0 (closed)

Patches and Tests

POC: https://github.com/php/php-src/pull/11674

Implementation

POC: https://github.com/php/php-src/pull/11674

References

Original issue: https://github.com/php/php-src/issues/11462

Rejected Features

None yet.

Changelog

• 0.3: Minor changes
• 0.2: Suggestions on clarification on interactions (thanks to Saki)
• 0.1: Created.