Problem With Insert with MySQLdb

Hello,

I am a complete beginner with Python. I've managed to get mod_python up and
running with Apache2 and I'm trying to a simple insert into a table in a
MySQL database.

I'm using the MySQLdb library for connectivity. I can read from the database
no problem, but when I do an insert, the value never gets added to the
database, even though there is no error, and the SQL is fine (I print out
the SQL statement in the function). When I copy and paste the sql from my
browser and insert directly into MySQL, it works fine.

Here is the function in question:

def add(req):
db = MySQLdb.connect(host="intranet", user="root", passwd="",
db="intranet")
# create a cursor
cursor = db.cursor()
# execute SQL statement

sql = "INSERT INTO category (category_name) VALUES ('" +
req.form['category'] + "')"
cursor.execute(sql)
return sql


The SQL it is outputting is:

INSERT INTO category (category_name) VALUES ('Test')

Am I doing something obviously incorrect here?

Thanks,

Dave

re: Problem With Insert with MySQLdb

On Sun, 30 Oct 2005 18:44:32 -0500, "David Mitchell" <dave@dbmdata.com>
declaimed the following in comp.lang.python:
[color=blue]
>
> sql = "INSERT INTO category (category_name) VALUES ('" +
> req.form['category'] + "')"
> cursor.execute(sql)[/color]

Don't do that!

Use the execute() method to do parameter substitution...

sql = "insert into category (category_name) values (%s)"
res = cursor.execute(sql, (req.form["category"],) )

Then see what "res" contains.
[color=blue]
>
> Am I doing something obviously incorrect here?
>[/color]
The other possibility might be that the Apache/mod_python session is
running as a host/user that may not have update privileges on the MySQL
server.

--[color=blue]
> ================================================== ============ <
> wlfraed@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> ================================================== ============ <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.netcom.com/> <[/color]

re: Problem With Insert with MySQLdb

David Mitchell wrote:[color=blue]
> Hello,
>
> I am a complete beginner with Python. I've managed to get mod_python up and
> running with Apache2 and I'm trying to a simple insert into a table in a
> MySQL database.
>
> I'm using the MySQLdb library for connectivity. I can read from the database
> no problem, but when I do an insert, the value never gets added to the
> database, even though there is no error, and the SQL is fine (I print out
> the SQL statement in the function). When I copy and paste the sql from my
> browser and insert directly into MySQL, it works fine.
>
> Here is the function in question:
>
> def add(req):
> db = MySQLdb.connect(host="intranet", user="root", passwd="",
> db="intranet")
> # create a cursor
> cursor = db.cursor()
> # execute SQL statement
>
> sql = "INSERT INTO category (category_name) VALUES ('" +
> req.form['category'] + "')"
> cursor.execute(sql)
> return sql
>
>
> The SQL it is outputting is:
>
> INSERT INTO category (category_name) VALUES ('Test')
>
> Am I doing something obviously incorrect here?
>
> Thanks,
>
> Dave
>[/color]

Try either executing this first:
cursor.execute("set autocommit = 1")
or this afterwards:
cursor.execute("commit")

The idea is that transactions are not committed into the database
until you "commit" them - and if you hit problems halfway through
a sequence of updates that really shouldn't be left half
finished, you can execute "abort" instead (or just close the
connection), and none of them will be done.

Steve

re: Problem With Insert with MySQLdb

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

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/