CODEWITHSUNDEEP
pythonsqlpython-3.xsqlite
CodeSpent
CodeSpent

Reputation: 1904

SQL Query returning "None" instead of row

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:

enter image description here

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?

Upvotes: 1

Views: 3059

Answers (2)

Larry Lustig
Larry Lustig

Reputation: 50970

  1. You're search expression is evaluating to (ph104694) which clearly doesn't exist in the data you showed.

  2. 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).

  3. 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])

Upvotes: 3

Batman
Batman

Reputation: 8917

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})

Upvotes: 3

Related Questions