[mod_python] String format in "LIKE" clause

Mike Looijmans nlv11281 at natlab.research.philips.com
Fri Jan 27 02:09:10 EST 2006


> BTW, if the substring you want to search for is coming from a web page or other
> user input this could be dangerous. For example, they could include a single quote
> in the substring for searching and stuff it all up. Thus, you need to perform
> appropriate escaping to avoid such problems.

I always take the approach that no matter how smart I am in escaping the 
user's gibberish, there will always be someone smarter than me who 
figures out how to break it. So what I do in cases like you describe:

paramlist = ('%' + company + '%',)
cursor.execute("SELECT * FROM company WHERE name LIKE %s", paramlist)

(note that there are no quotes...)

Passing user input as query parameters is, as far as I am concerned, the 
_only_ safe way of passing user input to the database engine. Even if 
you have a 'perfect' escaping function, one day they'll take your code 
and run it on an other DBMS on which the escape function is broken.

I typically instruct users to use the more obvious "*" and "?" 
wildcards, and replace them as needed (for simplicity, I omit escaping 
the % and _ characters in the user's input):

if ("*" in company) or ("?" in company):
	company = company.replace("*", "%").replace("?", "_")
else:
	company = company + "%"

--
Mike.



More information about the Mod_python mailing list