QuoteSQL

[Lawrence D'Oliveiro]

In message <slrnehie29.pdu.sybrenUSE at schuimige.stuvel.eu>, Sybren Stuvel
wrote:

> Tim Chase enlightened us with:
>> >>> cur.execute("select * from people where last_name in (%s)" %
>> ','.join('%s' for i in xrange(len(t))),
>> t)
> 
> But since the value of 'i' isn't used at all, it can be abbreviated
> to:
> 
>>>> cur.execute("select * from people where last_name in (%s)" %
> ','.join('%s' for i in t),
> t)

Stuff this. Here are some new functions, separating the escaping of specials
from that of wildcards:

    def SQLString(Str) :
        """returns a MySQL string literal which evaluates to Str. Needed
        for those times when MySQLdb's automatic quoting isn't good
        enough."""
        Result = []
        for Ch in str(Str) :
            if Ch == "\0" :
                Ch = "\\0"
            elif Ch == "\010" :
                Ch = "\\b"
            elif Ch == "\011" :
                Ch = "\\t"
            elif Ch == "\012" :
                Ch = "\\n"
            elif Ch == "\015" :
                Ch = "\\r"
            elif Ch == "\032" :
                Ch = "\\z"
            elif Ch == "'" or Ch == "\"" or Ch == "\\" :
                Ch = "\\" + Ch
            #end if
            Result.append(Ch)
        #end for
        return "\"" + "".join(Result) + "\""
    #end SQLString

    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

So to turn the result from EscapeSQLWild into a string, you still need to
wrap it in SQLString.

And here is the corresponding replacement for my previous QuoteSQLList
routine:

    def SQLStringList(TheList) :
        """returns a MySQL list containing the items of TheList, suitable
        for use in an "in" clause."""
        return \
            "(" + ", ".join([SQLString(Str) for Str in TheList]) + ")"
    #end SQLStringList

So Tim Chase's example above now becomes:

    cur.execute("select * from people where last_name in %s" %
SQLStringList(t))

Much simpler, isn't it?

And there are other, more dynamic cases where explicit quoting using
SQLString is easier than trying to make do with autoquoting.