Problem With Insert with MySQLdb

[Magnus Lycka]

Dennis Lee Bieber wrote:
> On Sun, 30 Oct 2005 18:44:32 -0500, "David Mitchell" <dave at dbmdata.com>
> declaimed the following in comp.lang.python:
>>	sql = "INSERT INTO category (category_name) VALUES ('" +
>>req.form['category'] + "')"
>>	cursor.execute(sql)
> 
> 	Don't do that!
> 
> 	Use the execute() method to do parameter substitution...

This advice is really extremely important from a security
point of view if this is a web app. Pasting in data from
a web form into a SQL command like this is really asking
for trouble.

I this case, someone could for instance craft a http request
so that req.form['category'] contains:

"');DELETE FROM category;INSERT INTO  category (category_name)
VALUES ('SUCKER!!!"

This SQL injection attack won't work if the SQL statement and
parameters are sent separately to the database server. (You
can't be sure that this is actually what happens just because
the Python DB-API looks like that though. Please verify that
your database driver is sane.)

Besides security, there are also performance issues here.
I'm not sure about MySQL, but most RDBMSs are much better if
it gets the same query many times, with different parameters
on each call, than if it gets many different queries, which
is what happens if you manually paste the parameter values
into the SQL string.

It's also a good idea to try to understand how transactions
work in SQL, and exactly when to do commit. In many cases,
using autocommit might lead to logically corrupt databases.

Some info can be found at: http://www.thinkware.se/epc2004db/