MySQLdb

[Kill Joy]

Hi all.

I have a mod_python script with two query:

cursor = db.cursor()

sql = 'SELECT * FROM users where username=\'' + username +'\''
cursor.execute(sql)
result = cursor.fetchall()
num = int(cursor.rowcount)

if num == 0 :
sql2 = 'insert into users values (null, \'' + username + '\', \'' +
password +'\', \'no\',\'fdfdf\')'
cursor.execute(sql2)
warning = "Registration ok"
else :
warning = "EXIST!"

The first query is executed... but not the second. It doesn't insert.
Why?
I'm a newbie... sorry.

Many thanks.


K

 

[Gerald Walker]

Hi all.

I have a mod_python script with two query:

cursor = db.cursor()

sql = 'SELECT * FROM users where username=\'' + username +'\''
cursor.execute(sql)
result = cursor.fetchall()
num = int(cursor.rowcount)

if num == 0 :
sql2 = 'insert into users values (null, \'' + username + '\', \'' +
password +'\', \'no\',\'fdfdf\')'
cursor.execute(sql2) db.commit()
warning = "Registration ok"

else :
warning = "EXIST!"

The first query is executed... but not the second. It doesn't insert.
Why?
I'm a newbie... sorry.

Many thanks.

I added db.commit() after cursor.execute(sql2).

 

[Kill Joy]

                db.commit()





I added db.commit() after cursor.execute(sql2).

ohhh... many thanks many thanks.

Gius.

 

[Dennis Lee Bieber]

Hi all.

I have a mod_python script with two query:

cursor = db.cursor()

sql = 'SELECT * FROM users where username=\'' + username +'\''
cursor.execute(sql)

And in future... DON'T do that! Any data items should be passed to
the DB-API for proper handling...

sql = "select * from users where username = %s"
cursor.execute(sql, (username,)) #pass as a tuple, hence (,)

result = cursor.fetchall()
num = int(cursor.rowcount)

if num == 0 :
sql2 = 'insert into users values (null, \'' + username + '\', \'' +
password +'\', \'no\',\'fdfdf\')'
cursor.execute(sql2)

Same comment (heck, I'm going to have to bring up Python just to
decode that mess of mixed escaped quote marks... If you triple quote the
string, you don't have to use escapes internally! (You can even
multi-line the query)

sql2 = """insert into users
values (null, %s, %s, 'no', 'fdfd')"""
cursor.execute(sql2, (username, password))


ANY data value that could come from a user's input should be passed
using placeholders in the SQL, and a tuple of parameters in the
execute() call. That way the DB-API ensures the parameter is safe. After
all, presuming your new user types in the password (shown as an
assignment here)
"insert into users values (null, 'Who me', 'yes, why not', 'no',
fdfdf');drop table users;#', 'no','fdfdf')"
.... look at the resulting SQL... Perfectly valid insert statment --
followed by a table drop!