SQL Query returning "None" instead of row

Question

I'm working on a light login, and have a tabled titled Users. I'm trying to take my login form POST body and verify it across the database.

Values from form:

user = request.form['username']
password = request.form['password']

SQL Statement:

conn = sqlite3.connect(db)
cur = conn.cursor()
cur.execute("SELECT * FROM Users WHERE Username LIKE '(%s)'" % user)
row = cur.fetchone()

Users Table:

So on a POST request from my form, here is what is printed:

Print(user, password) =  ph104694 Password123
Print(row)  = None

So you can see the row is being returned as None when the data absolutely exists. If I change user to something I know is incorrect, I'm getting the same results, but if I change the table from Users to something like Users2 I'm met with a no table exists error which is fine. So despite matching data existing something about my statement isn't allowing it to produce that row. Any ideas?

Larry Lustig · Accepted Answer · 2018-11-26 01:49:21Z

3

You're search expression is evaluating to (ph104694) which clearly doesn't exist in the data you showed.
There is no reason to use the LIKE operator here and it probably runs counter to what you want to do (match the single record exactly matching the user ID that was entered).
This is the classic example of code that is subject to an SQL injection attack. You should never, never, ever use string interpolation to build an SQL string like this. Instead, use parameter substitution.

Taken all together, you want something like this:

 cur.execute("SELECT * FROM Users WHERE Username = ?", [user])

answered Nov 26, 2018 at 1:49

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

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

1 Comment

CodeSpent Over a year ago

Thanks for the tips, so I've found out. Either way none of my SQL will ever be used anyways, so just needed to get over the hump. ;) I'll accept this answer if the gentleman who helped me solve the issue below doesn't update their answer to reflect the issue/solution.

Batman · Accepted Answer · 2018-11-26 01:41:39Z

3

Your query string evaluates to "SELECT * FROM Users WHERE Username LIKE '(ph104694)'".

Note the parentheses which aren't in the actual username.

Also, you almost certainly don't want to use LIKE.

What you want is "SELECT * FROM Users WHERE Username = 'ph104694'"

Which would create with "SELECT * FROM Users WHERE Username = '{user}'".format(user=user)

Also, you can (and should) parameterize this as

cur.execute("SELECT * FROM Users WHERE Username = :user", {user: user})

answered Nov 26, 2018 at 1:41

Batman

9,0177 gold badges48 silver badges87 bronze badges

Collectives™ on Stack Overflow

SQL Query returning "None" instead of row

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related