Is it possible, with the Python Standard Library (say version 2.5) to perform MS-SQL queries which are parameterized?

Question

While the particular data I'm working with right now will not be user-generated, and will be sanitized within an inch of its life during my usual validation routines, I would like to learn how to do your basic INSERT, SELECT, etc. SQL queries while protecting myself against SQL injection attacks, just for future reference. I'd rather learn how to do things the "right" way, through parameterized queries.

Sanitization is always nice, but I am pitting my pitiful intellect against that of seasoned hackers. Manually escaping means I am probably overlooking things, since blacklists are not as robust as whitelists. For additional clarification, I do not mean using the (%s) notation to pass as a parameter for building a string possibly named sqlstatement. I think one of the magic words I need to know is "binding."

I am also hoping to avoid anything outside of the Python Standard Library.

The application in question requires Microsoft SQL 2005, if that is relevant. I am using ActiveState Python and the modules dbi and odbc. Since this is Someone Else's Database, stored procedures are out.

Answer 1

PEP 249 (DB API 2.0) defines 5 paramstyles, PyMSSQL uses paramstyle == pyformat. But although it looks like string interpolation, it is actually binding.

Note difference between binding:

cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')

and interpolating (this is how it should NOT be done):

cur.execute('SELECT * FROM persons WHERE salesrep=%s' % 'John Doe')

See also http://wiki.python.org/moin/DbApiFaq

---

"I am also hoping to avoid anything outside of the Python Standard Library."

You're out of luck here. The only RDBMS driver that comes built-in in Python is SQLite.

Answer 2

Try pyodbc

But if you want to have things really easy (plus tons of powerful features), take a look at sqlalchemy (which by the way uses pyodbc as the default "driver" for mssql)