MySQLdb

Discussion in 'Python' started by Kill Joy, Nov 22, 2009.

  1. Kill Joy

    Kill Joy Guest

    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
     
    Kill Joy, Nov 22, 2009
    #1
    1. Advertising

  2. 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).
     
    Gerald Walker, Nov 22, 2009
    #2
    1. Advertising

  3. Kill Joy

    Kill Joy Guest

    On 22 Nov, 16:00, Gerald Walker <> 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.
     
    Kill Joy, Nov 22, 2009
    #3
  4. On Sun, 22 Nov 2009 06:36:09 -0800 (PST), Kill Joy
    <> 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
    HTTP://wlfraed.home.netcom.com/
     
    Dennis Lee Bieber, Nov 23, 2009
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alan Little

    can't import MySQldb on OS X

    Alan Little, Jun 25, 2003, in forum: Python
    Replies:
    0
    Views:
    856
    Alan Little
    Jun 25, 2003
  2. Skip Montanaro

    Re: Problem with MySQLdb on Mac OS X...

    Skip Montanaro, Jul 12, 2003, in forum: Python
    Replies:
    5
    Views:
    398
    Michael S. Jessop
    Jul 16, 2003
  3. Mike M
    Replies:
    1
    Views:
    346
  4. Dave Harrison

    MySQLdb and egenix mxDateTime dumps core

    Dave Harrison, Aug 5, 2003, in forum: Python
    Replies:
    0
    Views:
    348
    Dave Harrison
    Aug 5, 2003
  5. Dave Harrison

    using like and % in MySQLdb

    Dave Harrison, Aug 7, 2003, in forum: Python
    Replies:
    0
    Views:
    323
    Dave Harrison
    Aug 7, 2003
Loading...

Share This Page