Storing Plain Text Passwords

Learn about the pitfalls of storing plain text passwords and the simplest way to secure these passwords.

We'll cover the following

Storing passwords the easy way

At its core, authentication is a simple job; you simply compare the user’s correct password with the one just received. I like simple solutions, and storing passwords in plain text is the absolute simplest.

import sqlite3
conn = sqlite3.connect('users.db')
cursor = conn.cursor().execute('''
CREATE TABLE IF NOT EXISTS users (
    username VARCHAR(16) PRIMARY KEY,
    password VARCHAR(16)
)
''')
def create_account(username, password):
    if len(password) < 8:
        raise Exception('Password too short')
    if len(password) > 16:
        raise Exception('Password too long')
    cursor = conn.cursor()
    cursor.execute('SELECT count(*) FROM users WHERE username=?', (username,))
    result = cursor.fetchone()
    if result[0] > 0:
        raise Exception('Username already taken')
    cursor.execute('INSERT INTO users (username, password) VALUES (?, ?)', (username, password))
    conn.commit()
def login(username, password):
    cursor = conn.cursor()
    cursor.execute('SELECT count(*) FROM users WHERE username=? AND password=?', (username, password))
    result = cursor.fetchone()
    if result[0] == 0:
        raise Exception('Invalid username or password')
create_account('jim', 'a-password')
create_account('sue', 'another-password')
try:
    login('jim', 'bad-password')
except Exception as e:
    print('Login error: %s' % (e))
login('jim', 'a-password')
print('Login succeeded')

Nothing complicated here, just a few basic checks and an INSERT. Unfortunately, no amount of simplicity can make up for how terrible this is. If you’re reading a course on password storage, you probably know why it’s terrible. However, since even Sony and Yahoo managed to leak plain text passwords, I’d better tell you anyway.

What’s wrong with storing plain text passwords?

The problem is that passwords are valuable, even if the site in question has nothing else of value. No one likes keeping up with passwords, so people use what they can remember their passwords. Most sites prevent password, batman, letmein, etc., by enforcing some basic complexity rules. People begrudgingly pick one or two complex passwords they can remember and use them everywhere: Thund3rB!rd, 1AmTheB4tman!, S4llyNme4Ever!.

Re-using a password is like handing the key to your safety deposit box to the kid who watches your dog, but your users don’t care. So, while your little to-do list app may not have much of value to an attacker, that password you’re storing will probably let you into their Wi-Fi, email, bank, and that random sketchy site they don’t even know why they have an account on.

Therefore, please, use a password manager, and encourage your friends and family to do the same, especially those that don’t study password storage in their free time!

Upgrading plain text passwords

If you currently have a database of plaintext passwords, don’t despair. It’s actually really easy to fix. Especially if you can tolerate a little downtime. Start by replacing the account creation and login code with something better (see this lesson). Then, in a maintenance window, convert the passwords to the new format and delete the originals.

Here’s an example script that hashes passwords from the previous example:

import sqlite3
from nacl import pwhash
conn = sqlite3.connect('users.db')
cursor = conn.cursor()
print('before:')
for row in conn.cursor().execute('SELECT * FROM users'):
    print(row)
cursor.execute('ALTER TABLE users ADD COLUMN nacl_pwhash VARCHAR(100)')
params = []
for row in cursor.execute('SELECT password, username FROM users WHERE password != ""'):
    params.append(
        (pwhash.str(bytes(row[0], 'UTF-8')), row[1]),
    )
cursor.executemany('UPDATE users SET nacl_pwhash=?, password="" WHERE username=?', params)
conn.commit()
print('after:')
for row in conn.cursor().execute('SELECT * FROM users'):
    print(row)

This script hashes the plaintext passwords and removes the old values. SQLite doesn’t allow columns to be dropped, so this script clears the value. If you’re using a different database, just drop the column. Regardless of the method, make sure those passwords are completely erased.