rfc:pdo_escape_placeholders

PHP RFC: Escape PDO "?" parameter placeholder

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

Add ?? as a way to escape ? in PDO parametrised queries?
Real name Yes No
aeoris (aeoris)  
ashnazg (ashnazg)  
bishop (bishop)  
carusogabriel (carusogabriel)  
cpriest (cpriest)  
derick (derick)  
duncan3dc (duncan3dc)  
galvao (galvao)  
girgias (girgias)  
guilhermeblanco (guilhermeblanco)  
jasny (jasny)  
kalle (kalle)  
kguest (kguest)  
levim (levim)  
mbeccati (mbeccati)  
mike (mike)  
nikic (nikic)  
ocramius (ocramius)  
petk (petk)  
pmjones (pmjones)  
ramsey (ramsey)  
rjhdby (rjhdby)  
rtheunissen (rtheunissen)  
salathe (salathe)  
santiagolizardo (santiagolizardo)  
sergey (sergey)  
svpernova09 (svpernova09)  
trowski (trowski)  
yunosh (yunosh)  
Final result: 27 2
This poll has been closed.

Proposed Voting Choices

This RFC requires a 2/3 majority

Patches and Tests

References

rfc/pdo_escape_placeholders.txt · Last modified: 2020/08/01 23:55 by carusogabriel