#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2005
    Location
    WA
    Posts
    29
    Rep Power
    0

    Check input against database issue MySQLdb


    As the username is submitted to the python form, I want to have it check against the database to see if the username exists before actually creating it. In order to avoid duplicate users.

    Here's my code.
    Code:
    #!/usr/bin/python
    import cgi,os,MySQLdb,md5,sys
    
    print "Content-type: text/html\n\n"
    form = cgi.FieldStorage()
    a = form["username"].value
    c = form["password"].value
    conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "********", db = "test")
    cursor = conn.cursor()
    if cursor.execute("select * from logincreds where username='a'") == 0L:
            try:
                    conn = MySQLdb.connect (host = "localhost", user = "root", passwd = "********", db = "test")
                    cursor = conn.cursor()
                    add = "cursor.execute(\"INSERT into logincreds (username, password) VALUES ('" + a + "', '" + c + "')\")"
                    print add
                    exec(add)
            except MySQLdb.Error, e:
                    print "Error %d: %s" % (e.args[0], e.args[1])
                    sys.exit (1)
    else:
            print "blah"
    if cursor.execute("select * from logincreds where username='a'") >= 1:
            print "Username is Already in use."
    I've tried changing the 1L and 0L to 1 and 0, but nothing seems to work....

    Does anyone have any experience with this issue, or happen to see an issue?

    Thanks,
    Poz
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    174
    Rep Power
    11
    maybe dump the result of the select statement into a variable, then for loop through it until you find the username. Not sure that tuple return from Mysqldb is allowing for the equality comparison with multiple results. Can anyone confirm this?

    also why are you getting two cursors on the same database? you can put the first one into a try/except block then work with the cursor as is.

    try that and let us know what happens. If nothing I'm saying makes any sense let me know!

    cheers
    sf2k
    Last edited by sf2k; September 28th, 2005 at 06:09 AM.
  4. #3
  5. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    Not sure about MySQLdb myself, but unless you need the values, it's much faster to just ask the db for a count of matching values.

    e.g.:

    SQL Code:
     
    SELECT 
     COUNT(*)
    FROM
     logincreds
    WHERE
     username = "a";


    If you get 1 then you've got a matching username. if you get 0, then you don't. If you get more than 1, then you have a serious problem.

    This is much faster since you don't have to queue all the results for retrieval which a SELECT * does - at least just specify one row -SELECT fieldname FROM..- if you do it this way.

    Edit: you definitely do not want to be querying the database twice for the same result set.

    --Simon
    Last edited by SimonGreenhill; September 28th, 2005 at 06:17 AM.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2005
    Location
    WA
    Posts
    29
    Rep Power
    0
    The problem I'm having is that regardless of whether or not the username exists, the tuple returned is ALWAYS 1L.

    This is even in the python interpeter....

    Code:
    mysql> select * from logincreds;
    +----------+----------+
    | username | password |
    +----------+----------+
    | username | password |
    | amandah  | password |
    +----------+----------+
    2 rows in set (0.00 sec)
    
    mysql>
    Code:
    pozican@ricotta:~$ python
    Python 2.4 (#1, Jan  1 2005, 21:33:55)
    [GCC 3.3.4] on linux2
    Type "help", "copyright", "credits" or "license" for more information.
    >>> import cgi,os,MySQLdb,md5,sys
    >>> conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "********", db = "test")
    >>> cursor = conn.cursor()
    >>> cursor.execute("select count(*) from logincreds where username='username'")
    1L
    >>> cursor.execute("select count(*) from logincreds where username='DOESnotEXIST'")
    1L
    >>>
    What's up with that?
  8. #5
  9. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    Originally Posted by Pozican
    What's up with that?
    I guess the cursor returns a result set ( i.e. ONE result set ), but you need to get the results using cursor.fetchall()

    e.g.
  10. #6
  11. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    python Code:
     
    #!/usr/bin/python
    import cgi,os,MySQLdb,md5,sys
     
    print "Content-type: text/html\n\n"
    form = cgi.FieldStorage()
     
    # you need to escape any dodgy chars in anything you send 
    # to the database - not escaping things like ' and " will make it
    # very easy for people to hack your database via SQL injection.
     
    user = form["username"].value
    user = MySQLdb.escape_string( user )
     
    # you don't want to store the password
    # as plain text - just md5 or sha1 hash it
    pass = form["password"].value
    pass = md5.md5( pass )
    pass = pass.hexdigest()
     
    # Only connect once - multiple connects represent
    # a hefty overhead. Re-use your cursor.
    conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "********", db = "test")
     
    cursor = conn.cursor()
     
    query = "SELECT 
                    COUNT(*) 
                FROM logincreds 
                WHERE username='%s'" % ( user )
     
    cursor.execute( query )
     
    numentries = cursor.fetchall()   
     
    if numentries[0][0] == 0:
       try:
          query = 'INSERT INTO logincreds 
                         (username, password) 
                         VALUES 
                         ( "%s", "%s" )' % ( user, pass )
     
          cursor.execute( query )
     
       except MySQLdb.Error, e:
          print "Error %d: %s" % (e.args[0], e.args[1])
          sys.exit (1)
    else:
       print "Username is Already in use!"


    --Simon

    Comments on this post

    • sf2k agrees : nice example
    Last edited by SimonGreenhill; September 28th, 2005 at 06:46 PM.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2005
    Location
    WA
    Posts
    29
    Rep Power
    0
    This is extremely frustrating -- Why do I always get '1L' returned whether I use SELECT * or COUNT(*) regardless of whether the username exists or not.

    Code:
    >>> conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "abcd", db = "test")
    >>> cursor = conn.cursor()
    >>> query = "SELECT COUNT(*) FROM logincreds WHERE username='%s'" % ( user )
    >>> cursor.execute(query)
    1L
    >>> user = "username"
    >>> cursor.execute(query)
    1L
    >>> user = "NotExist"
    >>> cursor.execute(query)
    1L
    >>>
    Is there something I'm missing? Or maybe another way of doing it?
  14. #8
  15. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    Originally Posted by me, a few posts ago
    I guess the cursor returns a result set ( i.e. ONE result set ), but you need to get the results using cursor.fetchall()
    Code:
    >>> user = "username"
    >>> cursor.execute(query)
    >>> cursor.fetchall()

IMN logo majestic logo threadwatch logo seochat tools logo