Thread: Research on ?? operators
Hello generals, I'm trying to resurrect a change to the PHP PDO driver to allow using the "?" json operator (and other operators containing a question mark), without it being interpreted as a placeholder for a query argument. In order to do so, like Hibernate, I went for the double "??" escaping: https://wiki.php.net/rfc/pdo_escape_placeholders One question that I'm supposed to answer now is: is there any known usage in the wild of some custom "??" operator that would require funny escaping like "????"? I've tried to search pgxn but couldn't find any match, so I thought it was worth to try and ask here. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/
>>>>> "Matteo" == Matteo Beccati <php@beccati.com> writes: Matteo> https://wiki.php.net/rfc/pdo_escape_placeholders Matteo> One question that I'm supposed to answer now is: is there any Matteo> known usage in the wild of some custom "??" operator that would Matteo> require funny escaping like "????"? I don't recall seeing a ?? operator in the wild, but it is a perfectly legal operator name and you should assume that it exists somewhere. -- Andrew (irc:RhodiumToad)
> On Jun 4, 2019, at 10:00 AM, Matteo Beccati <php@beccati.com> wrote: > > Hello generals, > > I'm trying to resurrect a change to the PHP PDO driver to allow using > the "?" json operator (and other operators containing a question mark), > without it being interpreted as a placeholder for a query argument. In > order to do so, like Hibernate, I went for the double "??" escaping: > > https://wiki.php.net/rfc/pdo_escape_placeholders > > One question that I'm supposed to answer now is: is there any known > usage in the wild of some custom "??" operator that would require funny > escaping like "????"? I don't know of one today, but that doesn't mean there isn't or won't be tomorrow. > I've tried to search pgxn but couldn't find any match, so I thought it > was worth to try and ask here. Doubling "?" to "??" isn't an obvious, intuitive way to do that, at least to me. Maybe it would be to people coming from Java. Perl's DBD::Pg deals with it in two ways. One is to allow escaping the ? with a backslash - so "?" is a placeholder, while "\?" is passed as "?" to the database. That's more consistent with other languages, and I think you're far less likely to ever see a backslash in a custom operator than "??". The other is that it supports the postgresql standard placeholders - $1, $2 etc. - which avoid the issue and are more flexible. It also has a configuration option to completely ignore "?" in queries, so "$1" is a placeholder and "?" is passed through to the database. (Though I like the ":foo" or "@foo" style named placeholders best) Cheers, Steve
Hi Andrew, On 04/06/2019 12:38, Andrew Gierth wrote: >>>>>> "Matteo" == Matteo Beccati <php@beccati.com> writes: > > Matteo> https://wiki.php.net/rfc/pdo_escape_placeholders > > Matteo> One question that I'm supposed to answer now is: is there any > Matteo> known usage in the wild of some custom "??" operator that would > Matteo> require funny escaping like "????"? > > I don't recall seeing a ?? operator in the wild, but it is a perfectly > legal operator name and you should assume that it exists somewhere. Absolutely yes, that's why it is mentioned in my RFC and will be eventually mentioned in the UPGRADE documentation. But perhaps we can also safely assume that 99.9% of the users won't have any operator containing "??", so it can be considered a minor BC-break. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/
Hi Steve, On 04/06/2019 12:49, Steve Atkins wrote: > Doubling "?" to "??" isn't an obvious, intuitive way to do that, at least > to me. Maybe it would be to people coming from Java. Funny enough, when I proposed \? on the pg mailing lists a few years ago I was suggested ?? to match SQL escaping and Hibernate (going from memory). > Perl's DBD::Pg deals with it in two ways. One is to allow escaping > the ? with a backslash - so "?" is a placeholder, while "\?" is passed as > "?" to the database. That's more consistent with other languages, and > I think you're far less likely to ever see a backslash in a custom operator > than "??". That is true as well, but '\' being also the escape character for strings would also lead to some WTF moments if one tried to escape the backslash itself, e.g. '\\? === '\?'. > The other is that it supports the postgresql standard placeholders - $1, > $2 etc. - which avoid the issue and are more flexible. It also has a configuration > option to completely ignore "?" in queries, so "$1" is a placeholder and "?" > is passed through to the database. > > (Though I like the ":foo" or "@foo" style named placeholders best) PDO does support both positional "?" and ":foo" named placeholders. Adding an option to disable one would probably break much more code and libraries than just a simple escape character. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/
":foo" named placeholders
Hi Michael, On 04/06/2019 14:35, Michael Lewis wrote: > /":foo" named placeholders/ > > If I may, is this supported natively in Postgres prepared statements? > Can I see an example? I do not care much for the numbers of positional > placeholders and would love to use names instead if possible. Not natively. In this case it's the PHP PDO pgsql driver that performs the translation. I suppose many other DB libraries do that in any programming language. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/
> On Jun 4, 2019, at 1:35 PM, Michael Lewis <mlewis@entrata.com> wrote: > > ":foo" named placeholders > > If I may, is this supported natively in Postgres prepared statements? It's not. The only type we support are numbered $1 type placeholders. > Can I see an example? I do not care much for the numbers of positional placeholders and would love to use names insteadif possible. It'd be nice. They're supported via rewriting at the driver level in some drivers, and I've written shims to convert themin an app a few times and it makes for much more readable - and bug-resistant - code. Supporting it at the protocol level would be very nice. Cheers, Steve