QuoteSQL

[Duncan Booth]

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. In a LIKE clause, backslash percent matches a 
percent character, but double backslash matches a single backslash and 
double backslash percent matches a backslash followed by anything.

I think this version should work, (or rewrite it as a 'for' loop if you 
prefer, though I think the replace version is clearer as well as being 
between 3 and 222 times faster on the inputs I tried):

def EscapeSQLWild(s):
   s = s.replace('\\', '\\\\')
   s = s.replace('%', '\\%')
   s = s.replace('_', '\\_')
   return s