By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,171 Members | 809 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,171 IT Pros & Developers. It's quick & easy.

Problem With Insert with MySQLdb

P: n/a
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

Oct 30 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Sun, 30 Oct 2005 18:44:32 -0500, "David Mitchell" <da**@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...

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

Then see what "res" contains.

Am I doing something obviously incorrect here?
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.

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

Oct 31 '05 #2

P: n/a
David Mitchell wrote:
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


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
Oct 31 '05 #3

P: n/a
Dennis Lee Bieber wrote:
On Sun, 30 Oct 2005 18:44:32 -0500, "David Mitchell" <da**@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/
Nov 1 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.