PHP: rfc:pdo_driver_specific_parsers

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== PHP RFC: PDO Driver specific SQL parsers ======
  * Version: 1.0
  * Date: 2024-04-11
  * Author: Matteo Beccati, mbeccati@php.net
  * Status: Implemented
  * Target Version: 8.4
  * Implementation: [[https://github.com/php/php-src/commit/fbe317bf2179c65b750cc945c3530b28db1670e0]]
  * First Published at: [[https://wiki.php.net/rfc/pdo_driver_specific_parsers]]

===== Introduction =====
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 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: <code php>'This \'word\' is a single quoted'</code>
Whereas the SQL standard uses double single quotes: <code sql>'This ''word'' is a single quoted'</code>

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:

<code sql>
SELECT 'foo\' AS a, '?' AS b
</code>

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 [[https://bugs.php.net/bug.php?id=78534|[1]]], [[https://bugs.php.net/bug.php?id=79276|[2]]], [[https://bugs.php.net/bug.php?id=80340|[3]]], and possibly other duplicates[[https://github.com/php/php-src/issues/13958|[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**.

==== Bonus improvement ====
The limitation of a global SQL parser in PDO meant that my previous [[rfc:pdo_escape_placeholders|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.

===== Proposal =====
Following a detailed research (see below) for each of the databases currently supported by PDO in core, the proposal is to allow drivers to optionally provide a custom scanner function to handle their specific SQL dialect and:

  - Change the [[https://github.com/php/php-src/blob/ab589e4481f0cf35c8773e0c64dccc35b8870ae1/ext/pdo/pdo_sql_parser.re#L42|default PDO scanner]] to expect standard SQL only:
    - single and double quoted literals, with doubling as escaping mechanism
    - two-dashes and C-style comments (non-nested, as that seems to be the most common format, already implemented in PDO)
  - Add a MySQL-specific scanner function:
    - single and double quoted literals with both doubling and backslash as escaping mechanisms (MySQL default)
    - backtick literals with doubling as escaping mechanism (I also tested and it seems MySQL doesn't accept backslashes as escapes)
    - two-dashes (if followed by 1 whitespace), C-style comments, and Hash-comments
    - Tests, as necessary
  - Add a PgSQL-specific scanner function:
    - single and double quoted literals, with doubling as escaping mechanism
    - C-style "escape" string literals
    - Dollar-quoted string literals
    - two-dashes and C-style comments (non-nested, as nesting would require unwanted changes to the common parser functionality)
    - Support for "??" as escape sequence for the "?" operator
    - Tests, as necessary
  - Add a SqLite-specific scanner function:
    - single, double quoted, and backtick literals, with doubling as escaping mechanism
    - square brackets quoting for identifiers
    - two-dashes and C-style comments (non-nested)
    - Tests, as necessary

To keep the change as simple as possible, the proposal tries to cover the default SQL syntax for each database as closely as possible, without heavy changes to 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**.

===== Detailed Proposal =====
To execute the plan, a new member will be appended to:

<code c>
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;
}
</code>

Each PDO driver defines already [[https://github.com/search?q=repo%3Aphp/php-src%20pdo_dbh_methods&type=code|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.

To support dollar-quoted strings on Postgres, the functionality of custom quoting has been added to the common PDO parser function. The change has no side effects for other database drivers.

One minor potential BC-break was reported while researching bug [[https://github.com/php/php-src/issues/14244|#14244]], which basically describes lack of support for dollar quoting in pdo_pgsql. One of the currently viable workarounds is to use escaped question marks inside dollar-quoted strings to avoid unexpected placeholder detection. The last version of the implementation still allows that, while raising the following deprecation notice:

''Escaping question marks inside dollar quoted strings is not required anymore and is deprecated''.

Such BC-compatibility can be removed in the next major version.


===== Research on String Literals, Identifiers, and Comments ===== 

==== MySQL ====
MySQL by default accepts both backslash escaped quotes and SQL standard. String literals can use single or double quotes. See [[https://dev.mysql.com/doc/refman/8.0/en/string-literals.html|the documentation]] (8.0 current is linked here, but 5.7 and 8.3 behave the same).

The [[https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_backslash_escapes|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 [[https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_ansi_quotes|ANSI_QUOTES]] SQL mode switches from backtick to SQL standard double quotes for identifier literals.

Several [[https://dev.mysql.com/doc/refman/8.0/en/comments.html|comment types]] supported: ''-- '', ''#'', and ''/* */'' (not nested).

The RFC aims to support all the above kinds of string literals with string-affecting configuration variables set to their defaults. All comment types will be supported.

==== PostgreSQL ====

Escaping has evolved over 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 [[https://www.postgresql.org/docs/16/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS|the documentation]].

Postgres also supports [[https://www.postgresql.org/docs/16/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE|String Constants with Unicode Escapes ]], which follow the same conventions as standard strings and are parsed by PDO as regular strings.

It also accepts [[https://www.postgresql.org/docs/16/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE|“escape” string constants]], e.g.

<code php>E'This \'word\' is a single quoted'</code>

Lastly, [[https://www.postgresql.org/docs/16/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING|Dollar-Quoted String Constants]] are very common, especially when defining functions.

The behaviour of strings can also be manipulated in multiple ways through configuration variables, such as: [[https://www.postgresql.org/docs/16/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS|standard_conforming_strings]], [[https://www.postgresql.org/docs/16/runtime-config-compatible.html#GUC-BACKSLASH-QUOTE|backslash_quote]], and [[https://www.postgresql.org/docs/16/runtime-config-compatible.html#GUC-ESCAPE-STRING-WARNING|escape_string_warning]].

About [[https://www.postgresql.org/docs/16/sql-syntax-lexical.html#SQL-SYNTAX-COMMENTS|comments]], it follows the standard with ''--'' and ''/* */'' (w/ nested comments allowed)

The RFC aims to support all the above kinds of string literals with string-affecting configuration variables set to their defaults. Dollar-quoting support requires minimal changes to the common PDO parser function. All comment types are already supported, albeit support for nested comments will not be introduced.

==== SQLite ====

Follows the SQL standard, and requires double single quotes to represent the single quote in a string literal. See [[https://www.sqlite.org/lang_expr.html#literal_values_constants_|documentation]].

It will however accept double-quoted strings as string literals under [[https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted|some circumstances]].

Double quoted identifiers, but also [[https://sqlite.org/lang_keywords.html|backticks and square brackets]].

Almost SQL standard [[https://www.sqlite.org/lang_comment.html|comments]]: ''--'' and ''/* */'' (not nested).

The RFC aims to support single-quoted, double-quoted, backtick, and square-bracketed literals. All comment types are already supported. 

==== SQL Server ====

SQL standard string literals, according to the [[https://learn.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql?view=sql-server-ver16#character-string-constants|documentation]].

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

Almost SQL standard [[https://learn.microsoft.com/en-us/sql/t-sql/language-elements/comment-transact-sql?view=sql-server-ver16|comments]]: ''--'' and ''/* */'' (non nested).

No custom parser is planned in this RFC: the default scanner will be used by default, bringing compatibility for SQL standard string literals, identifiers, and comments.

==== Firebird ====
SQL standard string literals, according to the [[https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-commons.html#fblangref40-commons-constants|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 [[https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref25/fblangref25-structure-comments.html|comments]]: ''--'' and ''/* */'' (non nested).

No custom parser is planned in this RFC: the default scanner will be used by default, bringing compatibility for SQL standard string literals, identifiers, and comments.


==== ODBC ====

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

==== Oracle ====

SQL standard string literals, according to the [[https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html|documentation]].
It also supports alternative quoting, e.g. q'<literal>' and many other variants, which is out of scope for this RFC.

[[https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Object-Names-and-Qualifiers.html#SQLRF-GUID-75337742-67FD-4EC0-985F-741C93D918DA|Double quoted identifiers]].

Almost SQL standard [[https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Comments.html#SQLRF-GUID-5C84C344-CEB3-4DBF-B748-337DE11CCE2A|comments]]: ''--'' and ''/* */'' (not nested).

The OCI driver lives in PECL: the default scanner will be used by default, bringing compatibility for SQL standard string literals, identifiers, and comments.


===== Historical Background =====
A few years back I attempted to fix a bug and came up with a [[https://github.com/php/php-src/pull/6852|pull request]] that could be considered a proof of concept for this RFC. The same topic was also brought up [[https://externals.io/message/114016|by others on internals]], but no one had time to follow up with a proper RFC.

===== Backward Incompatible Changes =====
No BC breaks, but a deprecation notice will be raised when using the "escaped question marks inside dollar quoted string" workaround described before.

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.

===== Proposed PHP Version(s) =====
Next PHP 8.x, hopefully, 8.4.

===== RFC Impact =====
==== To SAPIs ====
No impact

==== To Existing Extensions ====
Drivers outside of php-src might have to be modified if they make assumptions about the structure of the 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 [[rfc:extended-string-types-for-pdo|PHP RFC: Extended String Types For PDO]].

==== To Opcache ====
No impact to opcache.

==== New Constants ====
No new constant.

==== php.ini Defaults ====
No php.ini changes

===== Open Issues =====
No open issues ATM.

===== Unaffected PHP Functionality =====
Anything not related to PDO scanning the SQL query for parameter placeholders.

===== Out Of Scope =====

==== Dynamic changes to the scanner ====
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 several scanners inside each driver for each possible permutation of such configuration directives.

===== Future Scope =====
Evaluate supporting "exotic" syntaxes in the existing scanners and/or add other custom scanner functionality.

===== Voting =====
Voting will be open until Monday, 17 June 2024 at 15:00:00 UTC. As usual, 2/3 majority is needed for this proposal to be accepted.

<doodle title="Implement PDO Driver specific SQL parsers?" auth="mbeccati" voteType="single" closed="true" closeon="2024-06-17T15:00:00Z">
   * Yes
   * No
</doodle>

===== Patches and Tests =====

[[https://github.com/php/php-src/pull/14035|Implementation Pull Request]]

===== References =====

  * [1] [[https://bugs.php.net/bug.php?id=78534|https://bugs.php.net/bug.php?id=78534]]
  * [2] [[https://bugs.php.net/bug.php?id=79276|https://bugs.php.net/bug.php?id=79276]]
  * [3] [[https://bugs.php.net/bug.php?id=80340|https://bugs.php.net/bug.php?id=80340]]
  * [4] [[rfc:pdo_escape_placeholders|PHP RFC: Escape PDO "?" parameter placeholder]]
  * [5] [[https://github.com/php/php-src/pull/6852|PoC implementation of a custom pdo_pgsql scanner]]
  * [6] [[https://externals.io/message/114016|Previous discussion of the topic on internals]]
  * [7] [[https://github.com/php/php-src/issues/13958|More recent bug]]
Page Tools
