PHP has a very handy function addslashes which escapes, by prefixing backslashes, characters that can cause undesired effects in SQL queries (read SQL injection). Coding a login page in Python had me hard pressed to find something similar in Python to escape characters retrieved from login forms. I won’t say I searched across the seven seas, but as much as I looked, I discovered the “escape” function from the “re” module. I cannot find a reference online (I need to search better and more thoroughly), but re.escape in effect escapes all non-alphanumeric characters in string given to it as argument.
The __doc__ string for re.escape says:
escape(pattern)
Escape all non-alphanumeric characters in pattern.
Posted in: General
Fawad Halim
24 April, 2007
I think it’s not there because (IMHO) it’s the wrong way of solving that problem. Backslashes might not be portable across databases, and it handles the datatype stuff in the wrong app layer. The correct way to do it is to use something like
.execute(“SELECT * FROM accounts WHERE id = %(id)s”, {‘id’ = ‘fawad’)
The database driver is supposed to take care of escaping and quoting the arguments. A side benefit is that the driver is free to take the string query and reuse the execution plan for later execution with different values of parameters.
Check out http://www.python.org/dev/peps/pep-0249/. Only downside here is that the parameter style (like %(id)s) is not enforced across drivers. You have to check driver.paramstyle and set the query accordingly.
Another approach is to use a higher level API like sqlalchemy which should handle everything I described above for you transparently.
Faried Nawaz
24 April, 2007
A consistent way to do it is to use %s everywhere, like:
cursor.execute(“select whatever from foo where id = %s and barf = %s”, (someId, someBarf))
or as in Fawad’s example. I learned the hard way that you shouldn’t use anything but %s, even for integers.
ayaz
25 April, 2007
I didn’t know SQLAlchemy sanitised parameters adequately before putting them into queries. I was doing something to the effect of:
s = self.teams_table.select(sqlalchemy.and_(self.teams_table.c.user_name==re.escape(us3r),
self.teams_table.c.passwd==md5.new(p4ss).hexdigest()))
The subsequent discussion on the addslashes page identifies that it is much safer to use DBD-specific string escape functions, for instance, mysql_real_escape_string().
ayaz
25 April, 2007
However, I still cannot see how use of %s in queries to pass around parameters will help neuter special and potentially offending characters. I might be missing something.
Faried Nawaz
1 May, 2007
The magic is not in the %s, but in the db driver.
Hello
29 July, 2011
Yeah, it is in the driver. Whichever one that is. Because I use %s everywhere and I found this page to solve the problem that arises in simply using %s.
If escaping it works, then that is what I will do.