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.
