Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   Python (http://www.velocityreviews.com/forums/f43-python.html)
-   -   MySQLdb (http://www.velocityreviews.com/forums/t706185-mysqldb.html)

Kill Joy 11-22-2009 02:36 PM

MySQLdb
 
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 11-22-2009 03:00 PM

Re: MySQLdb
 
Kill Joy wrote:
> 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 11-22-2009 03:03 PM

Re: MySQLdb
 
On 22 Nov, 16:00, Gerald Walker <geraldwalk...@gmail.com> wrote:
> Kill Joy wrote:
> > 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).


ohhh... many thanks many thanks.

Gius.

Dennis Lee Bieber 11-23-2009 12:21 AM

Re: MySQLdb
 
On Sun, 22 Nov 2009 06:36:09 -0800 (PST), Kill Joy
<g.ricioppo@gmail.com> declaimed the following in
gmane.comp.python.general:

> 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)

>>> password = "yes, why not', 'no', fdfdf');drop table users;#"
>>> sql2 = 'insert into users values (null, \'' + username + '\', \'' + password +'\', \'no\',\'fdfdf\')'
>>> sql2

"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!
--
Wulfraed Dennis Lee Bieber KD6MOG
wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/



All times are GMT. The time now is 02:18 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.