QuoteSQL

[Ben]

Lawrence D'Oliveiro wrote:
> 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.

But cursor.execute does not necessarily do parameter-substitution. It
can send the data directly to the database with no escaping. In this
case, doing it yourself is a massive pessimization, and you're more
likely to get it wrong than the driver writers

Ben