Re: Escaping placeholders, take 2
On Fri, Jan 09, 2015 at 02:54:56PM -, Greg Sabino Mullane wrote: Just a heads up to this list - support for backslash-escaped placeholders is now implemented in the current production version of DBD::Pg, 3.5.0 http://search.cpan.org/dist/DBD-Pg/ http://cpansearch.perl.org/src/TURNSTEP/DBD-Pg-3.5.0/Changes Many thanks Greg. Tim.
Re: Escaping placeholders, take 2
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Just a heads up to this list - support for backslash-escaped placeholders is now implemented in the current production version of DBD::Pg, 3.5.0 http://search.cpan.org/dist/DBD-Pg/ http://cpansearch.perl.org/src/TURNSTEP/DBD-Pg-3.5.0/Changes - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201501090952 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlSv64MACgkQvJuQZxSWSsjPNQCfbt80/2VAaRj5qIQG7fm+0TqL kSQAmQHrbf1d22hWx5+NuOeq7TmPccmK =+XvD -END PGP SIGNATURE-
Re: Escaping placeholders, take 2
On Wed, Dec 31, 2014 at 03:55:42AM -, Greg Sabino Mullane wrote: Tim Bunce wrote: Wherein I attempt to summarize the requirements, the background, the options, the risks, and the counter-proposal ... Thanks for taking the time to write all this out in a coherent manner. You're welcome. I find the effort usually greatly clarifies my thinking. I may once again assume the role of devil's advocate herein. Thanks. In modern application development, however, the DBI is just another foundation layer, low down in the stack. Above it are modules like SQL::Abstract, and ORMs like DBIx::Class. Companies have large and growing investments in these stacks, plus the modules they've built over them to provide abstraction and encapsulation of business logic. Agreed. For these large applications the attribute workarounds are rarely helpful. Is that a true generalization? Or is it only because these large applications have not bothered to support the attributes? Supporting attributes means supporting (non-standard) numbered placeholders. Supporting numbered placeholders is, as previously described, very far from trivial for SQL::Abstract and thus DBIx::Class. (I certainly wouldn't characterize this as not bothered.) +Some drivers allow you to prevent the recognition of a placeholder by placing a +single backslash character (C\) immediately before it. The driver will remove +the backslash character and ignore the placeholder, passing it unchanged to the +backend. If the driver supports this then L/get_info(9000) will return true. Seems a good start. I don't like the phrase prevent recognition of a placeholder but it's too late at night to propose an alternate right now. s/prevent/disable/? Which likely means that SQL::Abstract and/or DBIx::Class will need to know as well. (Unless they expose the DBD directly to the user, which ruins the point a bit). Not true. How will a user of SQL::Abstract know if escapes are supported or not? Isn't the job of such frameworks to release the user from such worries, or at least present some sort of attribute the user can rely on? SQL::Abstract isn't (just) about portability. It allows SQL conditions to be expressed and composed via Perl data structures. That's very powerful functionality. Users who want to use a postgres ? operator know they're using DBD::Pg and can simply require the appropriate version. Question marks are the international standard for placeholders. The DBI should have specified a way to escape them from the start. I'm attempting to fix that now. Taking off the devil's advocate hat now. I can agree with this position and the backslash solution. Great. Thanks Greg. To demonstrate the relative complexity, here's a proof-of-concept patch for DBD::Pg to implement support for escaping question mark placeholders: https://github.com/timbunce/dbdpg/commit/54358c7a7efeeaf2666c5e28c301e47624fb9615 +1, thanks for that. I certainly cannot see any harm in adding escape support to DBD::Pg, and then letting the upstream modules start adapting to it. Wonderful. I'll work on polishing up the patch. Tim.
Re: Escaping placeholders, take 2
Hi All My apologies. Instead of Text::Balanced::Marpa, I should have pointed you to MarpaX::Languages::SQL2003::AST. Here, AST is Abstract syntax tree. See https://en.wikipedia.org/wiki/Abstract_syntax_tree. -- Ron Savage - savage.net.au
Re: Escaping placeholders, take 2
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Tim Bunce wrote:
Wherein I attempt to summarize the requirements, the background, the options,
the risks, and the counter-proposal ...
Thanks for taking the time to write all this out in a coherent manner.
I may once again assume the role of devil's advocate herein.
In modern application development, however, the DBI is just another
foundation layer, low down in the stack. Above it are modules like
SQL::Abstract, and ORMs like DBIx::Class. Companies have large and
growing investments in these stacks, plus the modules they've built over
them to provide abstraction and encapsulation of business logic.
Agreed.
For these large applications the attribute workarounds are rarely helpful.
Is that a true generalization? Or is it only because these large
applications have not bothered to support the attributes?
Working 'high up' in an application stack that's dynamically constructing
SQL with placeholders it should be possible to express any desired behavior.
Agreed.
Some mechanism is needed to allow an individual character sequence that
normally represents a placeholder, to appear in the SQL without it being
treated as a placeholder by the driver. To escape it's usual meaning.
But... okay I can't find any fault with this. :)
If escaping question mark placeholders was already defined in 2008 then
pg_placeholder_dollaronly might not have been added to DBD::Pg.
Perhaps, perhaps not. pg_placeholder_dollaronly still allows the same SQL to
be slung around, including using it inside of Postgres itself. So it's
likely both methods would have been available.
1. Backslash
Seems like a good solution. A little ugly, but certainly unlikely to
clash with any future SQL-isms.
but that seems very minor as the use of backslashes in single and double
quotes is natural and familar for Perl developers.
True. We could recommend always using double quotes - and Perl::Critic's
inevitable nagging be damned. :)
2. Doubling
Ugh. No thanks.
I presume there are no database dialects where two adjacent placeholders
would be valid, so I think it's safe.
Nope, '??' is a perfectly legal operator in Postgres. A backslash, however,
cannot be part of an operator's name. (nor can braces).
3. Vendor escape clauses
Interesting. Probably overkill at this point, but certainly something
we could think about supporting.
$sql_abstract-where({ json_field = { '{verbatim ?}' = $bar } })
I find it hard to imagine people getting excited about writing
that, and I would imagine SQL::Abstract would simply let people write
{ \\? = $bar } and write the long-form behind their back.
No code will break!
Doubling will. But nobody likes that option anyway.
+Some drivers allow you to prevent the recognition of a placeholder by
placing a
+single backslash character (C\) immediately before it. The driver will
remove
+the backslash character and ignore the placeholder, passing it unchanged to
the
+backend. If the driver supports this then L/get_info(9000) will return
true.
Seems a good start. I don't like the phrase prevent recognition of a
placeholder
but it's too late at night to propose an alternate right now.
(I'm undecided about making the get_info(9000) return value a bit-map
Ugh, no. Drivers should escape all placeholder types it supports.
Which likely means that SQL::Abstract and/or DBIx::Class will need to
know as well. (Unless they expose the DBD directly to the user, which
ruins the point a bit).
Not true.
How will a user of SQL::Abstract know if escapes are supported or not?
Isn't the job of such frameworks to release the user from such worries,
or at least present some sort of attribute the user can rely on?
So we will have both escaped and unescaped versions of SQL
floating around, subject to the whims of whether or not your particular
DBD supports it (and in which version).
There's no such thing as escaped and unescaped versions of SQL.
That's a false premise. This is a really important point: the syntax
being proposed *is invalid SQL*.
I'm not sure of the distinction you are making here. The proposed
syntax is invalid, true, and the original syntax may or may not be
invalid, depending on how you look at it. Portable code that won't
know in advance if the underlying dbd supports escaping will need
to have two SQLs paths - one with escaping, and one to do it some
other way.
Then ribasushi, a principle maintainer of SQL::Abstract and DBIx::Class,
pointed out the fatal flaw: numbered placeholders aren't directly composable.
...
Trying to do the same with numbered placeholders (colon or dollar) would
require parsing and rewriting of the SQL. Clearly non-trivial and slow.
For this reason he wouldn't accept such a patch to SQL::Abstract, and I'd
agree with that position.
Non-trivial, but not exactly difficult either, especially considering all the
other contortions said modules are already performing.
Re: Escaping placeholders, take 2
On Dec 28, 2014, at 5:49 AM, Tim Bunce tim.bu...@pobox.com wrote: Question marks are the international standard for placeholders. The DBI should have specified a way to escape them from the start. I'm attempting to fix that now. +1 David smime.p7s Description: S/MIME cryptographic signature
Escaping placeholders, take 2
Wherein I attempt to summarize the requirements, the background, the options, the risks, and the counter-proposal ... ** Background Drivers that support placeholders parse the SQL statement to find them. The parsing is really just simple tokenization that only knows how to skip over quoted strings, quoted identifiers, and comments for the SQL dialect used by the database. The remaining text is checked for placeholders. A question mark is the international SQL standard for placeholders. Per ISO/IEC 9075-2:2003 section 4.24 Dynamic SQL concepts: In SQL-statements that are executed dynamically, the parameters are called dynamic parameters (dynamic parameter specifications) and are represented in SQL language by a question mark (?). Some drivers also support placeholders expressed as a colon ':' followed by a number and/or a name. Some drivers support other forms like a dollar '$' followed by a number. Different types of placeholders cannot be mixed within a statement. The number of parameters passed to execute (etc.) has to match the number of placeholders found in the statement or an error is reported. An individual SQL statement typically comes primarily from one of these sources: 1. Written literally in the source code, perhaps with some $interpolation 2. Constructed dynamically, in whole or part, using a module like SQL::Abstract 3. From an external source, like an SQL script file or 'catalog'. ** Historical Issues and Work-arounds Sometimes the driver will identify placeholders in the SQL statement that weren't intended by the developer. So some drivers have added ad-hoc ways to limit the parsing of placeholders. For example: pg_direct - query passed directly without parsing for placeholders at all pg_placeholder_dollaronly - question marks are not treated as placeholders pg_placeholder_nocolons - colons are not treated as placeholders ora_placeholders - disable processing of all placeholders odbc_ignore_named_placeholders - disable processing of :foo placeholders In most cases these have been added as a workaround for statements that contain colons followed by words that the driver was treating as placeholders. (For example :old and :new when defining Oracle triggers.) The exception is pg_placeholder_dollaronly which was added in 2008 to allow use of postgres geometric operators like ?|| (are parallel) See http://www.postgresql.org/docs/9.4/static/functions-geometry.html So supporting multiple styles of placeholders has both caused problems and allowed drivers to dodge the bullet when one style has become a problem in a particular situation. But that's only a limited solution. It doesn't promote the development and use of generic higher-level reusable modules. (You could frame the progress of software development in terms of enabling developers to work at ever-higher levels of abstraction.) ** Limitation of Existing Workarounds A key problem with these attribute workarounds is that they affect the entire statement. That's a good approach when loading statements from an external source, and often ok when writing individual DBI calls directly where an alternative placeholder style or quote()'ing can be used. In modern application development, however, the DBI is just another foundation layer, low down in the stack. Above it are modules like SQL::Abstract, and ORMs like DBIx::Class. Companies have large and growing investments in these stacks, plus the modules they've built over them to provide abstraction and encapsulation of business logic. For these large applications the attribute workarounds are rarely helpful. Working 'high up' in an application stack that's dynamically constructing SQL with placeholders it should be possible to express any desired behavior. ** Requirements Some mechanism is needed to allow an individual character sequence that normally represents a placeholder, to appear in the SQL without it being treated as a placeholder by the driver. To escape it's usual meaning. If escaping question mark placeholders was already defined in 2008 then pg_placeholder_dollaronly might not have been added to DBD::Pg. The issue of escaping placeholders is on the table now because PostgreSQL supports a JSON type with a rich set of features and operators, some of which use a question mark. For example, the expression json_type_field ? 'bar' is true if the string 'bar' exists as a key/element exist within the JSON value of json_type_field. For more information see http://www.postgresql.org/docs/9.4/static/functions-json.html Use of JSON and these operators is likely to be much more common than use of the geometric operators mentioned earlier. So, while the needs of Postgres users is what prompted this topic now, the requirement is more general. It would be good to agree on an approach that could be applied to most drivers if the need arises. ** Options There seem to be several options worth considering 1. Backslash A backslash could be placed
Re: Escaping placeholders, take 2
Thank you for this post Tim, it seemed to lay out the issues well and make a lot
of things clear.
I'm now inclined to support your backslash-escape proposal at the DBI driver
level in principle.
(I also agree that the doubling method is nasty.)
Or alternately I suggest variation on that proposal that brings in shades of the
vendor escape clauses.
I suggest a variant where instead of a single backslash character indicating an
escape, we have some multi-character thing to indicate it, ideally involving
delimiters so it is more clear on how far the effect of the new escape feature
is supposed to go.
For example, using \{?} rather than \? or \{:foo} rather than \:foo.
One benefit of that is if you have some SQL that contains ? or : numerous times,
you only need to surround the whole fragment with \{} and not individually
escape each occurrence, making for neater code.
As to dealing with say literal { or } in the SQL as I could see reasonably
happening, the quoting mechanism could be made more generic like Perl's q/etc,
so for example it would take the form \X...X where whatever character appears
after the \ is what is matched, and it could be a multiplicity if necessary
within reason, eg \{{{...}}} would just work. For that matter, heredocs or the
quoted printable feature you can see in email messages or such, eg you have
\foo...foo or some such.
I'm speaking in principle here, I'm not proposing a specific feature set, but
both q as well as Perl 6's related quoting mechanism is useful for guidance,
and I think something involving delimiters is best.
But if some of that sounds unduly complicated, I have a better idea.
I propose that the DBI include an API for users to tell the driver what possible
escape delimiters they are using. For example, doable at least at a statement
level (and optionally on a connection/etc level for defaulting if that makes sense).
The API could involve an 'attr' given when preparing a SQL statement or other
appropriate places.
placeholder_escape_delimiters = [ '\{','}' ]
placeholder_escape_delimiters = [ '\{','}','\[',']' ]
placeholder_escape_delimiters = [ '\{{{','}}}' ]
placeholder_escape_delimiters = [ '{{{','}}}' ]
In this way, the backslash is no longer special, or necessary, though I
anticipate it would often still be used for the mnemonics.
Rather, when the driver is parsing the SQL for placeholders, if it encounters
any left delimiter strings, if will leave the following SQL unaltered until it
encounters the corresponding right delimiter string, and then it looks for
placeholders again.
(As to numbered placeholders, which are effectively a special case of named
placeholders, not being directly composable in SQL::Abstract, I see that as
being a problem itself. It would be a great help to developers in principle if
the native way for working with parameters was named rather than positionally.
However, that is really a separate matter to deal with and I think it is a good
idea for Tim's proposal in some form to happen regardless of dealing with this
separate matter.)
-- Darren Duncan
Re: Escaping placeholders, take 2
Hi Darren
On 29/12/14 13:59, Darren Duncan wrote:
The API could involve an 'attr' given when preparing a SQL statement or
other appropriate places.
placeholder_escape_delimiters = [ '\{','}' ]
placeholder_escape_delimiters = [ '\{','}','\[',']' ]
placeholder_escape_delimiters = [ '\{{{','}}}' ]
placeholder_escape_delimiters = [ '{{{','}}}' ]
In this way, the backslash is no longer special, or necessary, though I
anticipate it would often still be used for the mnemonics.
Rather, when the driver is parsing the SQL for placeholders, if it
encounters any left delimiter strings, if will leave the following SQL
unaltered until it encounters the corresponding right delimiter string,
and then it looks for placeholders again.
Perhaps a job for https://metacpan.org/release/Text-Balanced-Marpa
--
Ron Savage - savage.net.au
Re: Escaping placeholders (was: [Dbix-class] Using Postgres JSONB operators in queries)
Sometime over the holiday period I hope to write up a summary of the arguments for and against and suggest ways forwards. Tim.
Re: Escaping placeholders
On 22/12/2014 00:39, Darren Duncan wrote: I agree with Greg's counter-proposal, from which I derive my own words here. 1. I propose that there be no modification to the DBI spec related to new escaping whether optional or not, so leave things the way they are here, SQL continues to be interpreted the way it long has by default. 2. When users want to use operators in PostgreSQL that contain literal ? in their names, then they enable DBD::Pg's pg_placeholder_dollaronly so that ? are no longer treated as placeholders. Likewise, pg_placeholder_nocolons can be enabled when they don't want literal : to indicate a placeholder either. Users would either do this directly if they're using DBI/DBD::Pg directly, or indirectly using their over-top framework of choice. When users aren't using the ? operators et al, they can leave things alone which will work as normal. 3. SQL::Abstract et al, those tools external to DBI/DBDs, are the correct and proper places to modify where users of said want to use the operators with ? names and such. These tools already have special knowledge of individual DBMS systems to work with them effectively, and the ? operators is just one more of those things. The users of said tools may have to flip a configuration switch possibly so $1 etc are used behind the scenes, if necessary, but that's okay because the use of ? operators only happens when the users choose to make a change to use them anyway. In summary, now is not the time or place to be introducing backslashing doubled or otherwise in DBI such as discussed, that's a poor solution and its better to save such risky/etc changes for when there's a more compelling case for them. I should also mention I feel it is perfectly reasonable for each DBMS to have operators composed of any characters they want where doing so makes sense within the context of the SQL/etc itself. See also that Perl itself has both ? and : and etc as operator names, Perl 6 even more so, and I don't see anyone thinking that's a bad idea. So I have no problem with PostgreSQL having ? in operator names such as it did. Its not like the SQL standard reserves ? or whatever for prepared statement parameters, that's defined to be implementation dependent I believe (WD 9075-2:200w(E) 20.6 prepare statement). -- Darren Duncan I tend to agree with Greg and Darren on this for all the same reasons. If it could be done in DBI without affecting DBDs then I might have less of a problem with it as anything wanting this feature would only have to check DBI. There are already too many differences between DBDs and introducing yet another one which would make applications and interfaces even more complex. Plus, this seems to be a postgres specific issue and escaping of ? or : would only benefit other DBDs in the few rare cases where those are legitmate chrs in themselves. There is already an inconsistency in placeholders as some DBDs need/support ':name' when binding and some only support 'name'. DBD::ODBC already has a ignore placeholders switch for when users want to pass SQL containing a ? or : when neither is a placeholder e.g., SQL Server procedures often contain colons. If it was not for some poor ODBC drivers that don't support SQLNumParams DBD::ODBC could get away with not parsing the SQL at all which would be my preferred solution and in fact, the version I use does not parse the SQL at all. Adding escaping would mean more work and probably taking a copy of the scalar for modification. However, I think Greg and Darren's arguments are strong enough on their own. If, it ends up where the DBI spec is changed to allow escaping can we please introduce the DBD capabilities API at the same time. I know I started the thread some time ago and put together an initial list then did not move it forward, but it would make application and interface layers so much easier if they could just say 'does this DBD do X' and maybe 'how do I make it do X'. Martin -- Martin J. Evans Wetherby, UK On 2014-12-21 7:17 AM, Greg Sabino Mullane (the tenth man) wrote: Tim Bunce wrote: For code not using DBIx::Class the pg_placeholder_dollaronly attribute might work, see https://metacpan.org/pod/DBD::Pg#Placeholders Yes, this is the current canonical solution. It's a real shame that ? was used as an operator, but that horse has left the barn. For code using DBIx::Class the problem is more tricky. I'm pretty sure that SQL::Abstract and thus DBIx::Class only support question mark placeholders. That means it probably impossible to use expressions containing a question mark operator with SQL::Abstract/DBIx::Class. (Though I'd be delighted to be proven wrong.) So I think the DBI spec for placeholders needs to be extended to allow a way to 'escape' a question mark that the driver would otherwise treat as a placeholder. The obvious and natural approach would be to use a backslash before a question mark. The backslash would be removed by the driver before
Re: [Dbix-class] Escaping placeholders
On 12/20/2014 03:38 PM, Tim Bunce wrote: On Fri, Dec 19, 2014 at 01:12:16PM +0100, Alexander Foken wrote: Hello all, this reminds me of a similar problem I had in 2000 with DBI, DBD::Oracle, and Oracle. See http://marc.info/?t=9506395904r=1w=2, http://173.79.223.25/?l=dbi-devm=95077716125217w=2. Problem was using named placeholders (:foo) in DBI and at the same time use PL/SQL code containing variables (:bar), DBI considered both :foo and :bar to be placeholders instead of leaving :bar alone and pass it to Oracle. A set of patches from Michael A. Chase allowed disabling parts or all of the placeholder parsing, so using unnamed placeholders (?) allowed using PL/SQL variables in SQL statements. But the fundamental problem was not solved, there was and still is no way to escape placeholders. Can you, or anyone else, think of any situation where a backslash before a ? or :foo (or even $1) style placeholder might be valid SQL? So far no one has come up with one, so I'm getting more comfortable with the idea that a backslash before a placeholder is a safe change. I.e., there's a near-zero risk that upgrading a DBI driver to support backslashes would cause breakage in existing code. I am not sure why but a backlashed version makes me wary... What about ?? instead?
Re: Escaping placeholders
Am 20.12.2014 um 23:10 schrieb Tim Bunce tim.bu...@pobox.com: On Sat, Dec 20, 2014 at 05:35:55PM +0100, Alexander Foken wrote: On 20.12.2014 15:38, Tim Bunce wrote: Can you, or anyone else, think of any situation where a backslash before a ? or :foo (or even $1) style placeholder might be valid SQL? I found two situations for PostgreSQL: (1) PostgreSQL allows almost any character as escape character in Unicode string constants (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE). With that, I can construct an expression containing \:foo that is valid SQL as understood by PostgreSQL: U'foo\:bar' UESCAPE ':' This expression represents the string foo\Xbar, where X is the Unicode character U+ (TAI VIET LETTER LOW VO). I don't think that'll be a problem because the driver code that parses the statement looking for placeholders will skip over quoted strings. Sure, strings can be manipulated on language level, no need for driver to interfere. (2) PostgreSQL also allows Dollar quoting (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING). With that, I can construct an expression containing \$1 that is valid SQL as understood by PostgreSQL: $1$foo\$1$ This expression represents the string foo\, quoted by dollar signs using the character 1 as tag. I'm not sure if the driver code that parses statements in DBD::Pg handles dollar quoting. I presume so. In which case this shouldn't be a problem either for the same reason as above. So far no one has come up with one, so I'm getting more comfortable with the idea that a backslash before a placeholder is a safe change. I.e., there's a near-zero risk that upgrading a DBI driver to support backslashes would cause breakage in existing code. Do you plan to escape the escape character, i.e. use a double backslash at DBI level to represent a single backslash at database level? That's a good question. I'm not sure. I think the answer has to be no. I'd welcome any input on that. We have a dictum (translated word by word ...): One can't even think as foolish as it sometimes happens. When escaping is specified, escaping the escape character itself should be mandatory - just for being complete and do not ask for future trouble. Of course - outside of quotes only. Cheers -- Jens Rehsack rehs...@gmail.com
Re: Escaping placeholders
On 20.12.2014 15:38, Tim Bunce wrote: [...] Can you, or anyone else, think of any situation where a backslash before a ? or :foo (or even $1) style placeholder might be valid SQL? Inside quoted text: of course, yes. Outside quoted text: maybe some RDBMS accept a backslash as a valid character in the name of a table or column? (Haven't tried this out yet.) At least make sure \? will not be handled as placeholder inside column name quoting, as in e.g. foo \? bar or in [foo \? bar] with SQL Server, or `foo \? bar` for MySQL. But my fear is that even foo\?bar would be accepted as valid column or table name by some RDBMS... -- Peter Vanroose, ABIS Training Consulting Leuven, Belgium.
Re: Escaping placeholders
On Sat, Dec 20, 2014 at 02:23:43PM -0800, Jonathan Leffler wrote: INSERT INTO SomeTable(DateTimeCol) VALUES(DATETIME[1](2014-12-31 23\:59\:59) YEAR TO SECOND); I really won't want people have to futz with their legitimate Informix SQL in order to pass it through DBD::Informix. Whatever is provided, whether by DBI or DBD::Informix, must accept the code without the backslashes in front of the colons. It is simply not acceptable to have to modify valid SQL to get it past the gatekeeper code. Understood. I wouldn't expect DBD::Informix to enable this by default. We were only discussing a hypothetical situation where DBD::Informix could optionally enable use of colon placeholders, if desired. At the moment, the unescaped code works fine. It will continue to work fine. As long as DBI does not break the currently working code, I will survive �� like I have for the last decade and more. Just make sure that whatever you do does not break working valid Informix SQL code. I have absolutely no intention of breaking anything :) It'll be up to the individual driver authors to add support for escaping placeholders, if they want to. (The DBI has a built-in preparse function that's intended for parsing placeholders but few, if any, drivers use it. I know DBD::Informix doesn't.) Tim. On Sat, Dec 20, 2014 at 2:17 PM, Tim Bunce [2]tim.bu...@pobox.com wrote: On Sat, Dec 20, 2014 at 01:14:29PM -0800, Jonathan Leffler wrote: Many, many years ago, DBD::Informix had to give up on the DBI-provided parsing for placeholders because there were too many contexts in which it was wrong for Informix. It may have improved since then, but: INSERT INTO SomeTable(DateTimeCol) VALUES(DATETIME(2014-12-31 23:59:59) YEAR TO SECOND); I think I raised this as an issue back in the 1996-1998 timeframe (I said 'many years ago' and meant it). I'd have to dig through my release notes to be more precise. Informix only supports natively the `?` placeholders. It doesn't yet have the complexities introduced by the PostgreSQL operators. I don't know whether this can be handled at all. It may be that DBD::Informix has to stay out in isolation but it would be nice if it wasn't necessary. The `?` placeholders are 'standard' (for some definition) so DBD::Informix isn't really 'in isolation'. There are quite a few drivers that only support `?` placeholders. In theory, if this proposal goes ahead, and is applied to `:` placeholders as seems likely, then you'd be able to write the above as: INSERT INTO SomeTable(DateTimeCol) VALUES(DATETIME[3](2014-12-31 23\:59\:59) YEAR TO SECOND); Tim. -- Jonathan Leffler [4]jonathan.leff...@gmail.com #include disclaimer.h Guardian of DBD::Informix - v2013.0521 - [5]http://dbi.perl.org Blessed are we who can laugh at ourselves, for we shall never cease to be amused. References Visible links 1. file:///tmp/tel:%282014-12-31%2023 2. mailto:tim.bu...@pobox.com 3. file:///tmp/tel:%282014-12-31%2023 4. mailto:jonathan.leff...@gmail.com 5. http://dbi.perl.org/
Re: Escaping placeholders
On 20.12.2014 23:10, Tim Bunce wrote: On Sat, Dec 20, 2014 at 05:35:55PM +0100, Alexander Foken wrote: On 20.12.2014 15:38, Tim Bunce wrote: Can you, or anyone else, think of any situation where a backslash before a ? or :foo (or even $1) style placeholder might be valid SQL? [...] Do you plan to escape the escape character, i.e. use a double backslash at DBI level to represent a single backslash at database level? That's a good question. I'm not sure. I think the answer has to be no. I'd welcome any input on that. The current problem with any kind of DBI placeholders is that there is no way to prevent DBI (including any DBDs) to interpret them as placeholders. This hasn't changed since 2000. The patches from 2000 disable placeholders by type (?, $1, :foo) or entirely, but it is not possible to pass :foo unchanged and uninterpreted to the database while at the same time having a named placeholder :bar. The proposed backslash escape allows exactly this, so you can have a named placeholder :bar and can pass :foo unchanged and uninterpreted to the database, but you have to add a backslash in front of :foo. No problems with old code for this usage, because this was simply not possible. But with the proposed backslash escape, the backslash in SQL statements passed to the DBI will suddenly have a new meaning, were it is currently passed unmodified to the database. This will break old code. Making the backslash escape special only directly in front of possible placeholders will probably break less code, because all other backslashes will behave as before. At the same time, the rules when a backslash is needed will become more complicated, and they may depend on the DBD, because different DBDs implement differrent placeholders (as far as I understand DBI). Also, the rule that a backslash in front of a possible placeholder passes the placeholder, but not the backslash, unmodified to the database prevents passing a backslash followed by an actual placeholder to the database. This is possible with the current DBI code. So, the backslash-is-only-special-in-front-of-placeholders rule breaks some existing code, makes it impossible to have a backslask in front of a placeholder, and the rules for when to use a backslash depend on the DBD implementation. This will likely lead to another change that will again changes the rules and break old code. Can we do better? I think that we can: As there is currently no escaping mechanism for placeholders in DBI, any new escaping mechanism will break some existing code. Breaking old code that uses DBI has happened before, e.g. in DBI 1.20, 24th August 2001 (selectall_hashref) DBI 1.19, 20th July 2001 (fetchall_arrayref), DBI 0.91, 10th December 1997 (connect). 1. Use a clean escaping mechanism that won't need future changes that will again break old code: Make all backslashes in SQL special. Require double backslashes for a single backslash at database level. Require escaping characters that otherwise might be parsed as placeholders (:, ?, $). Make unknown escape sequences fatal errors. This allows mixing placeholders and placeholder-like SQL sequences as before, it allows placing backslashes in front of placeholders, and it allows future extensions using the currently unknown escape sequences. Existing code just has to double each and every backslash in SQL code. 2. Slowly introduce the new mechanism: First, the backslash-escape must explicitly be enabled using an attribute BackslashEscape during connect(). Default is off, i.e. no backslash escaping. No old code should have problems. New code can enable backslash-escapes when needed by using connect() with BackslashEscape=on. After a while, change the default to warn, so that backslash escaping stays off, but any backslash in SQL statements will issue a compatibility warning. Old code will still run unmodified, but will likely issue some warnings. Those warnings can be disabled by adding BackslashEscape=off to connect(). After some more time, make changing BackslashEscape to off cause a compatibility warning. Old unmaintained code will still run unmodified, but issue some warnings. Maintained old code that sets BackslashEscape to off will issue one warning per connect. After even more time, change the default to on, so backslash escaping is enabled. This will intentionally break old unmaintained code that was not modified to switch off BackslashEscape. Old maintained code will continue to run, but issue one warning per connect. Finally, remove the non-backslash-escape code and make swiching to that code (off or warn) a fatal error. This will intentionally break old code that had a one-time maintainance but was not changed to use backslashes properly. The above is the user view of DBI. DBI developer view: As far as I understand the DBI, DBDs may also parse SQL code. They need to be changed to respect the BackslashEscape attribute. Using old
Re: Escaping placeholders
On Sun, Dec 21, 2014 at 10:27:18AM +0100, pe...@vanroose.be wrote: On 20.12.2014 15:38, Tim Bunce wrote: [...] Can you, or anyone else, think of any situation where a backslash before a ? or :foo (or even $1) style placeholder might be valid SQL? Inside quoted text: of course, yes. Outside quoted text: maybe some RDBMS accept a backslash as a valid character in the name of a table or column? (Haven't tried this out yet.) At least make sure \? will not be handled as placeholder inside column name quoting, as in e.g. foo \? bar That's standard identifier quoting so should be ignored by the driver for the same reason that single quoted strings are. or in [foo \? bar] with SQL Server, or `foo \? bar` for MySQL. For databases that support those non-standard identifier quoting styles the driver should be treating them as strings and so skipping them anyway. But my fear is that even foo\?bar would be accepted as valid column or table name by some RDBMS... I find that very hard to believe without any evidence. Even if true, the driver for such a database would, I presume, treat that ? as a placeholder and so it would already be broken. Tim.
Re: Escaping placeholders
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Tim Bunce wrote: For code not using DBIx::Class the pg_placeholder_dollaronly attribute might work, see https://metacpan.org/pod/DBD::Pg#Placeholders Yes, this is the current canonical solution. It's a real shame that ? was used as an operator, but that horse has left the barn. For code using DBIx::Class the problem is more tricky. I'm pretty sure that SQL::Abstract and thus DBIx::Class only support question mark placeholders. That means it probably impossible to use expressions containing a question mark operator with SQL::Abstract/DBIx::Class. (Though I'd be delighted to be proven wrong.) So I think the DBI spec for placeholders needs to be extended to allow a way to 'escape' a question mark that the driver would otherwise treat as a placeholder. The obvious and natural approach would be to use a backslash before a question mark. The backslash would be removed by the driver before the statement is passed to the backend. I'm going to play devil's advocate a bit here. There are some problems with this approach. First, it will require that the user know if the underlying DBD supports backslashes. Which likely means that SQL::Abstract and/or DBIx::Class will need to know as well. (Unless they expose the DBD directly to the user, which ruins the point a bit). Since we will thus need to patch those other modules, so why not fix them to do the right thing? (see below for a counter proposal). Another problem is that we have now extended the SQL syntax for our own purposes. While one could argue that placeholders already do so, their current use is consistent, widespread (e.g. not just DBI), and in part used by the underlying RDBMSs themselves (e.g. Postgres uses dollar-number placeholders). So we will have both escaped and unescaped versions of SQL floating around, subject to the whims of whether or not your particular DBD supports it (and in which version). All of which seems like an awful lot of work to fix SQL::Abstract. Why bother patching every DBD in existence when we can simply patch SQL::Abstract? Which leads to my counter-proposal: have SQL::Abstract accept dollar-number placeholders. It can pass pg_placeholder_dollaronly down the stack as needed. This neatly puts the onus back onto the frameworks, rather than having the DBDs selectively remove backslashes before passing to the RDBMS (ick). DBIx::Class and friends could even map dollar signs back to a format supported by the underlying DBDs, if they don't support dollar signs (that is one of their grand purposes after all - abstracting out details and taking care of things in the background). - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 201412211008 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlSW5E8ACgkQvJuQZxSWSshusgCfXOIjbJxQAF9s7FJEncDFoBGG oHgAnjMs9kP/imrZTnknJpUIuXOhmoPL =FxZ4 -END PGP SIGNATURE-
Re: Escaping placeholders
I agree with Greg's counter-proposal, from which I derive my own words here. 1. I propose that there be no modification to the DBI spec related to new escaping whether optional or not, so leave things the way they are here, SQL continues to be interpreted the way it long has by default. 2. When users want to use operators in PostgreSQL that contain literal ? in their names, then they enable DBD::Pg's pg_placeholder_dollaronly so that ? are no longer treated as placeholders. Likewise, pg_placeholder_nocolons can be enabled when they don't want literal : to indicate a placeholder either. Users would either do this directly if they're using DBI/DBD::Pg directly, or indirectly using their over-top framework of choice. When users aren't using the ? operators et al, they can leave things alone which will work as normal. 3. SQL::Abstract et al, those tools external to DBI/DBDs, are the correct and proper places to modify where users of said want to use the operators with ? names and such. These tools already have special knowledge of individual DBMS systems to work with them effectively, and the ? operators is just one more of those things. The users of said tools may have to flip a configuration switch possibly so $1 etc are used behind the scenes, if necessary, but that's okay because the use of ? operators only happens when the users choose to make a change to use them anyway. In summary, now is not the time or place to be introducing backslashing doubled or otherwise in DBI such as discussed, that's a poor solution and its better to save such risky/etc changes for when there's a more compelling case for them. I should also mention I feel it is perfectly reasonable for each DBMS to have operators composed of any characters they want where doing so makes sense within the context of the SQL/etc itself. See also that Perl itself has both ? and : and etc as operator names, Perl 6 even more so, and I don't see anyone thinking that's a bad idea. So I have no problem with PostgreSQL having ? in operator names such as it did. Its not like the SQL standard reserves ? or whatever for prepared statement parameters, that's defined to be implementation dependent I believe (WD 9075-2:200w(E) 20.6 prepare statement). -- Darren Duncan On 2014-12-21 7:17 AM, Greg Sabino Mullane (the tenth man) wrote: Tim Bunce wrote: For code not using DBIx::Class the pg_placeholder_dollaronly attribute might work, see https://metacpan.org/pod/DBD::Pg#Placeholders Yes, this is the current canonical solution. It's a real shame that ? was used as an operator, but that horse has left the barn. For code using DBIx::Class the problem is more tricky. I'm pretty sure that SQL::Abstract and thus DBIx::Class only support question mark placeholders. That means it probably impossible to use expressions containing a question mark operator with SQL::Abstract/DBIx::Class. (Though I'd be delighted to be proven wrong.) So I think the DBI spec for placeholders needs to be extended to allow a way to 'escape' a question mark that the driver would otherwise treat as a placeholder. The obvious and natural approach would be to use a backslash before a question mark. The backslash would be removed by the driver before the statement is passed to the backend. I'm going to play devil's advocate a bit here. There are some problems with this approach. First, it will require that the user know if the underlying DBD supports backslashes. Which likely means that SQL::Abstract and/or DBIx::Class will need to know as well. (Unless they expose the DBD directly to the user, which ruins the point a bit). Since we will thus need to patch those other modules, so why not fix them to do the right thing? (see below for a counter proposal). Another problem is that we have now extended the SQL syntax for our own purposes. While one could argue that placeholders already do so, their current use is consistent, widespread (e.g. not just DBI), and in part used by the underlying RDBMSs themselves (e.g. Postgres uses dollar-number placeholders). So we will have both escaped and unescaped versions of SQL floating around, subject to the whims of whether or not your particular DBD supports it (and in which version). All of which seems like an awful lot of work to fix SQL::Abstract. Why bother patching every DBD in existence when we can simply patch SQL::Abstract? Which leads to my counter-proposal: have SQL::Abstract accept dollar-number placeholders. It can pass pg_placeholder_dollaronly down the stack as needed. This neatly puts the onus back onto the frameworks, rather than having the DBDs selectively remove backslashes before passing to the RDBMS (ick). DBIx::Class and friends could even map dollar signs back to a format supported by the underlying DBDs, if they don't support dollar signs (that is one of their grand purposes after all - abstracting out details and taking care of things in the background).
Re: Escaping placeholders
Thanks David :) Tim. On Fri, Dec 19, 2014 at 12:23:23PM -0600, David Nicol wrote: Please disregard my previous. After reading Tim Bunce's earlier response and thinking about this some more, although backslash escaping can be tricky, that is how Perl does these things, and Perl coders are familiar with the nuances of when they must be doubled to get through quoting. Doubling of syntax characters is a database thing, but it would have to be revised for every new escapable character. Backslash escaping can be embraced once and will continue to work in potentialfutures where other significant characters (aside from colon and question mark) might need to be escaped too. On Fri, Dec 19, 2014 at 10:27 AM, David Nicol [1]davidni...@gmail.com wrote: I think the suggestion of making ::(\w+) become :$1 and exempting that from placeholder recognition seems like a complete winner and DBD maintainers could do that right away, and by do that I mean accepting, applying, and redistributing patches.. References Visible links 1. mailto:davidni...@gmail.com
Re: Escaping placeholders
On Fri, Dec 19, 2014 at 01:12:16PM +0100, Alexander Foken wrote: Hello all, this reminds me of a similar problem I had in 2000 with DBI, DBD::Oracle, and Oracle. See http://marc.info/?t=9506395904r=1w=2, http://173.79.223.25/?l=dbi-devm=95077716125217w=2. Problem was using named placeholders (:foo) in DBI and at the same time use PL/SQL code containing variables (:bar), DBI considered both :foo and :bar to be placeholders instead of leaving :bar alone and pass it to Oracle. A set of patches from Michael A. Chase allowed disabling parts or all of the placeholder parsing, so using unnamed placeholders (?) allowed using PL/SQL variables in SQL statements. But the fundamental problem was not solved, there was and still is no way to escape placeholders. Can you, or anyone else, think of any situation where a backslash before a ? or :foo (or even $1) style placeholder might be valid SQL? So far no one has come up with one, so I'm getting more comfortable with the idea that a backslash before a placeholder is a safe change. I.e., there's a near-zero risk that upgrading a DBI driver to support backslashes would cause breakage in existing code. Tim.
Re: Escaping placeholders
On 20.12.2014 15:38, Tim Bunce wrote: On Fri, Dec 19, 2014 at 01:12:16PM +0100, Alexander Foken wrote: Hello all, this reminds me of a similar problem I had in 2000 with DBI, DBD::Oracle, and Oracle. See http://marc.info/?t=9506395904r=1w=2, http://173.79.223.25/?l=dbi-devm=95077716125217w=2. Problem was using named placeholders (:foo) in DBI and at the same time use PL/SQL code containing variables (:bar), DBI considered both :foo and :bar to be placeholders instead of leaving :bar alone and pass it to Oracle. A set of patches from Michael A. Chase allowed disabling parts or all of the placeholder parsing, so using unnamed placeholders (?) allowed using PL/SQL variables in SQL statements. But the fundamental problem was not solved, there was and still is no way to escape placeholders. Can you, or anyone else, think of any situation where a backslash before a ? or :foo (or even $1) style placeholder might be valid SQL? I found two situations for PostgreSQL: (1) PostgreSQL allows almost any character as escape character in Unicode string constants (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE). With that, I can construct an expression containing \:foo that is valid SQL as understood by PostgreSQL: U'foo\:bar' UESCAPE ':' This expression represents the string foo\Xbar, where X is the Unicode character U+ (TAI VIET LETTER LOW VO). (2) PostgreSQL also allows Dollar quoting (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING). With that, I can construct an expression containing \$1 that is valid SQL as understood by PostgreSQL: $1$foo\$1$ This expression represents the string foo\, quoted by dollar signs using the character 1 as tag. So far no one has come up with one, so I'm getting more comfortable with the idea that a backslash before a placeholder is a safe change. I.e., there's a near-zero risk that upgrading a DBI driver to support backslashes would cause breakage in existing code. Do you plan to escape the escape character, i.e. use a double backslash at DBI level to represent a single backslash at database level? Alexander Tim. -- Alexander Foken mailto:alexan...@foken.de http://www.foken.de/alexander/
Re: Escaping placeholders
Many, many years ago, DBD::Informix had to give up on the DBI-provided parsing for placeholders because there were too many contexts in which it was wrong for Informix. It may have improved since then, but: INSERT INTO SomeTable(DateTimeCol) VALUES(DATETIME(2014-12-31 23:59:59) YEAR TO SECOND); is valid Informix-dialect SQL with no placeholders whatsoever. The Standard SQL notation would be: VALUES(TIMESTAMP '2014-12-31 23:59:59') and many systems would likely get away without needing the TIMESTAMP (automatically coercing the string into the correct type). Indeed, Informix accepts the string notation (without the TIMESTAMP prefix), but the other notation is also valid and therefore DBD::Informix must support it. The standard notation avoids problems because the colons are embedded within strings, whereas the Informix DATETIME literal notation uses parentheses instead of quotes around what is otherwise effectively a string. I could also insert INTERVAL(23 13:45:19) DAY TO SECOND with similar comments about its behaviour. Also, Informix uses the notation: dbase@server:owner.table for a fully qualified table name, with no placeholders present. You can omit the '@server' to reference the current server (hence dbase:owner.table is valid too); you can omit the 'owner.' if you want to refer to a table (depending on context) owned by the current user or uniquely named belonging to an arbitrary user (so dbase:table is valid too). You can omit the dbase altogether, but then the colon isn't present and it is no longer relevant to the handling of placeholders prefixed by colon. This leads to the synoptic notation: [dbase[@server]:][owner.]table I think I raised this as an issue back in the 1996-1998 timeframe (I said 'many years ago' and meant it). I'd have to dig through my release notes to be more precise. Informix only supports natively the `?` placeholders. It doesn't yet have the complexities introduced by the PostgreSQL operators. I don't know whether this can be handled at all. It may be that DBD::Informix has to stay out in isolation — but it would be nice if it wasn't necessary. On Sat, Dec 20, 2014 at 8:35 AM, Alexander Foken alexan...@foken.de wrote: On 20.12.2014 15:38, Tim Bunce wrote: On Fri, Dec 19, 2014 at 01:12:16PM +0100, Alexander Foken wrote: Hello all, this reminds me of a similar problem I had in 2000 with DBI, DBD::Oracle, and Oracle. See http://marc.info/?t=9506395904r=1w=2, http://173.79.223.25/?l=dbi-devm=95077716125217w=2. Problem was using named placeholders (:foo) in DBI and at the same time use PL/SQL code containing variables (:bar), DBI considered both :foo and :bar to be placeholders instead of leaving :bar alone and pass it to Oracle. A set of patches from Michael A. Chase allowed disabling parts or all of the placeholder parsing, so using unnamed placeholders (?) allowed using PL/SQL variables in SQL statements. But the fundamental problem was not solved, there was and still is no way to escape placeholders. Can you, or anyone else, think of any situation where a backslash before a ? or :foo (or even $1) style placeholder might be valid SQL? I found two situations for PostgreSQL: (1) PostgreSQL allows almost any character as escape character in Unicode string constants (http://www.postgresql.org/docs/current/static/sql- syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE). With that, I can construct an expression containing \:foo that is valid SQL as understood by PostgreSQL: U'foo\:bar' UESCAPE ':' This expression represents the string foo\Xbar, where X is the Unicode character U+ (TAI VIET LETTER LOW VO). (2) PostgreSQL also allows Dollar quoting (http://www.postgresql.org/ docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING). With that, I can construct an expression containing \$1 that is valid SQL as understood by PostgreSQL: $1$foo\$1$ This expression represents the string foo\, quoted by dollar signs using the character 1 as tag. So far no one has come up with one, so I'm getting more comfortable with the idea that a backslash before a placeholder is a safe change. I.e., there's a near-zero risk that upgrading a DBI driver to support backslashes would cause breakage in existing code. Do you plan to escape the escape character, i.e. use a double backslash at DBI level to represent a single backslash at database level? Alexander Tim. -- Alexander Foken mailto:alexan...@foken.de http://www.foken.de/alexander/ -- Jonathan Leffler jonathan.leff...@gmail.com #include disclaimer.h Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org Blessed are we who can laugh at ourselves, for we shall never cease to be amused.
Re: Escaping placeholders
On Sat, Dec 20, 2014 at 05:35:55PM +0100, Alexander Foken wrote: On 20.12.2014 15:38, Tim Bunce wrote: Can you, or anyone else, think of any situation where a backslash before a ? or :foo (or even $1) style placeholder might be valid SQL? I found two situations for PostgreSQL: (1) PostgreSQL allows almost any character as escape character in Unicode string constants (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE). With that, I can construct an expression containing \:foo that is valid SQL as understood by PostgreSQL: U'foo\:bar' UESCAPE ':' This expression represents the string foo\Xbar, where X is the Unicode character U+ (TAI VIET LETTER LOW VO). I don't think that'll be a problem because the driver code that parses the statement looking for placeholders will skip over quoted strings. (2) PostgreSQL also allows Dollar quoting (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING). With that, I can construct an expression containing \$1 that is valid SQL as understood by PostgreSQL: $1$foo\$1$ This expression represents the string foo\, quoted by dollar signs using the character 1 as tag. I'm not sure if the driver code that parses statements in DBD::Pg handles dollar quoting. I presume so. In which case this shouldn't be a problem either for the same reason as above. So far no one has come up with one, so I'm getting more comfortable with the idea that a backslash before a placeholder is a safe change. I.e., there's a near-zero risk that upgrading a DBI driver to support backslashes would cause breakage in existing code. Do you plan to escape the escape character, i.e. use a double backslash at DBI level to represent a single backslash at database level? That's a good question. I'm not sure. I think the answer has to be no. I'd welcome any input on that. Tim.
Re: Escaping placeholders
On Sat, Dec 20, 2014 at 01:14:29PM -0800, Jonathan Leffler wrote: Many, many years ago, DBD::Informix had to give up on the DBI-provided parsing for placeholders because there were too many contexts in which it was wrong for Informix. It may have improved since then, but: INSERT INTO SomeTable(DateTimeCol) VALUES(DATETIME(2014-12-31 23:59:59) YEAR TO SECOND); I think I raised this as an issue back in the 1996-1998 timeframe (I said 'many years ago' and meant it). I'd have to dig through my release notes to be more precise. Informix only supports natively the `?` placeholders. It doesn't yet have the complexities introduced by the PostgreSQL operators. I don't know whether this can be handled at all. It may be that DBD::Informix has to stay out in isolation but it would be nice if it wasn't necessary. The `?` placeholders are 'standard' (for some definition) so DBD::Informix isn't really 'in isolation'. There are quite a few drivers that only support `?` placeholders. In theory, if this proposal goes ahead, and is applied to `:` placeholders as seems likely, then you'd be able to write the above as: INSERT INTO SomeTable(DateTimeCol) VALUES(DATETIME(2014-12-31 23\:59\:59) YEAR TO SECOND); Tim.
Re: Escaping placeholders
Gmail hid the line you gave (as if it was the same as something I'd sent — curious behaviour by Gmail): INSERT INTO SomeTable(DateTimeCol) VALUES(DATETIME(2014-12-31 23\:59\:59) YEAR TO SECOND); I really won't want people have to futz with their legitimate Informix SQL in order to pass it through DBD::Informix. Whatever is provided, whether by DBI or DBD::Informix, must accept the code without the backslashes in front of the colons. It is simply not acceptable to have to modify valid SQL to get it past the gatekeeper code. At the moment, the unescaped code works fine. It will continue to work fine. As long as DBI does not break the currently working code, I will survive — like I have for the last decade and more. Just make sure that whatever you do does not break working valid Informix SQL code. On Sat, Dec 20, 2014 at 2:17 PM, Tim Bunce tim.bu...@pobox.com wrote: On Sat, Dec 20, 2014 at 01:14:29PM -0800, Jonathan Leffler wrote: Many, many years ago, DBD::Informix had to give up on the DBI-provided parsing for placeholders because there were too many contexts in which it was wrong for Informix. It may have improved since then, but: INSERT INTO SomeTable(DateTimeCol) VALUES(DATETIME(2014-12-31 23:59:59) YEAR TO SECOND); I think I raised this as an issue back in the 1996-1998 timeframe (I said 'many years ago' and meant it). I'd have to dig through my release notes to be more precise. Informix only supports natively the `?` placeholders. It doesn't yet have the complexities introduced by the PostgreSQL operators. I don't know whether this can be handled at all. It may be that DBD::Informix has to stay out in isolation but it would be nice if it wasn't necessary. The `?` placeholders are 'standard' (for some definition) so DBD::Informix isn't really 'in isolation'. There are quite a few drivers that only support `?` placeholders. In theory, if this proposal goes ahead, and is applied to `:` placeholders as seems likely, then you'd be able to write the above as: INSERT INTO SomeTable(DateTimeCol) VALUES(DATETIME(2014-12-31 23\:59\:59) YEAR TO SECOND); Tim. -- Jonathan Leffler jonathan.leff...@gmail.com #include disclaimer.h Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org Blessed are we who can laugh at ourselves, for we shall never cease to be amused.
Escaping placeholders (was: [Dbix-class] Using Postgres JSONB operators in queries)
Hello Augustus.
On Thu, Dec 04, 2014 at 03:38:14PM -0800, Augustus Saunders wrote:
Hi all, I have been unable to find a way to use some of the new
JSONB operators in Postgres 9.4 with DBIx::Class. A quick search
for JSONB on the email archive site didn't turn up any results, so
I thought I would ask.
In particular, ?, ?, and ?| are now operators, and we run into
problems with the DBI placeholder being ?.
As more people start using Postgres 9.4 and the JSON operators this
is going to become a significant problem.
I read that putting single quotes around the question mark would
allow a literal question mark in DBI, but I can't seem to make this
work from DBIx::Class.
It won't do what you want. Question marks in quotes are ignored by the
DBI driver, but question marks in quotes won't work as JSON operators.
Can anybody tell me whether this is currently possible, if so how,
and if not what might be involved or where in the code to look?
For code not using DBIx::Class the pg_placeholder_dollaronly attribute
might work, see https://metacpan.org/pod/DBD::Pg#Placeholders
For code using DBIx::Class the problem is more tricky. I'm pretty sure
that SQL::Abstract and thus DBIx::Class only support question mark
placeholders. That means it probably impossible to use expressions
containing a question mark operator with SQL::Abstract/DBIx::Class.
(Though I'd be delighted to be proven wrong.)
So I think the DBI spec for placeholders needs to be extended to allow a
way to 'escape' a question mark that the driver would otherwise treat as
a placeholder.
The obvious and natural approach would be to use a backslash before a
question mark. The backslash would be removed by the driver before the
statement is passed to the backend.
$dbh-selectrow_array(q{ SELECT {a:1, b:2}'::jsonb ? 'b' }); # breaks
$dbh-selectrow_array(q{ SELECT {a:1, b:2}'::jsonb \? 'b' }); # would work
The key question is: what is the risk of any existing DBI SQL statements
containing a question mark placeholder that's preceeded by a backslash?
Can anyone think of realistic examples? (For ANY DBI driver or backend.)
Tim.
Re: Escaping placeholders
Hello all,
this reminds me of a similar problem I had in 2000 with DBI,
DBD::Oracle, and Oracle. See
http://marc.info/?t=9506395904r=1w=2,
http://173.79.223.25/?l=dbi-devm=95077716125217w=2.
Problem was using named placeholders (:foo) in DBI and at the same
time use PL/SQL code containing variables (:bar), DBI considered both
:foo and :bar to be placeholders instead of leaving :bar alone and
pass it to Oracle. A set of patches from Michael A. Chase allowed
disabling parts or all of the placeholder parsing, so using unnamed
placeholders (?) allowed using PL/SQL variables in SQL statements.
But the fundamental problem was not solved, there was and still is no
way to escape placeholders.
I think being able to escape placeholders (both unnamed AND named ones)
would help. A backslash seems reasonable, but doubling the question mark
resp. the colon works with single and double quoting, you don't have to
think about escaping backslashes in double quoted strings. And with
doubling, you don't need to quote backslashes.
My ancient problem could have been solved by simply replacing every
colon in the PL/SQL code with either a backslash and a colon or two colons.
Alexander
On 19.12.2014 11:40, Tim Bunce wrote:
Hello Augustus.
On Thu, Dec 04, 2014 at 03:38:14PM -0800, Augustus Saunders wrote:
Hi all, I have been unable to find a way to use some of the new
JSONB operators in Postgres 9.4 with DBIx::Class. A quick search
for JSONB on the email archive site didn't turn up any results, so
I thought I would ask.
In particular, ?, ?, and ?| are now operators, and we run into
problems with the DBI placeholder being ?.
As more people start using Postgres 9.4 and the JSON operators this
is going to become a significant problem.
I read that putting single quotes around the question mark would
allow a literal question mark in DBI, but I can't seem to make this
work from DBIx::Class.
It won't do what you want. Question marks in quotes are ignored by the
DBI driver, but question marks in quotes won't work as JSON operators.
Can anybody tell me whether this is currently possible, if so how,
and if not what might be involved or where in the code to look?
For code not using DBIx::Class the pg_placeholder_dollaronly attribute
might work, see https://metacpan.org/pod/DBD::Pg#Placeholders
For code using DBIx::Class the problem is more tricky. I'm pretty sure
that SQL::Abstract and thus DBIx::Class only support question mark
placeholders. That means it probably impossible to use expressions
containing a question mark operator with SQL::Abstract/DBIx::Class.
(Though I'd be delighted to be proven wrong.)
So I think the DBI spec for placeholders needs to be extended to allow a
way to 'escape' a question mark that the driver would otherwise treat as
a placeholder.
The obvious and natural approach would be to use a backslash before a
question mark. The backslash would be removed by the driver before the
statement is passed to the backend.
$dbh-selectrow_array(q{ SELECT {a:1, b:2}'::jsonb ? 'b' }); # breaks
$dbh-selectrow_array(q{ SELECT {a:1, b:2}'::jsonb \? 'b' }); # would
work
The key question is: what is the risk of any existing DBI SQL statements
containing a question mark placeholder that's preceeded by a backslash?
Can anyone think of realistic examples? (For ANY DBI driver or backend.)
Tim.
--
Alexander Foken
mailto:alexan...@foken.de http://www.foken.de/alexander/
Re: Escaping placeholders
On Fri, Dec 19, 2014 at 6:12 AM, Alexander Foken alexan...@foken.de wrote: Problem was using named placeholders (:foo) in DBI and at the same time use PL/SQL code containing variables (:bar), DBI considered both :foo and :bar to be placeholders instead of leaving :bar alone and pass it to Oracle. But the fundamental problem was not solved, there was and still is no way to escape placeholders. I think the suggestion of making ::(\w+) become :$1 and exempting that from placeholder recognition seems like a complete winner and DBD maintainers could do that right away, and by do that I mean accepting, applying, and redistributing patches..
Re: Escaping placeholders
Please disregard my previous. After reading Tim Bunce's earlier response and thinking about this some more, although backslash escaping can be tricky, that is how Perl does these things, and Perl coders are familiar with the nuances of when they must be doubled to get through quoting. Doubling of syntax characters is a database thing, but it would have to be revised for every new escapable character. Backslash escaping can be embraced once and will continue to work in potentialfutures where other significant characters (aside from colon and question mark) might need to be escaped too. On Fri, Dec 19, 2014 at 10:27 AM, David Nicol davidni...@gmail.com wrote: I think the suggestion of making ::(\w+) become :$1 and exempting that from placeholder recognition seems like a complete winner and DBD maintainers could do that right away, and by do that I mean accepting, applying, and redistributing patches..
