rfc:pdo_escape_placeholders

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
rfc:pdo_escape_placeholders [2016/12/18 15:57] – created mbeccatirfc:pdo_escape_placeholders [2020/08/01 23:55] (current) – RFC was implemented carusogabriel
Line 1: Line 1:
-====== PHP RFC: Escape PDO placeholders ====== +====== PHP RFC: Escape PDO "?" parameter placeholder ====== 
-  * Version: 0.+  * Version: 1.0 
-  * Date: 2016-12-18+  * Date: 2019-05-31
   * Author: Matteo Beccati <mbeccati@php.net>   * Author: Matteo Beccati <mbeccati@php.net>
-  * Status: Pre-Draft+  * Status: Implemented
   * First Published at: http://wiki.php.net/rfc/pdo_escape_placeholders   * First Published at: http://wiki.php.net/rfc/pdo_escape_placeholders
 +  * Targets: PHP 7.4
  
 ===== Introduction ===== ===== 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 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, but 3 of them apply to the //jsonb// type, 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 placeholder, which can't currently be disabled or escaped.+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 ===== ===== Proposal =====
-Improve the PDO SQL parser so that question marks can be escaped by doubling them, in order to follow the SQL standard.+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:
 +<code php>
 +$stmt = $pdo->prepare('SELECT * FROM tbl WHERE json_col ?? ?');
 +$stmt->execute(['foo']); 
 +</code>
  
-To [[http://news.php.net/php.internals/66051|paraphrase Zeev Suraski]], explain hows the proposal brings substantial value to be considered +which would effectively run the following query:
-for inclusion in one of the world's most popular programming languages.+
  
-Remember that the RFC contents should be easily reusable in the PHP Documentation. +<code sql> 
- +SELECT * FROM tbl WHERE json_col ? 'foo' 
-If applicable, you may wish to use the language specification as a reference.+</code>
  
 ===== Backward Incompatible Changes ===== ===== Backward Incompatible Changes =====
-What breaksand what is the justification for it?+The parsing of the "??" character sequence within queries is going to changebut 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) ===== ===== Proposed PHP Version(s) =====
-List the proposed PHP versions that the feature will be included in.  Use relative versions such as "next PHP 7.x" or "next PHP 7.x.y".+Next PHP 7.x, which is 7.4.
  
 ===== RFC Impact ===== ===== RFC Impact =====
Line 32: Line 41:
  
 ==== To Existing Extensions ==== ==== To Existing Extensions ====
-All PDO drivers will be somehow affected by the change, but there is no API change.+All PDO drivers will be saffected by the change, but there is no API change.
  
 ==== To Opcache ==== ==== To Opcache ====
Line 38: Line 47:
  
 ===== Open Issues ===== ===== Open Issues =====
-Make sure there are no open issues when the vote starts!+None.
  
-===== Unaffected PHP Functionality ===== +===== Alternative proposals =====
-List existing areas/features of PHP that will not be changed by the RFC.+
  
-This helps avoid any ambiguityshows that you have thought deeply about the RFC's impactand helps reduces mail list noise.+==== Why not \? ==== 
 +That was my first ideabut 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 "\?".
  
-===== Future Scope ===== +==== New PDO Flags ==== 
-This sections details areas where the feature might be improved in futurebut that are not currently proposed in this RFC.+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 flagexecute, 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.
  
-===== Proposed Voting Choices ===== +==== Custom placeholder styles for drivers ==== 
-Include these so readers know where you are heading and can discuss the proposed voting options.+Another suggestion was to use different styles for placeholders to match the database driver in use, which to me seems a huge step backwards.
  
-State whether this project requires a 2/3 or 50%+1 majority (see [[voting]])+===== Unaffected PHP Functionality ===== 
 +Everything not PDO.
  
-===== Patches and Tests ===== +===== Vote ===== 
-Links to any external patches and tests go here.+Started 7th July 2019Ends 22nd July 2019 
 +<doodle title="Add ?? as a way to escape ? in PDO parametrised queries?" auth="mbeccati" voteType="single" closed="true"> 
 +   * Yes 
 +   * No 
 +</doodle>
  
-If there is no patch, make it clear who will create patch, or whether a volunteer to help with implementation is needed.+===== Proposed Voting Choices ===== 
 +This RFC requires 2/3 majority
  
-Make it clear if the patch is intended to be the final patch, or is just a prototype. +===== Patches and Tests ===== 
- +[[https://github.com/php/php-src/pull/4217]]
-For changes affecting the core language, you should also provide a patch for the language specification. +
- +
-===== Implementation ===== +
-After the project is implemented, this section should contain  +
-  - the version(s) it was merged to +
-  - a link to the git commit(s) +
-  - a link to the PHP manual entry for the feature +
-  a link to the language specification section (if any)+
  
 ===== References ===== ===== References =====
-Links to external references, discussions or RFCs+  * [[https://bugs.php.net/bug.php?id=71885]] (Feature request) 
 +  * [[https://github.com/php/php-src/pull/1967]] (Original PR)
  
-===== Rejected Features ===== 
-Keep this updated with features that were discussed on the mail lists. 
rfc/pdo_escape_placeholders.txt · Last modified: 2020/08/01 23:55 by carusogabriel