Escaping SQL in python

[Glen Starchman]

Daniel Dittmar wrote:
> 
> > Is there a method to escape ' " \ and the like in python auto-magiacaly
> > ?
> 
> It is probably best to use triple quoted strings. This allows single quotes
> of either variety and SQL statements tend to spread over multiple lines
> anyway.

You *could* use the poor man's escape routing... urllib.quote_plus.
There are some obvious problems with this (such as adding characters to
the resulting string), it works fine so long as you remember to use
urllib.unquote_plus on the string when you return it from the database.
Or, you could roll your own with something like the following:


"""methods for making strings SQL safe, and back again,
also used for URL encoding"""

import string

#add anything you like to the mappings dict
mappings = {"'":"''",
           '"':'""',
           ' ':'+'
           }

def escape(*args):
    arg_lst = []
    if len(args)==1:
        return escape_single(args[0])
    for x in args:
        arg_lst.append(escape_single(x))
    return tuple(arg_lst)

def escape_single(x):
    if type(x)==type(()) or type(x)==type([]):
        return escape(x)
    if type(x)==type(""):
        tmpstr=''
        for c in range(len(x)):
            if x[c] in mappings.keys():
                if x[c] in ("'", '"'):
                    if c+1<len(x):
                        if x[c+1]!=x[c]:
                            tmpstr+=mappings[x[c]]
                    else:
                        tmpstr+=mappings[x[c]]
                else:
                   tmpstr+=mappings[x[c]]
            else:
                tmpstr+=x[c]
    else:
        tmpstr=x
    return tmpstr
def unescape(val):
    if type(val)==type(""):
        tmpstr=''
        for key,item in mappings.items():
            val=string.replace(val,item,key)
        tmpstr = val
    else:
        tmpstr=val
    return tmpstr

def unescape_list(*args):
    arg_lst = []
    for x in args:
        arg_lst.append(unescape(x))
    return tuple(arg_lst)

if __name__=="__main__":
    x= escape(escape("hello'"))
    print x
    print unescape(x)