QuoteSQL

[Lawrence D'Oliveiro]

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

> Lawrence D'Oliveiro <ldo at geek-central.gen.new_zealand> wrote:
> 
>> In message <Xns984B5837B9F7Aduncanbooth at 127.0.0.1>, Duncan Booth
>> wrote:
>> 
>>> Lawrence D'Oliveiro <ldo at geek-central.gen.new_zealand> wrote:
>>> 
>>>>     def EscapeSQLWild(Str) :
>>>>         """escapes MySQL pattern wildcards in Str."""
>>>>         Result = []
>>>>         for Ch in str(Str) :
>>>>             if Ch == "%" or Ch == "_" :
>>>>                 Result.append("\\")
>>>>             #end if
>>>>             Result.append(Ch)
>>>>         #end for
>>>>         return "".join(Result)
>>>>     #end EscapeSQLWild
>>> 
>>> That doesn't quite work. If you want to stop wildcards being
>>> interpreted as such in a string used as a parameter to a query, then
>>> you have to escape the escape character as well.
>> 
>> That's part of the separation of function. Note that the above
>> function does not generate a MySQL string literal: you must still put
>> it through the previously-defined SQLString routine, which will
>> automatically escape all the specials added by EscapeSQLWild.
>> 
> 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.

> You still have to escape the escape character...

Which will be done by cursor.execute if you use its parameter-substitution
mechanism.

> Calling the SQLString routine in this situation would be wrong because it
> would escape characters such as newline which must not be escaped.

SQLString will convert newlines into the \n sequence in the generated string
literal, which MySQL will interpret as a newline. cursor.execute's
parameter-substitution mechanism would do exactly the same thing.