Sanitizing SQLite Input

Question

First of all Im pretty new to databases and i started using Python yesterday.

I started playing around with the sqlite3 module ( I used some Sqlite prior to this via DBI in Perl)

I stumbled across the following example on the official Python Sqlite Documentation here

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.fetchone()

Why is the first example insecure and the second not?

Lets just say i have some application that stores documents and the user can search the database by the document name.

For this to work i would need an input from the user and then create a query with the keyword/s

I dont get why a tuple should now be more "secure" then a string i mean in both cases the user could input something like "xyz OR 1=1" to display every record.

I hope someone is kind enough to explain this to me. I know this is probably very obvious to someone with experience.

Answer 1

Parameters are not the same as simple string substitution; they give their values directly to the database without further interpretation:

>>> import sqlite3
>>> db=sqlite3.connect(":memory:")
>>> db.execute("CREATE TABLE t(x)")                 
>>> db.execute("INSERT INTO t VALUES('x'),('secret')")
>>> db.execute("SELECT * FROM t WHERE x = '%s'" % ("x' OR 1=1--",)).fetchall()
[(u'x',), (u'secret',)]
>>> db.execute("SELECT * FROM t WHERE x = ?",     ("x' OR 1=1--",)).fetchall()
[]

With a parameter, you get the same effect as if the value would have been quoted correctly for all special characters (in this case, WHERE x = 'x'' OR 1=1--').