Python MySQL module: cursor not working

Question

I am working on building a textbook auction application using the Python MySQL module to connect to the database. The login code isn't working. When prompted, I type in a username/password that I know is in the DB, but it keeps saying that the username/password is invalid. Am I using the cursor object incorrectly?

#import the datetime package to use dates
import datetime
#import the mysql connector to access the database
import mysql.connector
#establish a connection to the database
cnx = mysql.connector.connect(user='uXXXXXX', password='pXXXXXX',
host='COMPDBSXXX', database='schemaXXXXXX')
#retrieve a cursor to execute queries
cursor = cnx.cursor()

logged_in = False

########----USER INTERFACE STARTS HERE----###################
print("Welcome to eGCC! Please select an action.")
print("\r")
print("1. LOGIN")

###---LOGIN----#################
while logged_in == False:
    print("Enter eGCC username: ")
    username = input()
    print("Enter eGCC password: ")
    password = input()
    query = ("SELECT Username from egccuser")
    cursor.execute(query)
    for ID in cursor:
        if ID == str(username):
            userID = username
            query = ("SELECT password from egccuser where Username = %s")
            qdata = str(userID)
            cursor.execute(query,qdata) 
            if cursor == password:
                logged_in = True               
    if logged_in == False:
            print("Error: Invalid Username/Password")

#close cursor
cursor.close()
#close the connection to the DBMS
cnx.close()

do you mean that i should put this in place of the cursor.execute in the for loop? if so, that didn't change anything. am i looping through the cursor correctly? — jordpw, Dec 01 '14 at 15:16
qdata should be a tuple or dict, not a string. http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html — Binux, Dec 01 '14 at 15:26

score 0 · Answer 1 · edited May 23 '17 at 12:05

Pro Tip: Don't publish credentials online

Iterating over a cursor will return a row (Which is a tuple) and not the ID. From the source under BaseCursor

 def __iter__(self):
    return iter(self.fetchone, None) 

 def fetchone(self):
    """Fetches a single row from the cursor. None indicates that
    no more rows are available."""
    self._check_executed()
    if self.rownumber >= len(self._rows): return None
    result = self._rows[self.rownumber]
    self.rownumber = self.rownumber+1
    return result

As such if ID == str(username): will never be true.

I also have a feeling if cursor == password: will never be true either. Since cursor is not a string.

Haven't tested this yet (expect afternoon) but my recommendation would be to set some debug points and see whether you ever enter one of the blocks.

Side note. Why iterate when you can simply run ("SELECT Username from egccuser where Username = %s and password = %s").

Side Side note: For the sake of your security as well- storing passwords in plain text is never a good practice. Hashes with salt can be broken, but are better than nothing. Read more here

Python MySQL module: cursor not working

1 Answers1