Re: Wierd "ProgrammingError" with "E" string prefix

On Wed, Mar 04, 2009 at 11:18:08AM +0100, Karsten Hilbert wrote:
> > Something like:
> > 
> > curs.execute("DROP TABLE %s", (Identifier(unstrusted_data),))
> 
> Should this, then, translate to
> 
> 	drop table quote_ident(untrusted_data);
> 
> or am I on the wrong track ?
If untrustedData were something like ");wreak_havoc;--", this would,
I think, not help you -- at least I reached that conclusion when I
last thought about it.

No, I think you need to parse untrusted_data the way postgres parses
their identifiers.  Which is suprisingly involved.

In my ADQL parser, I have this pyparsing snippet:

	regularIdentifier = Word(alphas, alphanums+"_").addParseAction(
		_failOnReservedWord)
	delimitedIdentifier = QuotedString(quoteChar='"', escQuote='"',
		unquoteResults=False)
	identifier = regularIdentifier | delimitedIdentifier

I'm not sure that failing on reserved words actually is necessary
when quoting values; it might be a nice challenge to see if one could
craft an attack based on selling reserved words as identifiers.  My
feeling is that producing anything else than syntax errors will be
hard.

However, for Postgres in general, this snippet is wrong. Postges 
docs 4.1.1 say:

"""
SQL identifiers and key words must begin with a letter (a-z, but also
letters with diacritical marks and non-Latin letters) or an
underscore (_). Subsequent characters in an identifier or key word
can be letters, underscores, digits (0-9), or dollar signs ($).
[...]
Quoted identifiers can contain any character other than a double
quote itself. (To include a double quote, write two double quotes.)
"""

So, one issue is that Postgres (and apparently SQL) allows all kinds
of weird characters in identifiers, which makes us depend on the
input encoding and possibly on the locale Postgres is running in.

Also, simply deciding that input strings containing blanks should
become quoted identifiers and ones that do not should not be quoted
is tricky, since quoted identifiers are case sensitive.

Postgres' quote_ident seems to do something like this.  It uses (at
least in the 8.2.4 source I've unpacked here)
./backend/utils/adt/ruleutils.c:quote_identifier.  I guess we should
replicate the behaviour given there, even if that's going to yield
suprises (like foObar==foobar, but foO bar!=foo bar).

Cheers,

             Markus