[Tutor] [OT] Secure coding guidelines
zstumgoren at gmail.com
Tue Oct 13 17:49:18 CEST 2009
> In reference to this tip, my question is why?
> - don't use string formatting to create SQL statements - use the
> two-argument form of execute() to pass args as a sequence
SQL injection is the primary reason:
If you are going to "manually" hit a database by constructing your own
SQL calls, it's generally safer to pass in a tuple of parameters. The
below is an excerpt from the Python Docs for sqlite3:
Usually your SQL operations will need to use values from Python
variables. You shouldn’t assemble your query using Python’s string
operations because doing so is insecure; it makes your program
vulnerable to an SQL injection attack.
Instead, use the DB-API’s parameter substitution. Put ? as a
placeholder wherever you want to use a value, and then provide a tuple
of values as the second argument to the cursor’s execute() method.
(Other database modules may use a different placeholder, such as %s or
:1.) For example:
# Never do this -- insecure!
symbol = 'IBM'
c.execute("... where symbol = '%s'" % symbol)
# Do this instead
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)
# Larger example
for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
('2006-04-06', 'SELL', 'IBM', 500, 53.00),
c.execute('insert into stocks values (?,?,?,?,?)', t)
You're other option is to use some type of object relational mapper,
such as SQL Alchemy or Django's ORM, to hit the database. You use that
language's database-query language, which in turn constructs the SQL
calls for you in a (hopefully) safe manner.
More information about the Tutor