Originally Posted by
lefen
Hay all,
...
So my question is, (1)what is it about the query that makes it resistant to SQL injection? and (2)could I use a similar query for the MYSQL db on my website, as an alternative to all the PHP I currently have in place to sanitise user input?
Many thanks :>
I thought folks might be ... interested ... in what makes SQLite resistant to
SQL injection, so, I went to the source ... The short answer, is, when properly used it is resistant to injection by design.
An extract of a chat between D. Richard Hipp (SQLite author) (drh below), and yours truly ...
Code:
steveo drh - one more quick Q?
steveo am I on firm ground telling folks that the tcl/C bindings to SQLite may be safer than the Python ones?
drh Depends on how tall you are looking to build, I suppose....
steveo re sqlinjection, in particular
drh The use of "?" prevents SQL injection attacks, but it is subject to counting errors (miscounting the number of ?s and binding the wrong values)
drh But if python uses %s then it is subject to SQL injection.
steveo TY!
drh On the other hand, so is TCL if you use [db eval "INSERT... $value"] instead of [db eval {INSERT ...$value}]
* steveo nods
drh The primary advantage of the TCL interface is that the variable name is embedded in the SQL statement so that you don't have count ?s and match them up with arguments.
* steveo would love to have this documented for public consumption
* drh suggests that steveo write it down - perhaps in the SQLite wiki
drh Which is safer...
drh A: db eval {INSERT INTO x VALUE(?,?,?,?,?)} $one $two $three $four
drh B: db eval {INSERT INTO x VALUES($one,$two,$three,$four)}
drh Now ask the question again assuming that the SQL text spans 20 lines and so an average of 10 lines of code separate each ? from its corresponding value?
* steveo would never have even thought of using A ...
steveo drh, TY again
* steveo must pay attention to the paywork
drh you're welcome
steveo - I couldnt get by w/out SQLite, FWIW
drh Glad you find it useful!
steveo extremely
So, the moral of this little story, is don't use %s bindings from Python.
I am basically clueless with regard to the question about MySQL...
Steve
(who always tries to have one eye focused on security...)
Bookmarks