Store Password Securely in SQLite: Python, SQLAlchemy, and Bcrypt

Storing passwords securely is a fundamental aspect of building robust and secure applications.

In this article, we will explore how to store encrypted passwords in an SQLite database using Python, SQLAlchemy, and the bcrypt hashing algorithm. By combining the power of SQLAlchemy’s ORM with bcrypt’s robust password hashing capabilities, we can ensure that passwords are stored in a hashed and encrypted format, protecting user credentials from potential security breaches. We’ll guide you through the process of setting up the development environment, defining the database model, encrypting and storing passwords, and validating them when needed. Let’s dive in!

Prerequisites
Setting Up the Development Environment
Creating the SQLite Database and Table
Storing Encrypted Passwords
Complete code
Conclusion

Prerequisites

Before proceeding, make sure you have the following prerequisites:

Basic knowledge of Python programming language.
Familiarity with SQLAlchemy and SQLite.
Understanding of password hashing and encryption concepts.
Installation of necessary libraries: SQLAlchemy and bcrypt.

Setting Up the Development Environment

Let’s start by creating a requirements.txt file:

bcrypt==4.0.1
SQLAlchemy==2.0.18

Ensure that you have Python installed on your system and install the required libraries:

python3 -m venv .venv
source .venv/bin/activate
pip3 install -r requirements.txt

Creating the SQLite Database and Table

Using SQLAlchemy, define a user table that will store the encrypted passwords in the SQLite database:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker, declarative_base
import bcrypt

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(100), unique=True)
    hashed_password = Column(String(100))

Storing Encrypted Passwords

To store encrypted passwords in the SQLite database, follow these steps:

Import the necessary libraries:

from sqlalchemy.orm import sessionmaker
import bcrypt

Set up the database connection:

engine = create_engine('sqlite://')  # in memory database
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()

Hash and store the passwords:

    ##############################
    ## Storage and encryption   ##
    ##############################

    password = "password123"

    # convert password to array of bytes
    bytes = password.encode()

    # Generate a salt and hash the password
    salt = bcrypt.gensalt()
    hashed_password = bcrypt.hashpw(bytes, salt)

    # Create a new user with the hashed password
    new_user = User(username='example_user', hashed_password=hashed_password.decode())
    session.add(new_user)
    session.commit()

Verifying Passwords:

To verify a user’s password during authentication, retrieve the hashed password from the database and compare it with the provided password:

    ##############################
    ## Retrieval and comparison ##
    ##############################
    username = "example_user"
    userPassword = "password123"
    userBytes = userPassword.encode()

    # Retrieve the user from the database
    dbUser = session.query(User).filter_by(username=username).first()

    if dbUser and bcrypt.checkpw(userBytes, dbUser.hashed_password.encode()):
        print("Authentication successful.")
    else:
        print("Invalid username or password.")

    for user in session.query(User).all():
        print(user.username, user.hashed_password)

    session.close()

Complete code

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker, declarative_base
import bcrypt

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(100), unique=True)
    hashed_password = Column(String(100))


if __name__ == '__main__':
    engine = create_engine('sqlite://')  # in memory database
    Base.metadata.create_all(bind=engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    ##############################
    ## Storage and encryption   ##
    ##############################

    password = "password123"

    # convert password to array of bytes
    bytes = password.encode()

    # Generate a salt and hash the password
    salt = bcrypt.gensalt()
    hashed_password = bcrypt.hashpw(bytes, salt)

    # Create a new user with the hashed password
    new_user = User(username='example_user', hashed_password=hashed_password.decode())
    session.add(new_user)
    session.commit()

    ##############################
    ## Retrieval and comparison ##
    ##############################

    username = "example_user"
    userPassword = "password123"
    userBytes = userPassword.encode()

    # Retrieve the user from the database
    dbUser = session.query(User).filter_by(username=username).first()

    if dbUser and bcrypt.checkpw(userBytes, dbUser.hashed_password.encode()):
        print("Authentication successful.")
    else:
        print("Invalid username or password.")

    for user in session.query(User).all():
        print(user.username, user.hashed_password)
    session.close()

Output:

Authentication successful.
example_user $2b$12$CfIdY8Fp2cJO7Vnj./ssFuw0ROt/QzcOTJ8cLPMRoIsbuIfk/.0ae

Conclusion

By combining the power of Python, SQLAlchemy, and bcrypt, you can securely store encrypted passwords in an SQLite database. This approach ensures that user passwords remain protected and reduces the risk of unauthorized access. By following the steps outlined in this article, you can implement robust password encryption in your applications, bolstering the overall security and protecting user credentials.

Table of Contents