rfc:pdo_escape_placeholders
PHP RFC: Escape PDO "?" parameter placeholder
- Version: 1.0
- Date: 2019-05-31
- Author: Matteo Beccati mbeccati@php.net
- Status: Implemented
- First Published at: http://wiki.php.net/rfc/pdo_escape_placeholders
- Targets: PHP 7.4
Introduction
PostgreSQL, and possibly other databases, allow the usage of the question mark (“?”) character in operators, either alone or part of a multi-character operator. In fact, as of today, core Postgres currently has 21 operators that include it in their names. Most of them belong to geometric types 1), but 3 of them apply to the jsonb type 2), which has been introduced in Postgres 9.4 and has a much wider and growing audience. Unfortunately it is impossible to use them with the PDO extension, due to a clash with the positional parameter placeholder, which can't currently be escaped, nor disabled.
Proposal
Improve the PDO SQL parser so that question marks can be escaped by doubling them, similarly to what the SQL standard does with single quotes within string literals, which is also the same behaviour implemented in JDBC 3). That means that the “??” string would be translated to “?” when sending the query to the database, whereas “?” is still going to be interpreted as a positional parameter placeholder.
For example it would be possible to do the following:
$stmt = $pdo->prepare('SELECT * FROM tbl WHERE json_col ?? ?'); $stmt->execute(['foo']);
which would effectively run the following query:
SELECT * FROM tbl WHERE json_col ? 'foo'
Backward Incompatible Changes
The parsing of the “??” character sequence within queries is going to change, but only when they are outside of literal strings or comments. Since such character sequence isn't normally valid SQL syntax, no BC break is expected.
The only exception to that is that Postgres (and possibly other RDMSs) allows the creation of custom operators: anyone having a custom “??” operator in use would need to escape it as “????”.
Since the change affects the PDO SQL parser, all the drivers could potentially use escaping, unless they use the “?” character themselves as prepared statement parameter placeholder. In that case “??” will be sent to the database as-is do avoid any confusion.
Proposed PHP Version(s)
Next PHP 7.x, which is 7.4.
RFC Impact
To SAPIs
No impact.
To Existing Extensions
All PDO drivers will be saffected by the change, but there is no API change.
To Opcache
No impact.
Open Issues
None.
Alternative proposals
Why not \?
That was my first idea, but backslash is the escaping symbol for strings themselves, which was potentially making eventual escaping quite confusing: in order to send a backslash followed by a positional parameter placeholder one would have to write “\\\?” or “\\\\?”, as “\\?” is in fact equivalent to “\?”.
New PDO Flags
It had been proposed to add some flags to enable/disable usage of positional and/or named PDO parameter placeholders. After some evaluation, I've discarded it as I would find them unpractical to use (e.g. set the flag, execute, restore previous flags), potentially harmful for any code that follows (e.g. flags not reset after catching an exception), if at all a viable option for users of ORM or abstraction libraries.
Custom placeholder styles for drivers
Another suggestion was to use different styles for placeholders to match the database driver in use, which to me seems a huge step backwards.
Unaffected PHP Functionality
Everything not PDO.
Vote
Started 7th July 2019. Ends 22nd July 2019
Proposed Voting Choices
This RFC requires a 2/3 majority
Patches and Tests
References
- https://bugs.php.net/bug.php?id=71885 (Feature request)
- https://github.com/php/php-src/pull/1967 (Original PR)
rfc/pdo_escape_placeholders.txt · Last modified: 2020/08/01 23:55 by carusogabriel