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.
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--').