python sqlite3 what's wrong

[oliverjrose99]

when i run the function login it wont get the password related to the username and so just loops (i have all the functions in one file and other code in another). whats wrong with it, thanks

def login(c, db, logged_in):    username = input("Enter your username: ")    c.execute("""SELECT password FROM users WHERE username=username""")    correct_pass = c.fetchall()    password = input("Enter your password: ")    if password != correct_pass:        print("Username and/or Password are not valid")        login(c, db, logged_in)    else:        logged_in = True        print("Login Successful")

 

[MikeD]

 

    c.execute("""SELECT password FROM users WHERE username=username""")

 

In that line you are asking the database to give you the password for user "username".

You have to concatenate the querry string with the actual input, so something like

c.execute("""SELECT password FROM users WHERE username=""" + username)

[oliverjrose99]

thanks but i have another problem. sqlite3 returns "[('password',)]", how can i turn it into "password"

[MikeD]

When you do c.fetchall() sql gives you a list with all the rows that match the condidtion specified. Each row has a tuple with the columns requested.

Since there is supposed to be only one user with a given username (although, from your previous post a while back it doesn't seem to be enforced) you can use c.fetchone() which will return only the first row. This is equivalent to

res = c.fetchall()res = res[0]

Then, since you only asked for the column with the password, you can do res[0] to access the password.

[AnarchyOnline]

 

In that line you are asking the database to give you the password for user "username".

You have to concatenate the querry string with the actual input, so something like

c.execute("""SELECT password FROM users WHERE username=""" + username)

 

Hey guys!

 

There's a bit in the official docs about how you should never insert python variables into your SQL statements the way you did.

It opens up your code to an injection attack.

 

http://docs.python.org/2/library/sqlite3.html#module-sqlite3

 

 

# Never do this -- insecure!symbol = 'RHAT'c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)# Do this insteadt = ('RHAT',)c.execute('SELECT * FROM stocks WHERE symbol=?', t)print c.fetchone()# Larger example that inserts many records at a timepurchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),            ]c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

[oliverjrose99]

thanks alot

[MikeD]

Hey guys!

 

There's a bit in the official docs about how you should never insert python variables into your SQL statements the way you did.

It opens up your code to an injection attack.

 

http://docs.python.org/2/library/sqlite3.html#module-sqlite3

 

It's not just in python: any sql queries (in any language) should use prepared statements.

A while back I said the same thing to someone working with C# (here) and there is a whole topic for sql injection in this forum (http://linustechtips.com/main/topic/64224-sql-injection-web-programming-with-databases-watch-this/)

I just wanted to point out what the error was and keep it simple!