Just been reading this article <http://www.theregister.co.uk/2010/06/23/xxs_sql_injection_attacks_testing_remedy/> which says that a lot of security holes are arising these days because everybody is concentrating on unit testing of their own particular components, with less attention being devoted to overall integration testing.
Fair enough. But it’s disconcerting to see some of the advice being offered in the reader comments, like “force everyone to use stored procedures”, or “force everyone to use prepared/parametrized statements”, “never construct ad-hoc SQL queries” and the like. I construct ad-hoc queries all the time. It really isn’t that hard to do safely. All you have to do is read the documentation—for example, <http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html>—and then write a routine that takes arbitrary data and turns it into a valid string literal, like this <http://www.codecodex.com/wiki/Useful_MySQL_Routines#Quoting>. I’ve done this sort of thing for MySQL, for HTML and JavaScript (in both Python and JavaScript itself), and for Bash. It’s not hard to verify you’ve done it correctly. It lets you easily create table-updating code like the following, which makes it so easy to update the code to track changes in the database structure: sql.cursor.execute \ ( "update items set " + ", ".join ( tuple ( "%(name)s = %(value)s" % { "name" : field[0], "value" : SQLString(Params.getvalue ( "%s[%s]" % (field[1], urllib.quote(modify_id)) )) } for field in ( ("class_name", "modify_class"), ("make", "modify_make"), ("model", "modify_model"), ("details", "modify_details"), ("serial_nr", "modify_serial"), ("inventory_nr", "modify_invent"), ("when_purchased", "modify_when_purchased"), ... you get the idea ... ("location_name", "modify_location"), ("comment", "modify_comment"), ) ) + ( "last_modified = %d" % int(time.time()), ) ) + " where inventory_nr = %s" % SQLString(modify_id) ) -- http://mail.python.org/mailman/listinfo/python-list
