Login script using Python and SQLite

Question

I'm trying to create a login script using SQLite to store the user data. Any ideas on how to to that? I have research over hours and found nothing of the kind. I would appreciate any help! :)

This is what I got so far:

user = raw_input "User:"
pswd = getpass.getpass "Password"

db = sqlite3.connect('/SABB/DATASETS/SENHAS')
c = db.cursor()
c.execute('SELECT 1 from sabb WHERE usuario = "user"')
('SELECT 1 from sabb WHERE senha = "pswd"')
if c.fetchall() is True:
    print "Welcome"
else:
    print "Login failed"

But it always returns Login failed... I want to check the input "user" and the input "pswd" against the database and if they match, return Welcome.

I changed it to:

db = sqlite3.connect('/SABB/DATASETS/SENHAS')
c = db.cursor()
login = c.execute('SELECT * from sabb WHERE usuario = "user" AND senha = "pswd"')
if (login > 0):
    print "Welcome"
else:
    print "Login failed"

But I'm still getting Welcome every time. I also tried "if (login == 1)" but then it only returns Login failed.

Solution 1

user = raw_input "User:"
pswd = getpass.getpass "Password"

db = sqlite3.connect('/SABB/DATASETS/SENHAS')
c = db.cursor()
c.execute('SELECT * from sabb WHERE usuario="%s" AND senha="%s"' % (user, pswd))
if c.fetchone() is not None:
    print "Welcome"
else:
    print "Login failed"

First: fetchone() and fatchall() don't return True, but a matching result. We want to use fetchone() because we are interested in one row in the database. If no row is found, fetchone() will return None.

We check if we got a matching result and print Welcome if we do. If fetchone() returns None, it goes to else statement - Login Failed

Here's my test log

>>> import sqlite3
>>> database = sqlite3.connect("my.db")
>>> db = database.cursor()
>>> Player = "iScrE4m"
>>> Played = 10
>>> db.execute("SELECT * FROM players WHERE Name='%s' AND Played='%i'" % (Player, Played))
<sqlite3.Cursor object at 0x02D3B3E0>
>>> print db.fetchone()
(1, u'iScrE4m', 1, 1494, 10, 5, 5)
>>> Played = 8
>>> db.execute("SELECT * FROM players WHERE Name='%s' AND Played='%i'" % (Player, Played))
<sqlite3.Cursor object at 0x02D3B3E0>
>>> print db.fetchone()
None
>>> 

Solution 2

From how I've read your source code, you're getting the username and password from your user, but not actually using this anywhere. Instead you'll want to substitute the actual username and password in your WHERE statements. I believe the code below would be the most pythonic:

# Get login details from user
user = input('User: ')
password = getpass.getpass('Password: ')

# Connect to database
db = sqlite3.connect('path/to/database')
c = db.cursor()

# Execute sql statement and grab all records where the "usuario" and
# "senha" are the same as "user" and "password"
c.execute('SELECT * FROM sabb WHERE usuario = ? AND senha = ?', (user, password))

# If nothing was found then c.fetchall() would be an empty list, which
# evaluates to False 
if c.fetchall():
    print('Welcome')
else:
    print('Login failed')

Please note that you should always the method provided by cursor.execute() for substituting data entered by a user into a database call. Using the format() or % substitution method leaves you open to sql injection.

Do not do this:

c.execute('SELECT * from sabb WHERE usuario="%s" AND senha="%s"' % (user, pswd))

Imagine if someone passed in:

• User = Bob
• Password = my_cool_password" OR 1=1; --

The cursor would evaluate: SELECT * from sabb WHERE usuario="Bob" AND senha="my_cool_password" OR 1=1; -- ";

It'd allow me to log in as any user. By trivially changing my input I could execute any command on the database that I wish (including deleting a login, adding a login, dropping the entire table etc).