Python and SQLite

SQLite databases are the common method of storing application data, as they’re lightweight, and what passes for the database ‘engine’ exists inside the same process as the application. What often happens is a given application will store passwords and other sensitive information as plaintext in an SQLite record, as discovered recently with the Starbucks ‘app’.
The programs here follow the same principle as with my last post – passwords are checked by their SHA256 values and are never stored in their original form. One thing I forgot to mention is the SHA256 value is always 256-bits in length, which is what enables any password to become a 256-bit encryption key. Where symmetric encryption is used, it might be worth checking if a stored hash is being used as a key.

For the demonstration here, we’ll need the following:

SQLite3 for Python
SQLite database browser/manager
PythonCard for the front-end

To set things up, we use an SQLite browser/manager to create a database with a table called ‘Hashes‘. This will have two fields – ‘Username‘ and ‘Hash‘, and both are populated when the first program (AddHash.py) runs. The SQLite file itself is saved as ‘hashBase.db‘, and becomes the application database. All the files are available at SourceForge.net if anyone wants to re-use the code.

Adding an Account to the Database

The first script (AddHash.py) simply creates a record in the database to store a user name and the password’s SHA256 value. After importing the sqlite3 and hashlib modules, we connect to the database:


				conn = sqlite3.connect('hashBase.db')

				c = conn.cursor()

The user enters a login name and password, and an SHA256 hash is generated exactly as in my previous post:


				currentUser = raw_input("User name: ")

				txtPassword = raw_input("Password: ")

				currentPassword = hashlib.sha256(txtPassword).hexdigest()

Instead of writing currentPassword to a text file, a record is created and the two variables, currentUser and currentPassword, are written sequentially to it. The conn.commit() function makes the changes persistent:


				c.execute("insert into Hashes values (?, ?)", (currentUser, currentPassword))

				conn.commit()

				print "Password added."

Finally the connection to the database is closed: conn.close() After running the program several times with different user names, the database should have multiple records when viewed again in the SQLite browser.

Checking Account Logins and Passwords

The second example is a little more tricky to create, as the program here (CheckPassword.py) must do three things:

1. Check whether the user account exists in the database.
2. Retrieve the correct SHA256 value as a string.
3. Compare the hashed password against the SHA256 value.

Again there are three variables, as we take the username and generate the hash for the login attempt:


				currentUser = raw_input("Login: ")

				currentPass = raw_input("Enter password: ")

				currentHash = hashlib.sha256(currentPass).hexdigest()

Pull the correct record and field from the database:


				t = (currentUser,)

				c.execute('SELECT Hash FROM Hashes WHERE Username=?', t)

To make this work properly, the program must check whether it’s pulled a record for the username, and fetch the SHA256 value only if it has:


				row = c.fetchone()

				if row is None:

				print "Account not found"

				else:

				fetchedHash = row[0]

The value is then compared with currentHash (the password attempt):


				if fetchedHash == currentHash:

				print "Login Success."

				else:

				print "Login Fail."

Both programs could also be implemented as a PythonCard GUI, although I still declared c.fetchone() and the input boxes locally as variables currentUser and currentPassword for both handlers, as a workaround for the return error problem.

Real-World Applications and Vulnerabilities

Compared with the amount of code required for reading and writing plaintext passwords to SQLite, there’s only one variable and a couple of lines difference in Python, but this is a simplification as the underlying code for the hashlib and sqlite3 functions is either C or C++. If a developer truly wanted to create an application for doing this securely, probably half the code (for a relatively small program) would be for preventing SQL injection and bruteforce attacks, ensuring passwords meet certain criteria, and ensuring that a specific part of the code is the only entry point to whatever the login screen’s supposed to protect.

Python and SQLite3 Databases

Adding an Account to the Database

Checking Account Logins and Passwords

Real-World Applications and Vulnerabilities

References