QuoteSQL

[Lawrence D'Oliveiro]

In message <Xns984B793317A15duncanbooth at 127.0.0.1>, Duncan Booth wrote:

> Lawrence D'Oliveiro <ldo at geek-central.gen.new_zealand> wrote:
> 
>>> You are still missing the point. I'm not talking about generating a
>>> MySQL string literal, I'm talking about preventing wildcards
>>> characters having their special meaning when using the string as a
>>> parameter in cursor.execute.
>> 
>> But that's what cursor.execute will do if you use its
>> parameter-substitution mechanism--generate a string literal.
> 
> The current implementation of the MySQL database adapter will do that.
> Other database adaptors may handle parameters without generating string
> literals.

Doesn't matter what other implementations of parametrization might or might
not do. The syntax I generate is valid for MySQL, therefore it will work
with the MySQL database adapter regardless of what else the adaptor might
do.

>>> You still have to escape the escape character...
>> 
>> Which will be done by cursor.execute if you use its
>> parameter-substitution mechanism.
> 
> Too late and not enough. Too late, because if you want to search for the
> literal "\\%" (single backslash percent) you need to escape the backslash
> before you escape the percent. Not enough because at the point MySQLdb
> finally converts it to a string literal a literal backslash to be used in
> a context where wildcards are allowed needs to be spelled with 4
> backslashes. i.e. it needs to be escaped twice, once for the string
> literal and once to stop it being interpreted as an escape within the
> wildcard string.

I'm assuming you mean, how would you get from a Python expression to a MySQL
clause that looks like

    name like "%\\\\%%"

(wildcard % followed by literal backslash \\ followed by literal percent \%
followed by wildcard %.) That's easy:

    EscapeSQLWild(r"\%") => r"\\%"
    SQLString(r"\\%") => r'"\\\\%"'

So the Python expression

    "name like %s" % SQLString("%" + EscapeSQLWild(r"\%") + "%")

gives you what you want.

> Correct: they both do the same thing. So you have to use either SQLString
> or the parameter substitution. You cannot use both. Calling SQLString on a
> string to be passed in to the parameter substitution mechanism will not
> work correctly.

I thought I had made that clear already.