====== PHP RFC: Escape PDO "?" parameter placeholder ====== * Version: 1.0 * Date: 2019-05-31 * Author: Matteo Beccati * 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 ((https://www.postgresql.org/docs/10/static/functions-geometry.html#functions-geometry-op-table)), but 3 of them apply to the //jsonb// type ((https://www.postgresql.org/docs/10/static/functions-json.html#functions-jsonb-op-table)), 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 ((https://jdbc.postgresql.org/documentation/head/statement.html)). 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 * Yes * No ===== Proposed Voting Choices ===== This RFC requires a 2/3 majority ===== Patches and Tests ===== [[https://github.com/php/php-src/pull/4217]] ===== References ===== * [[https://bugs.php.net/bug.php?id=71885]] (Feature request) * [[https://github.com/php/php-src/pull/1967]] (Original PR)