Unicode string literals versus the world
So I started to look at what might be involved in teaching plpgsql about
standard_conforming_strings, and was soon dismayed by the sheer epic
nature of its failure to act like the core lexer. It was shaky enough
before, but the recent introduction of Unicode strings and identifiers
into the core has left plpgsql hopelessly behind.
I can see two basic approaches to making things work: copy-and-paste
practically all of parser/scan.l into plpgsql's lexer (certainly all of
it that involves exclusive states); or throw out plpgsql's lexer
altogether in favor of somehow using the core lexer directly. Neither
one looks very attractive.
It gets worse though: I have seldom seen such a badly designed piece of
syntax as the Unicode string syntax --- see
http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE
You scan the string, and then after that they tell you what the escape
character is!? Not to mention the obvious ambiguity with & as an
operator.
If we let this go into 8.4, our previous rounds with security holes
caused by careless string parsing will look like a day at the beach.
No frontend that isn't fully cognizant of the Unicode string syntax is
going to parse such things correctly --- it's going to be trivial for
a bad guy to confuse a quoting mechanism as to what's an escape and what
isn't.
I think we need to give very serious consideration to ripping out that
"feature".
regards, tom lane
On Fri, Apr 10, 2009 at 05:54:25PM -0400, Tom Lane wrote:
It gets worse though: I have seldom seen such a badly designed piece of
syntax as the Unicode string syntax --- see
http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPEI think we need to give very serious consideration to ripping out that
"feature".
I'd agree it's comically bad syntax; I just had a look in the archives
and it was only put in a few months ago:
http://archives.postgresql.org/pgsql-hackers/2008-10/msg01169.php
I can't see much support in the other database engines; searched for
Oracle, MS-SQL, DB2 and Firebird. MySQL has it planned for 7.1, so not
for a while.
http://forge.mysql.com/worklog/task.php?id=3529
--
Sam http://samason.me.uk/
Tom Lane wrote:
It gets worse though: I have seldom seen such a badly designed piece of
syntax as the Unicode string syntax --- see
http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPEYou scan the string, and then after that they tell you what the escape
character is!? Not to mention the obvious ambiguity with & as an
operator.If we let this go into 8.4, our previous rounds with security holes
caused by careless string parsing will look like a day at the beach.
No frontend that isn't fully cognizant of the Unicode string syntax is
going to parse such things correctly --- it's going to be trivial for
a bad guy to confuse a quoting mechanism as to what's an escape and what
isn't.I think we need to give very serious consideration to ripping out that
"feature".
+1
I don't recall a great deal of discussion about it, and it certainly
looks pretty horrible now you point it out.
cheers
andrew
On 4/11/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It gets worse though: I have seldom seen such a badly designed piece of
syntax as the Unicode string syntax --- see
http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPEYou scan the string, and then after that they tell you what the escape
character is!? Not to mention the obvious ambiguity with & as an
operator.If we let this go into 8.4, our previous rounds with security holes
caused by careless string parsing will look like a day at the beach.
No frontend that isn't fully cognizant of the Unicode string syntax is
going to parse such things correctly --- it's going to be trivial for
a bad guy to confuse a quoting mechanism as to what's an escape and what
isn't.I think we need to give very serious consideration to ripping out that
"feature".
Ugh, it's rather dubious indeed. Especially when we are already in
the middle of seriously confusing conversion from stdstr=off -> on.
Is it really OK to introduce even more complexity in the mix?
Alternative proposal - maybe it would be saner to introduce \uXXXX
escape to E'' strings as a non-standard way for quoting unicode.
Later when the standard quoting is our only quoting method we can play
with standard extensions?
--
marko
On 4/11/09 11:47 AM, Marko Kreen wrote:
On 4/11/09, Tom Lane<tgl@sss.pgh.pa.us> wrote:
It gets worse though: I have seldom seen such a badly designed piece of
syntax as the Unicode string syntax --- see
http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE
WTF? Whose feature is this? What's the use case?
Speak up, submitter, please.
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
On 4/11/09, Tom Lane<tgl@sss.pgh.pa.us> wrote:
http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE
WTF? Whose feature is this? What's the use case?
Peter put it in, I think. It is in the SQL:2008 spec, but that doesn't
change the fact that it's a horribly bad piece of design.
regards, tom lane
Peter put it in, I think. It is in the SQL:2008 spec, but that doesn't
change the fact that it's a horribly bad piece of design.
Hmmm. We're not going to implement *everything* in the spec; nobody
does, even IBM. I think maybe these kinds of additions need to be
hashed out for value so we don't waste Peter's work in the future. Peter?
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
On Saturday 11 April 2009 00:54:25 Tom Lane wrote:
It gets worse though: I have seldom seen such a badly designed piece of
syntax as the Unicode string syntax --- see
http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#SQL
-SYNTAX-STRINGS-UESCAPEYou scan the string, and then after that they tell you what the escape
character is!? Not to mention the obvious ambiguity with & as an
operator.If we let this go into 8.4, our previous rounds with security holes
caused by careless string parsing will look like a day at the beach.
No frontend that isn't fully cognizant of the Unicode string syntax is
going to parse such things correctly --- it's going to be trivial for
a bad guy to confuse a quoting mechanism as to what's an escape and what
isn't.
Note that the escape character marks the Unicode escapes; it doesn't affect the
quote characters that delimit the string. So offhand I can't see any potential
for quote confusion/SQL injection type problems. Please elaborate if you see
a problem.
If there are problems, we could consider getting rid of the UESCAPE clause.
Without it, the U&'' strings would behave much like the E'' strings. But I'd
like to understand the problem first.
On Saturday 11 April 2009 21:50:29 Josh Berkus wrote:
On 4/11/09 11:47 AM, Marko Kreen wrote:
On 4/11/09, Tom Lane<tgl@sss.pgh.pa.us> wrote:
It gets worse though: I have seldom seen such a badly designed piece
of syntax as the Unicode string syntax --- seehttp://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#
SQL-SYNTAX-STRINGS-UESCAPEWTF? Whose feature is this? What's the use case?
The use case is approximately the same as &#xxxx; in HTML: entering Unicode
characters that your screen or keyboard cannot easily produce. It's a
desperately needed feature for me.
On 4/14/09, Peter Eisentraut <peter_e@gmx.net> wrote:
On Saturday 11 April 2009 00:54:25 Tom Lane wrote:
It gets worse though: I have seldom seen such a badly designed piece of
syntax as the Unicode string syntax --- see
http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#SQL
-SYNTAX-STRINGS-UESCAPEYou scan the string, and then after that they tell you what the escape
character is!? Not to mention the obvious ambiguity with & as an
operator.If we let this go into 8.4, our previous rounds with security holes
caused by careless string parsing will look like a day at the beach.
No frontend that isn't fully cognizant of the Unicode string syntax is
going to parse such things correctly --- it's going to be trivial for
a bad guy to confuse a quoting mechanism as to what's an escape and what
isn't.Note that the escape character marks the Unicode escapes; it doesn't affect the
quote characters that delimit the string. So offhand I can't see any potential
for quote confusion/SQL injection type problems. Please elaborate if you see
a problem.If there are problems, we could consider getting rid of the UESCAPE clause.
Without it, the U&'' strings would behave much like the E'' strings. But I'd
like to understand the problem first.
I think the problem is that they should not act like E'' strings, but they
should act like plain '' strings - they should follow stdstr setting.
That way existing tools that may (or may not..) understand E'' and stdstr
settings, but definitely have not heard about U&'' strings can still
parse the SQL without new surprises.
If they already act that way then keeping U& should be fine.
And if UESCAPE does not affect main string parsing, but is handled in
second pass going over parsed string - like bytea \ - then that should
also be fine and should not cause any new surprises.
But if not, it must go.
I would prefer that such quoting extensions would wait until
stdstr=on setting is the only mode Postgres will operate.
Fitting new quoting ways to environment with flippable stdstr setting
will be rather painful for everyone.
I still stand on my proposal, how about extending E'' strings with
unicode escapes (eg. \uXXXX)? The E'' strings are already more
clearly defined than '' and they are our "own", we don't need to
consider random standards, but can consider our sanity.
--
marko
Marko Kreen wrote:
I still stand on my proposal, how about extending E'' strings with
unicode escapes (eg. \uXXXX)? The E'' strings are already more
clearly defined than '' and they are our "own", we don't need to
consider random standards, but can consider our sanity.
I suspect there would be lots more support in the user community, where
\uXXXX is well understood in a number of contexts (Java and ECMAScript,
for example). It's also tolerably sane.
cheers
andrew
On Tuesday 14 April 2009 14:38:38 Marko Kreen wrote:
I think the problem is that they should not act like E'' strings, but they
should act like plain '' strings - they should follow stdstr setting.That way existing tools that may (or may not..) understand E'' and stdstr
settings, but definitely have not heard about U&'' strings can still
parse the SQL without new surprises.
Can you be more specific in what "surprises" you expect? What algorithms do
you suppose those "existing tools" use and what expectations do they have?
I still stand on my proposal, how about extending E'' strings with
unicode escapes (eg. \uXXXX)? The E'' strings are already more
clearly defined than '' and they are our "own", we don't need to
consider random standards, but can consider our sanity.
This doesn't excite me. I think the tendency should be to get rid of E''
usage, because its definition of escape sequences is single-byte and ASCII
centric and thus overall a legacy construct. Certainly, we will want to keep
around E'' for a long time or forever, but it is a legitimate goal for
application writers to not use it, which is after all the reason behind this
whole standards-conforming strings project. I wouldn't want to have a
forward-looking feature such as the Unicode escapes be burdened with that kind
of legacy behavior.
Also note that Unicode escapes are also available for identifiers, for which
there is no existing E"" that you can add it to.
On Saturday 11 April 2009 18:20:47 Sam Mason wrote:
I can't see much support in the other database engines; searched for
Oracle, MS-SQL, DB2 and Firebird. MySQL has it planned for 7.1, so not
for a while.
DB2 supports it, as far as I know.
On 4/14/09, Peter Eisentraut <peter_e@gmx.net> wrote:
On Tuesday 14 April 2009 14:38:38 Marko Kreen wrote:
I think the problem is that they should not act like E'' strings, but they
should act like plain '' strings - they should follow stdstr setting.That way existing tools that may (or may not..) understand E'' and stdstr
settings, but definitely have not heard about U&'' strings can still
parse the SQL without new surprises.Can you be more specific in what "surprises" you expect? What algorithms do
you suppose those "existing tools" use and what expectations do they have?
If the parsing does not happen in 2 passes and it does not take account
of stdstr setting then the default breakage would be:
stdstr=off, U&' \' UESCAPE '!'.
And anything, whose security or functionality depends on parsing SQL
can be broken that way.
Broken functionality would be eg. Slony (or other replication solution)
distributing developer-written SQL code to bunch of nodes. It needs to
parse text file to SQL statements and execute them separately.
There are probably other solutions who expect to understand SQL
at least token level to function correctly. (pgpool, java has
probably something depending on it, etc.)
I still stand on my proposal, how about extending E'' strings with
unicode escapes (eg. \uXXXX)? The E'' strings are already more
clearly defined than '' and they are our "own", we don't need to
consider random standards, but can consider our sanity.This doesn't excite me. I think the tendency should be to get rid of E''
usage, because its definition of escape sequences is single-byte and ASCII
centric and thus overall a legacy construct.
Why are you concentrating only on \0xx escapes? The \\, \n, etc
seem standard and forward looking enough. Yes, unicode escapes are
missing but we can add them without breaking anything.
Certainly, we will want to keep
around E'' for a long time or forever, but it is a legitimate goal for
application writers to not use it, which is after all the reason behind this
whole standards-conforming strings project. I wouldn't want to have a
forward-looking feature such as the Unicode escapes be burdened with that kind
of legacy behavior.Also note that Unicode escapes are also available for identifiers, for which
there is no existing E"" that you can add it to.
Well, I was not rejecting the standard quoting, but suggesting
postponing until the stdstr mess is sorted out. We can use \uXX
in meantime and I think most Postgres users would prefer to keep
using it...
--
marko
Peter Eisentraut <peter_e@gmx.net> writes:
On Saturday 11 April 2009 00:54:25 Tom Lane wrote:
If we let this go into 8.4, our previous rounds with security holes
caused by careless string parsing will look like a day at the beach.
Note that the escape character marks the Unicode escapes; it doesn't
affect the quote characters that delimit the string. So offhand I
can't see any potential for quote confusion/SQL injection type
problems. Please elaborate if you see a problem.
The problem is the interaction with non-standard-conforming strings.
Case 1:
select u&'foo\' uescape ',' ...
The backend will see the backslash as just a data character, and
will think that "..." is live SQL text. A non-Unicode-literal-aware
frontend will think that the backslash escapes the second quote, the
comma is live SQL text, and the ... is quoted material. Construction
of an actual SQL injection attack is left as an exercise for the reader,
but certainly the raw material is here.
Case 2:
select u&'foo' uescape '\' ...
Again, any existing frontend code will think that the backslash quotes
the final quote and the ... is quoted material. This one is
particularly nasty because we allow arbitrary amounts of whitespace and
commenting on either side of "uescape":
select u&'foo'
/* hello joe, do you /* understand nested comments today?
*/
-- yes, this one too */
uescape
-- but not this one /*
'\' ...
I suspect that it's actually impossible to parse such a thing correctly
without a full-fledged flex lexer or something of equivalent complexity.
Certainly it's a couple of orders of magnitude harder than it is for
either standard-conforming or E'' literals.
Case 3:
select u&'foo\' uescape ',' ...
select u & 'foo\' uescape ',' ...
In the first form the ... is live SQL, in the second form it is quoted
material. This means that you might correctly validate a query and then
have your results invalidated by later processing that innocently adds
or removes whitespace. (This is particularly nasty in a standard that
demands we parse "x/-1" and "x / -1" the same ...)
So what we've got here is a whole new set of potential SQL injection
attacks by confusing frontend literal-syntax checking, plus a truly
staggering increase in the minimum *required* complexity of such
checking. I understand the usefulness of being able to write Unicode
code points, but they're not useful enough to justify this syntax.
This thread has already mentioned a couple of ways we could add the
same facility without making any lexical-level changes, at least for
data values. I admit that the SQL:2008 way also covers Unicode code
points in identifiers, which we can't emulate without a lexical change;
but frankly I think the use-case for that is so thin as to be almost
nonexistent. Who is going to choose identifiers that they cannot easily
type on their keyboards?
regards, tom lane
Marko Kreen <markokr@gmail.com> writes:
I would prefer that such quoting extensions would wait until
stdstr=on setting is the only mode Postgres will operate.
Fitting new quoting ways to environment with flippable stdstr setting
will be rather painful for everyone.
It would certainly be a lot safer to wait until non-standard-conforming
strings don't exist anymore. The problem is that that may never happen,
and is certainly not on the roadmap to happen in the foreseeable future.
I still stand on my proposal, how about extending E'' strings with
unicode escapes (eg. \uXXXX)? The E'' strings are already more
clearly defined than '' and they are our "own", we don't need to
consider random standards, but can consider our sanity.
That's one way we could proceed. The other proposal that seemed
attractive to me was a decode-like function:
uescape('foo\00e9bar')
uescape('foo\00e9bar', '\')
(double all the backslashes if you assume not
standard_conforming_strings). The arguments in favor of this one
are (1) you can apply it to the result of an expression, it's not
strictly tied to literals; and (2) it's a lot lower-footprint solution
since it doesn't affect basic literal handling. If you wish to suppose
that this is only a stopgap until someday when we can implement the SQL
standard syntax more safely, then low footprint is good. One could
even imagine back-porting this into existing releases as a user-defined
function.
The solution with \u in extended literals is probably workable too.
I'm slightly worried about the possibility of issues with code that
thinks it knows what an E-literal means but doesn't really. In
particular something might think it knows that "\u" just means "u",
and proceed to strip the backslash. I don't see a path for that to
become a security hole though, only a garden-variety bug. So I could
live with that one on the grounds of being easier to use (which it
would be, because of less typing compared to uescape()).
regards, tom lane
On Tue, Apr 14, 2009 at 8:53 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
This doesn't excite me. I think the tendency should be to get rid of E''
usage, because its definition of escape sequences is single-byte and ASCII
centric and thus overall a legacy construct. Certainly, we will want to keep
around E'' for a long time or forever, but it is a legitimate goal for
application writers to not use it, which is after all the reason behind this
whole standards-conforming strings project. I wouldn't want to have a
forward-looking feature such as the Unicode escapes be burdened with that kind
of legacy behavior.Also note that Unicode escapes are also available for identifiers, for which
there is no existing E"" that you can add it to.
Maybe I've just got my head deeply in the sand, but I don't understand
what the alternative to E'' supposedly is. How am I supposed to write
the equivalent of E'\t\n\f' without using E''? The
standard_conforming_strings syntax apparently supports no escapes of
any kind, which seems so hideously inconvenient that I can't even
imagine why someone wants that behavior.
...Robert
Robert Haas <robertmhaas@gmail.com> writes:
Maybe I've just got my head deeply in the sand, but I don't understand
what the alternative to E'' supposedly is. How am I supposed to write
the equivalent of E'\t\n\f' without using E''? The
standard_conforming_strings syntax apparently supports no escapes of
any kind, which seems so hideously inconvenient that I can't even
imagine why someone wants that behavior.
Well, quite aside from issues of compatibility with standards and other
databases, I'm sure there are lots of Windows users who are more
interested in being able to store a Windows pathname without doubling
their backslashes than they are in being able to type readable names
for ASCII control characters. After all, in most cases you can get
those characters into a string just by typing them (especially if you
aren't using readline or something like it).
BTW, does anyone know whether Unicode includes the ASCII control
characters ... ie, is \u0009 a name for tab? If so, maybe this
syntax is in part an attempt to cover that use-case in the standard.
regards, tom lane
On Tuesday 14 April 2009 20:35:21 Robert Haas wrote:
Maybe I've just got my head deeply in the sand, but I don't understand
what the alternative to E'' supposedly is. How am I supposed to write
the equivalent of E'\t\n\f' without using E''?
Well, the first alternative is to type those characters in literally. The
second alternative is the U&'' syntax. ;-) The third alternative is to design
applications that don't need this, because the processing behavior of those
characters is quite unportable. But of course in some cases using the E''
syntax is the most convenient.
On Apr 14, 2009, at 11:22 AM, Tom Lane wrote:
BTW, does anyone know whether Unicode includes the ASCII control
characters ... ie, is \u0009 a name for tab? If so, maybe this
syntax is in part an attempt to cover that use-case in the standard.
Yes, you can use, e.g., 	 in HTML to represent a tab character.
Best,
David
