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.

Never safe plain text passwords, always save salted hashes. Get a library that does this for you. Under GDPR you're subject to hefty fines for saving plain passwords like this. — Robert
– Robert, Commented Oct 7, 2020 at 19:23

Cottonbud · Accepted Answer · 2015-11-30 18:17:49Z

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

iScrE4m · Accepted Answer · 2015-11-30 17:49:00Z

2

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

edited Nov 30, 2015 at 17:49

answered Nov 30, 2015 at 1:40

iScrE4m

8821 gold badge12 silver badges31 bronze badges

12 Comments

John Lisboa Over a year ago

that gives me the "Welcome" message every time, even when the username and password is incorrect.

iScrE4m Over a year ago

What does print c get you instead of welcome? When debugging code, it's often good to let it print everything it's getting. It's way easier to spot what it's actually doing that way.

John Lisboa Over a year ago

I get "<sqlite3.Cursor object at 0x1005e6ef8>"

iScrE4m Over a year ago

Oh sorry, it's probably good idea to create result = c.fetchone() and print result | edit: Just realized. What happens with if c.fetchone() is not None:?

iScrE4m Over a year ago

In that case my edit should do the trick, I'll edit the original answer and try and explain why :)

|

AlexDotis · Accepted Answer · 2016-11-02 18:03:47Z

0

db = sqlite3.connect('/SABB/DATASETS/SENHAS')

c = db.cursor()

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

if (len(login.fetchall()) > 0):
     print "Welcome"
else:
     print "Login failed"

answered Nov 2, 2016 at 18:03

AlexDotis

3345 silver badges14 bronze badges

2 Comments

Ajean Over a year ago

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. Code-only answers are discouraged.

AlexDotis Over a year ago

You're right. Sorry for that. The answer is here : stackoverflow.com/questions/2440147/…

Jean-François Fabre · Accepted Answer · 2022-04-13 12:18:44Z

-1

In your second approach, you are checking if a cursor object is greater than 0 rather than checking if the returned list of results has a length greater than 0. Since the variable has some object, the expression will evaluate to True.login is not a status as you may have assumed.

Your final code should be:

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

edited Apr 13, 2022 at 12:18

Jean-François Fabre♦

141k24 gold badges179 silver badges246 bronze badges

answered Nov 30, 2015 at 15:42

adit-39

796 bronze badges

2 Comments

John Lisboa Over a year ago

I'm still getting Login failed every time. I checked the databse and the records are correct.

adit-39 Over a year ago

The code assumes hardcoded values 'user' and 'pswd' for the fields usuario and senha in table sabb that should exist in the /SABB/DATASETS/SENHAS database. Checked it personally, should work without problems.

Collectives™ on Stack Overflow

Login script using Python and SQLite

4 Answers 4

Do not do this:

Comments

12 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Do not do this:

Comments

12 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related