Can't retrieve hashed password from postgreSQL Database with Python

Question

So I'm writing a simple login script in python cgi. It wasn't working on the localhost, (showing 500 error) so running the code on its own revealed there were code issues. The code is as follows:

#!/usr/bin/python2.7
import cgi
import cgitb
import hashlib
import psycopg2
from dbconfig import *
cgitb.enable()
print "Content-Type: text/html"


def checkPass():
    email = "[email protected]"
    password = "blahblah"
    password = hashlib.sha224(password.encode()).hexdigest()
    result = cursor.execute('SELECT * FROM logins WHERE email=%s passhash=%s') % (email, password)
    print str(result)


if __name__ == "__main__":
    conn_string = "host=%s dbname=%s user=%s password=%s" % (host, database, dbuser, dbpassword)
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()
    checkPass()

The line that the program gets stuck on is the cursor.execute of the postgre query. The error that is shown is as follows:

Traceback (most recent call last):
  File "login.py", line 28, in &lt;module&gt;
    checkPass()
  File "login.py", line 20, in checkPass
    result = cursor.execute('SELECT * FROM logins WHERE email=%s passhash=%s') % (email, password)
ProgrammingError: syntax error at or near "passhash"
LINE 1: SELECT * FROM logins WHERE email=%s passhash=%s

And it should be noted that it points to passhash. I tried entering the query directly into the db in psql console as:

SELECT * FROM logins WHERE passhash=storedhashedcode;

However, this returns an error about a column of the name of the hash (i.e e342hefheh43hfhfhefherf....etc) not existing. What am I doing wrong here? The only thing I can think of is hashes being stored differently.

Note - here's the code I used to store the password etc, if that helps:

email = "[email protected]"
allpass = "blahblah"
password = hashlib.sha224(allpass.encode()).hexdigest()
cursor.execute('INSERT INTO logins (email, passhash) VALUES (%s, %s);', (email, password)) 
conn.commit()

Any help would be greatly appreciated!

UPDATE:

As suggested by Decly, I've changed the query to:

result = cursor.execute('SELECT * FROM logins WHERE email=%s AND passhash=%s') % (email, password)

This now produces the error:

ProgrammingError: column "s" does not exist
LINE 1: SELECT * FROM logins WHERE email=%s AND passhash=%s

So it's obviously using s as a column for some reason. Why is it not taking in my variable names?

Decly · Accepted Answer · 2017-04-18 10:11:12Z

1

In:

'SELECT * FROM logins WHERE email=%s passhash=%s'

You are missing a AND in the boolean expression:

'SELECT * FROM logins WHERE email=%s AND passhash=%s'

When you write the query in psql you are missing the quotes for the string literal, so postgresql is inferring a column name, you should write:

SELECT * FROM logins WHERE passhash='storedhashedcode';

You are also putting the parentesis in the wrong place, the python sentence should be:

result = cursor.execute('SELECT * FROM logins WHERE email= %s AND passhash= %s', (email, password))

edited Apr 18, 2017 at 10:11

answered Apr 18, 2017 at 1:20

Decly

3793 silver badges12 bronze badges

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

5 Comments

Generic Snake Over a year ago

Ok, I've added the AND as suggested, but now it throws the same error as before, except in reference to the %s

Generic Snake Over a year ago

sorry, not quite the same error, now it is taking s in as a string it seems, and not putting in the variables.

Generic Snake Over a year ago

Got it. Apparently you don't set queries up in the manner I had. Now there's another issue, but that's a whole other kettle of fish.

Decly Over a year ago

I have extended my answer, your prepared statement have an error.

Generic Snake Over a year ago

It was that and the fact that cursor.execute doesn't actually return anything. Need to use fetchone for that.

Collectives™ on Stack Overflow

Can't retrieve hashed password from postgreSQL Database with Python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related