Searching for user input variable (string) in SQLite using python

Question

I am trying to search for a username in a table and subsequently find that users password and check it against input so far I have...

    def check():
            username = logEntry.get()
            password = passEntry.get()
            curs.execute("SELECT * FROM logins WHERE username = VALUES (?);", (username))
            userExists = curs.fetchone()
            if userExists:
                curs.execute("SELECT * FROM logins WHERE password = VALUES (?);",(password))
                passExists = curs.fetchone()
                if passExists:
                    controller.show_frame(look)
            else:
                errorLabel.place(x=0, y=0)

    logButton = tk.Button(self, text="Login", command=check)
    logButton.place(x=320, y=120)

    regButton = tk.Button(self, text="Registration For New Users",
                          command=lambda: controller.show_frame(Register))
    regButton.place(x=110, y=120)

Any help or suggestions would be much appreciated :)

Updated: I am now having trouble with an error saying that column username does not exist, here is what I have so far. @antti-haapala

     def check():
            username = logEntry.get()
            password = passEntry.get()
            cursor.execute("SELECT username, password" "FROM logins WHERE username = ?",(username))
            resultrow = cursor.fetchone()
            if resultrow is not None:
                db_username, db_password = resultrow
                if username == db_username and password == db_password:
                    controller.show_frame(look)

you know that anyone could open the local database and change it ? — Xavier Combelle
– Xavier Combelle, Commented Apr 12, 2015 at 8:26
How? I am really new to python and sql so help with such this is appreciated — LloydyPoydy
– LloydyPoydy, Commented Apr 12, 2015 at 14:28
That I want to say is that a sqlite3 database is simply a file which can be reade and write to anyone as access to your system. For example by writing another python program — Xavier Combelle
– Xavier Combelle, Commented Apr 13, 2015 at 14:52
The program will not be for commercial use so this is fine for me, thank you for the information — LloydyPoydy
– LloydyPoydy, Commented Apr 16, 2015 at 21:22

Antti Haapala · Accepted Answer · 2015-04-12 08:34:21Z

2

Your code is faulty. Even if this is really a Tkinter program, and the password checks do not really add to any security, the following should be noted for all readers might not be aware of the implications in your code:

This kind of program would first for the existence of any user with the given username, then it would check for the existence of any user with the given password, these need not be the same. If you'd use this kind of code in production, I could get into admin account by changing my unrelated user account password to a and logging in with admin:a.

Try something like this instead to ensure that the password that you check belongs to the very user you are checking:

results = cursor.execute("SELECT username, password "
                         "FROM logins WHERE username = ?", (username,))
resultrow = cursor.fetchone()
if resultrow is not None:
    db_user, db_password = resultrow
    if username == db_user and password == db_password:
        controller.show_frame(look)

And do note that storing plain-text passwords is frowned upon; it is preferable to encrypt these with one-way salted password hashing scheme; for example passlib (PyPI) is a good choice.

edited Apr 12, 2015 at 8:34

answered Apr 12, 2015 at 5:50

Antti Haapala

135k23 gold badges297 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Adam Smith Over a year ago

(and please hash your passwords)

LloydyPoydy Over a year ago

I am currently getting an error saying that the column username does not exist when it does I am confused at why this error has occured.

Antti Haapala Over a year ago

Perhaps you created an empty database by accident.

LloydyPoydy Over a year ago

I do not think I did, but can you have a look here is my database and table being created createDb = sqlite3.connect("database.db") cursor = createDb.cursor() try: cursor.execute('''CREATE TABLE logins (username TEXT UNIQUE, password TEXT)''') createDb.commit() except sqlite3.OperationalError: None @antti-haapala

Collectives™ on Stack Overflow

Searching for user input variable (string) in SQLite using python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related