PHP RFC: PDO Driver specific SQL parsers

• Version: 0.4
• Date: 2024-04-11
• Author: Matteo Beccati, mbeccati@php.net
• Status: Under Discussion
• Discussion: https://externals.io/message/123141
• First Published at: https://wiki.php.net/rfc/pdo_driver_specific_parsers
• Implementation: https://github.com/php/php-src/pull/14035

The PDO extension contains a SQL parser, whose main purpose is to recognise parameter placeholders inside queries (i.e. “?” and “:paramName”), so that it knows how many and what parameters to expect for a query, and pass the information to the PDO driver in use.

This parser had historically been modelled to work with what was the de-facto SQL standard in the PHP ecosystem at that time: MySQL. However, the SQL dialect used by MySQL is different when handling string literals from standard SQL, followed by other database vendors, such as PostgreSQL and SQLite.

Specifically, MySQL treats the backslash character as an escape character:

'This \'word\' is a single quoted'

Whereas the SQL standard uses double single quotes:

'This ''word'' is a single quoted'

When using databases other than MySQL with PDO, valid queries having string literals ending with a backslash character are throwing off the parser and causing apparently inexplicable bugs.

For example:

SELECT 'foo\' AS a, '?' AS b

will make PDO consider “'foo\' AS a, '” to be a string literal and will parse the following “?” as a positional parameter placeholder. In fact, if you pay close attention, even the DocuWiki SQL code formatter is thrown off by this very example.

We have several reports of similar bugs [1], [2], [3], and possibly other duplicates[7].

In a nutshell, the PDO SQL scanner function doesn't need to completely parse the SQL dialect in use. It is sufficient to properly recognise quoted string literals, quoted identifier literals, and comments to skip placeholder detection inside them.

The limitation of a global SQL parser in PDO meant that my previous RFC to support the PostgreSQL-only "?" operator[4] had to apply the parser change to all database types. The change had no known side-effects, but I feel this RFC could improve by keeping this peculiarity inside the pdo_pgsql scanner only.

Following a detailed research (see below) for each of the databases currently supported by PDO in core, the proposal is to:

1. change the default PDO scanner to expect standard SQL only:
   1. single and double quoted literals, with doubling as escaping mechanism
   2. two-dashes and C-style comments (non-nested, as that seems to be the most common format, already implemented in PDO)
2. allow drivers to optionally provide a custom scanner function to handle their specific SQL dialect;
3. Add a MySQL specific scanner function:
   1. single and double quoted literals with both doubling and backslash as escaping mechanisms (MySQL default)
   2. backtick literals with doubling as escaping mechanism (I also tested and it seems MySQL doesn't accept backslashes as escapes)
   3. two-dashes, C-style comments, and Hash-comments, albeit I couldn't force the requirement of a space after “--” (I tried, but failed)
   4. Tests, as necessary
4. Add a PgSQL specific scanner function:
   1. single and double quoted literals, with doubling as escaping mechanism
   2. two-dashes and C-style comments (non-nested, as nesting would require unwanted changes to the common parser functionality)
   3. Support for “??” as escape sequence for the “?” operator
   4. Tests, as necessary
5. Add a SqLite specific scanner function:
   1. single, double quoted, and backtick literals, with doubling as escaping mechanism
   2. two-dashes and C-style comments (non-nested)
   3. Tests, as necessary

In order to keep the change as simple as possible, the proposal tries to cover the default SQL syntax for each database as closely as possible, trying to modifying the common parser code.

One important thing to mention is that the proposed changes are only targeting the part of PDO that scans the SQL query for parameter placeholders. Quoting literals or using parameters in queries is not going to be affected.

In order to execute the plan, a new member will be appended to:

struct pdo_dbh_methods {
	pdo_dbh_close_func		closer;
	pdo_dbh_prepare_func		preparer;
	pdo_dbh_do_func			doer;
	pdo_dbh_quote_func		quoter;
	// ...
	pdo_dbh_sql_scanner		scanner;
}

Each PDO driver defines already their own struct. Leaving the new member to NULL will make the driver use the default PDO scanner function. Otherwise a pointer to a custom scanner function will override the default when parsing queries. It's really as simple as that.

The rest of the implementation is the actual re2c scanner code, config.*, Makefile changes, etc. required to incorporate the driver specific scanner into the build.

MySQL by default accepts both backslash escaped quotes and SQL standard. String literals can use single or double quotes. See the documentation (8.0 current is linked here, but 5.7 and 8.3 bahave the same).

The NO_BACKSLASH_ESCAPE SQL mode will disable recognition of the backslash as escape character. If set it will break SQL scanning regardless of this RFC.

The ANSI_QUOTES SQL mode switches from backtick to SQL standard double quotes for identifier literals.

Several comment types supported: -- , #, and /* */ (not nested).

Escaping has evolved during the years. Historically accepted “\'”, but started gradually transitioning to the SQL standard around 2005, going from memory. Since 9.1 (2011+) it accepts only single quoted string literals by default according to the SQL standard. See the documentation.

It also accepts escape string literals, e.g.

E'This \'word\' is a single quoted'

I had seen them being used when PostgreSQL started emitting warnings about backslashes in string literals, during the transition phase when magic_quotes_qpc was a thing, but I really see no place for this syntax in modern PHP. In fact this is the only syntax that is compatible with the current PDO SQL scanner, although I wouldn't recommend anybody to change their SQL string literals to this variant.

The behaviour of strings can also be manipulated in multiple ways through configuration variables, such as: standard_conforming_strings, backslash_quote, and escape_string_warning.

The RFC will ensure regular SQL standard string literals are supported on a Postgres instance with default configuration, while the rarer escape string literals will not be supported.

Support for escape string literal is out of scope of this RFC and should be documented as incompatible in the UPGRADING file, as weirdly enough it previously was the only fully-compatible format.

About comments, it follows the standard with -- and /* */ (w/ nested comments allowed)

Follows the SQL standard, and requires double single quotes to represent the single quote in a string literal. See documentation.

It will however accept double quoted strings as string literals under some circumstances.

Double quoted identifiers, but also backticks and square brackets.

Almost SQL standard comments: -- and /* */ (not nested).

SQL standard string literals, according to the documentation. It also supports alternative quoting, e.g. q'<literal>' and many other variants, which is out of scope for this RFC.

Double quoted identifiers.

Almost SQL standard comments: -- and /* */ (not nested).

SQL standard string literals, according to the documentation.

Depending on the QUOTED_IDENTIFIER setting, double quotes are either used for strings or identifiers.

Almost SQL standard comments: -- and /* */ (non nested).

SQL standard string literals, according to the documentation. It also support hexadecimal (binary) strings, e.g. x'50444F', and, similarly to Oracle, quoted strings (out of scope).

The documentation mentions “Double quotes are NOT VALID for quoting strings. The SQL standard reserves double quotes for a different purpose: quoting identifiers.”

Almost SQL standard comments: -- and /* */ (non nested).

Since ODBC can connect to various types of databases, the SQL standard parser hopefully will suffice.

A few years back I attempted to fix a bug and came up with with a pull request that could be considered a proof of concept for this RFC. The same topic was also brought up by others on internals, but no one had time to follow up with a proper RFC.

No expected BC breaks.

Users having applications that can work with multiple database engines should still be very careful and write portable queries, possibly using the PDO::quote() method when necessary instead of hardcoding strings containing escape characters.

Next PHP 8.x, hopefully 8.4.

No impact

Drivers outside of php-src might have to be modified if they make assumptions about the structure of enum pdo_param_type. They would have to be rebuilt since the PDO_DRIVER_API macro would be updated.

That has historically been allowed/expected in minor versions. The last time it happened was for PHP 7.2 with PHP RFC: Extended String Types For PDO.

No impact to opcache.

No new constant.

No php.ini changes

No open issues ATM.

Anything not related to PDO scanning the SQL query for parameter placeholders.

The scanners are generated when PHP is compiled and, currently, cannot be modified at runtime. However, some databases allow configuration directives or SET queries to change the accepted syntax for literals, identifiers, etc.

Being able to understand all possible combinations would require tracking what directives are different from the expected default and having a number of scanners inside each driver for each possible permutation of such configuration directives.

Evaluate supporting “exotic” syntaxes in the existing scanners and/or add other custom scanner functionality.

As per the voting RFC a yes/no vote with a 2/3 majority is needed for this proposal to be accepted.

Draft Pull Request

• [1] https://bugs.php.net/bug.php?id=78534
• [2] https://bugs.php.net/bug.php?id=79276
• [3] https://bugs.php.net/bug.php?id=80340
• [4] PHP RFC: Escape PDO "?" parameter placeholder
• [5] PoC implementation of a custom pdo_pgsql scanner
• [6] Previous discussion of the topic on internals
• [7] More recent bug