Secure a Database with Python

Defend a Python SQLite app against SQL injection and encrypt sensitive fields.

Introduction

30 Second Summary

Every time you sign up for a website, your personal data goes into a database. If that database is not properly secured, a single malicious input can expose every record in the system.

In this project, you will build a Python application that stores sensitive data in a SQLite database, deliberately attack it with SQL injection, then harden it with parameterized queries and field-level encryption.

What You'll Build

A secure user management system in Python that protects against SQL injection attacks and encrypts sensitive data at rest using Fernet symmetric encryption.

By the end of this project, you'll have:

  • A vulnerable database app that you will attack with SQL injection to see how the exploit works.
  • A hardened version using parameterized queries that blocks the same attacks.
  • Fernet encryption functions that protect sensitive fields like social security numbers at rest.
  • Secret Mission: Add password hashing with bcrypt and a login system that resists brute-force attacks.

Are there any prerequisites?

No prior experience with databases or security is needed. This guide includes full setup instructions for Python, VS Code, and all required packages.

Before We Start

To build a secure database application, you need a few tools in place first.

In this step, you will install Python 3 and VS Code, create an isolated project environment, and grab the security packages you need for encryption.

In this step, get ready to:
  • Install Python 3 and VS Code.
  • Create a project directory and virtual environment.
  • Install the cryptography and python-dotenv packages.
Install Python 3 and VS Code

Python is the programming language you will use throughout this project. The cryptography library requires Python 3.9 or higher.

  • Open your terminal by pressing Cmd+Space (macOS) or the Windows key (Windows) and typing Terminal.
  • Check your Python version by running this command:
python3 --version

✔️ I see Python 3.9 or higher

You are good to go. Python is ready for this project.

ⓧ I see an older version

You need Python 3.9 or higher. Visit the official Python downloads page to install a newer version:

  • Go to python.org/downloads.
  • Download the latest Python 3 installer for your operating system.
  • Run the installer. On Windows, check the box that says Add Python to PATH before clicking Install.
  • Close and reopen your terminal, then run python3 --version again to confirm the update.

ⓧ Command not found

Python is not installed yet. Follow these steps to install it:

  • Go to python.org/downloads.
  • Download the latest Python 3 installer for your operating system.
  • Run the installer. On Windows, check the box that says Add Python to PATH before clicking Install.
  • Close and reopen your terminal, then run python3 --version to confirm the installation.

Next, you need a code editor. VS Code is a free, lightweight editor that works on all platforms.

  • If you already have VS Code installed, open it by pressing Cmd+Space (macOS) or the Windows key (Windows) and typing VS Code.
  • If you do not have it yet, download it from code.visualstudio.com.
  • Run the installer and open VS Code once it finishes.
Create your project directory and virtual environment

A virtual environment isolates your project's packages from the rest of your system. This prevents version conflicts between different projects on your machine.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir secure-database
cd secure-database
  • Confirm the folder exists by running:
ls

You should see an empty directory with no output. That is expected for a brand new folder.

  • Create a virtual environment by running this command:
python3 -m venv venv

What is a virtual environment?

A virtual environment is a self-contained directory that holds its own copy of Python and pip. Any packages you install inside it stay isolated from your system Python.

This way, each project on your machine can have its own dependencies without conflicts.

  • Activate the virtual environment by running the command for your operating system:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate

Linux

source venv/bin/activate

You should see (venv) appear at the beginning of your terminal prompt. This confirms the virtual environment is active.

Don't see (venv) in your prompt?

  • Make sure you are inside the secure-database folder. Run ls and confirm you see a venv folder listed.
  • On Windows, if you see an error about execution policies, run Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned and try the activate command again.

Help me activate my virtual environment.

Install the required packages

You need two packages for this project. The cryptography library provides Fernet symmetric encryption. The python-dotenv library loads environment variables from a .env file.

  • Install both packages by running this command:
pip install cryptography==48.0.0 python-dotenv

What does this install?

  • cryptography provides the Fernet encryption algorithm, which bundles AES encryption with HMAC authentication into a simple encrypt/decrypt API.
  • python-dotenv reads key-value pairs from a .env file and loads them as environment variables. You will use this to store your encryption key safely.

You should see output ending with a line like Successfully installed followed by the package names.

Seeing an install error?

  • Confirm your virtual environment is active. You should see (venv) in your prompt.
  • If you see a permissions error, make sure you activated the virtual environment before running pip install. Installing inside a virtual environment does not require administrator privileges.

Help me fix my pip install error.

Now create a .gitignore file to keep sensitive files and generated artifacts out of version control.

  • In VS Code, open the secure-database folder by clicking File in the top menu bar, then Open Folder.
  • Select the secure-database folder on your Desktop, then click Open.
  • Create a new file by clicking File then New File.
  • Save it immediately as .gitignore by pressing Cmd+S (macOS) or Ctrl+S (Windows) and typing .gitignore as the filename.
  • Paste the following contents into .gitignore:
__pycache__/
venv/
*.db
.env

What does this .gitignore do?

  • __pycache__/ excludes Python bytecode files that are generated automatically.
  • venv/ excludes your virtual environment directory (it can be recreated from scratch).
  • *.db excludes SQLite database files that will contain test data.
  • .env excludes your environment file, which will hold your encryption key. This is critical for security.
  • Save .gitignore.

✔️ Awesome, I've got everything!

Great. Double check you have saved .gitignore in the secure-database folder.

ⓧ I'd like to double check the full code

Here is the complete .gitignore file. Make sure yours matches exactly:

__pycache__/
venv/
*.db
.env

Your environment is locked and loaded. Next up, you will build a deliberately vulnerable database app and attack it with SQL injection to see the threat firsthand.

Build a Vulnerable Database App

Before you can defend a database, you need to understand how attacks work. SQL injection is the #3 vulnerability on the OWASP Top 10 (2021 edition). Despite being preventable for over 20 years, it still accounts for a significant portion of web application breaches.

In this step, you will build a deliberately vulnerable database app, then attack it yourself. Experiencing the attack firsthand is the fastest way to understand why parameterized queries matter.

In this step, get ready to:
  • Create a SQLite database with a users table storing usernames, roles, and SSNs.
  • Write a query function that uses unsafe string concatenation.
  • Run a SQL injection attack against your own app and watch it succeed.
Create a SQLite database with a users table

SQLite is a lightweight database built into Python's standard library. It requires zero setup and stores everything in a single file.

  • In VS Code, create a new file called vulnerable_app.py inside your project folder.
  • Add the following code to set up the database with sample user data:
import sqlite3

def setup_database():
    """Create a database with sample data (VULNERABLE VERSION)."""
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    
    cursor.execute('DROP TABLE IF EXISTS users')
    cursor.execute('''
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL,
            role TEXT NOT NULL,
            ssn TEXT NOT NULL
        )
    ''')
    
    users = [
        ('alice', 'admin', '123-45-6789'),
        ('bob', 'user', '987-65-4321'),
        ('charlie', 'user', '555-12-3456'),
    ]
    cursor.executemany(
        'INSERT INTO users (username, role, ssn) VALUES (?, ?, ?)', users
    )
    conn.commit()
    conn.close()
    print("Database created with sample data.")

What does this code do?

sqlite3.connect('users.db') creates (or opens) a database file called users.db in your project folder.

The CREATE TABLE statement defines a users table with four columns: id, username, role, and ssn. Three sample users are inserted with their social security numbers stored as plain text.

  • Save vulnerable_app.py.

Seeing a red underline in VS Code?

If sqlite3 shows a warning, make sure your VS Code Python interpreter is set to the virtual environment you created in Step 1. Click the Python version in the bottom status bar and select the interpreter inside your venv folder.

Help me fix my Python interpreter in VS Code

Write the vulnerable query function

This function deliberately uses string concatenation to build a SQL query. This is the exact pattern that makes applications vulnerable to injection attacks.

  • In vulnerable_app.py, add the following function below setup_database():
def get_user_vulnerable(username):
    """VULNERABLE: Uses string concatenation. DO NOT use in production."""
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    
    query = f"SELECT * FROM users WHERE username = '{username}'"
    print(f"Executing query: {query}")
    
    cursor.execute(query)
    results = cursor.fetchall()
    conn.close()
    return results

Why is string concatenation dangerous?

The f-string on line query = f"SELECT * FROM users WHERE username = '{username}'" directly inserts whatever the user types into the SQL statement. If a user types special SQL characters, the database treats them as part of the query, not as data.

This means an attacker can inject their own SQL commands by crafting a specific input string. You will see this in action in the next chunk.

  • Save vulnerable_app.py.
Run a SQL injection attack against your own app

Now you will add a main block that demonstrates both a normal query and a malicious one. The malicious input uses a classic SQL injection payload to bypass the username check entirely.

  • At the bottom of vulnerable_app.py, add this code to run the demonstration:
if __name__ == '__main__':
    setup_database()
    
    print("\n--- Normal query ---")
    result = get_user_vulnerable('alice')
    print(f"Result: {result}")
    
    print("\n--- SQL Injection Attack ---")
    malicious_input = "' OR '1'='1' --"
    result = get_user_vulnerable(malicious_input)
    print(f"Result: {result}")
    print(f"Returned {len(result)} rows (should be 0 for invalid user!)")

How does the injection payload work?

The payload ' OR '1'='1' -- closes the original quote, adds an OR condition that is always true (1=1), and uses -- to comment out the rest of the query.

The database interprets this as "give me all users where username matches OR where 1 equals 1." Since 1 always equals 1, every row is returned. The attacker gets all user data, including SSNs.

  • Save vulnerable_app.py.
  • Run the script by executing this command in your terminal:
python3 vulnerable_app.py

You should see two sections of output. The normal query returns only Alice's row. The SQL injection attack returns all 3 rows, exposing every user's data including their SSNs.

Seeing a ModuleNotFoundError?

Make sure your virtual environment is activated. You should see (venv) at the beginning of your terminal prompt. If not, activate it again from your project folder.

Help me troubleshoot running my Python script

✔️ Awesome, I've got everything!

Great. Double check you have saved vulnerable_app.py and that your output shows all 3 rows returned by the injection attack.

ⓧ I'd like to double check the full code

Here is the complete vulnerable_app.py file:

import sqlite3

def setup_database():
    """Create a database with sample data (VULNERABLE VERSION)."""
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    
    cursor.execute('DROP TABLE IF EXISTS users')
    cursor.execute('''
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL,
            role TEXT NOT NULL,
            ssn TEXT NOT NULL
        )
    ''')
    
    users = [
        ('alice', 'admin', '123-45-6789'),
        ('bob', 'user', '987-65-4321'),
        ('charlie', 'user', '555-12-3456'),
    ]
    cursor.executemany(
        'INSERT INTO users (username, role, ssn) VALUES (?, ?, ?)', users
    )
    conn.commit()
    conn.close()
    print("Database created with sample data.")


def get_user_vulnerable(username):
    """VULNERABLE: Uses string concatenation. DO NOT use in production."""
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    
    query = f"SELECT * FROM users WHERE username = '{username}'"
    print(f"Executing query: {query}")
    
    cursor.execute(query)
    results = cursor.fetchall()
    conn.close()
    return results


if __name__ == '__main__':
    setup_database()
    
    print("\n--- Normal query ---")
    result = get_user_vulnerable('alice')
    print(f"Result: {result}")
    
    print("\n--- SQL Injection Attack ---")
    malicious_input = "' OR '1'='1' --"
    result = get_user_vulnerable(malicious_input)
    print(f"Result: {result}")
    print(f"Returned {len(result)} rows (should be 0 for invalid user!")

You have just proven that string concatenation in SQL queries is exploitable. Any attacker who can type into an input field can dump your entire database. In the next step, you will fix this vulnerability with parameterized queries.

Harden with Parameterized Queries

In the last step, you proved how dangerous SQL injection is. A single malicious string returned every row in your database.

Now you will fix the vulnerability by rewriting your query function to use parameterized queries. Instead of gluing user input directly into SQL strings, you will pass data as separate parameters that the database engine treats as literal values.

In this step, get ready to:
  • Rewrite the user lookup to use parameterized queries with placeholders.
  • Run the same SQL injection attack and watch it fail.
  • Add input validation as a secondary defense layer.
Rewrite get_user to use parameterized queries

Parameterized queries separate the SQL structure from the data. The database engine knows the ? placeholder is a value slot, not part of the command. Any special characters in the input are treated as literal text.

  • Create a new file called secure_app.py in your project folder.
  • Copy the following code into it:
import sqlite3
import os
from cryptography.fernet import Fernet
from dotenv import load_dotenv


def get_user_secure(username, fernet):
    """SECURE: Uses parameterized queries and decrypts sensitive data."""
    conn = sqlite3.connect('secure_users.db')
    cursor = conn.cursor()

    # The ? placeholder keeps user input separate from the SQL command
    cursor.execute(
        'SELECT id, username, role, encrypted_ssn FROM users WHERE username = ?',
        (username,)
    )
    row = cursor.fetchone()
    conn.close()

    if row:
        return {
            'id': row[0],
            'username': row[1],
            'role': row[2],
            'ssn': decrypt_data(row[3], fernet)
        }
    return None

What does this code do?

The ? in the SQL string is a placeholder. SQLite receives the query structure and the user input as two separate things. Even if the input contains SQL commands, the database treats the whole string as a literal username value.

Notice the tuple (username,) as the second argument. The trailing comma makes it a tuple. SQLite requires parameters in a tuple or list.

Why are there imports we haven't used yet?

The imports for Fernet and load_dotenv are for the encryption features you will add in the next step. The function also references decrypt_data which you will write later. For now, the parameterized query is the focus.

  • Save secure_app.py.
Run the same SQL injection attack and see it fail

You will now throw the exact same attack payload at your new function. Because get_user_secure uses a parameterized query, the database will look for a user literally named ' OR '1'='1' --. No such user exists, so it returns nothing.

  • Test the injection by running this command in your terminal:
python3 << 'EOF'
import sqlite3
from secure_app import get_user_secure

# Create a temporary test database
conn = sqlite3.connect('secure_users.db')
cursor = conn.cursor()
cursor.execute('DROP TABLE IF EXISTS users')
cursor.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        role TEXT NOT NULL,
        encrypted_ssn TEXT NOT NULL
    )
''')
cursor.execute(
    'INSERT INTO users (username, role, encrypted_ssn) VALUES (?, ?, ?)',
    ('alice', 'admin', 'test_placeholder')
)
conn.commit()
conn.close()

# Try the same injection from vulnerable_app.py
malicious_input = "' OR '1'='1' --"
result = get_user_secure(malicious_input, None)
print(f"Injection result: {result}")
print("Attack blocked!" if result is None else "WARNING: Attack succeeded!")
EOF

What does this test do?

This script creates a temporary secure_users.db database with one test user, then imports your get_user_secure function and runs the same malicious payload from the previous step.

Because the injection payload does not match any real username, fetchone() returns None. The function never reaches the decrypt_data call, so the missing function is not a problem yet.

  • You should see this output:
Injection result: None
Attack blocked!

Compare this to the vulnerable version, which returned all 3 rows. The same attack string is now completely harmless.

Seeing a ModuleNotFoundError?

Make sure your virtual environment is still activated. You should see (venv) at the start of your terminal prompt.

If you see an error about secure_app not being found, make sure your terminal is in the same directory as secure_app.py.

Still stuck? Help me debug this ModuleNotFoundError when testing my secure_app.py

Add input validation as a secondary defense layer

Parameterized queries are your primary defense. Input validation is a second layer that catches obviously malicious input before it even reaches the database.

  • Open secure_app.py and add this function below get_user_secure:
def validate_input(username):
    """Secondary defense: validate input format."""
    # Reject empty input
    if not username:
        return False
    # Reject suspiciously long strings
    if len(username) > 50:
        return False
    # Only allow letters and numbers
    if not username.isalnum():
        return False
    return True

What does this code do?

This function applies three checks before the input ever reaches a query. It rejects empty strings, anything longer than 50 characters, and any input containing special characters like quotes or dashes.

The injection payload ' OR '1'='1' -- contains single quotes, spaces, and dashes. It would fail the isalnum() check immediately.

Why two defenses instead of one?

This is called defense in depth. Parameterized queries prevent the attack at the database level. Input validation prevents it at the application level. If one layer has a bug, the other still protects you.

  • Save secure_app.py.
  • Verify the validation works by running this quick test:
python3 -c "from secure_app import validate_input; print(validate_input('alice')); print(validate_input(\"' OR '1'='1' --\"))"
  • You should see True for the valid username and False for the injection payload.

Seeing False for both inputs?

Check that your validate_input function returns True at the very end (after all the checks pass). A missing final return True causes the function to return None which prints as None, not False.

Need help? Help me fix my validate_input function

✔️ Awesome, I've got everything!

Great. Double check you have saved your file.

ⓧ I'd like to double check the full code

Here is the complete secure_app.py so far:

import sqlite3
import os
from cryptography.fernet import Fernet
from dotenv import load_dotenv


def get_user_secure(username, fernet):
    """SECURE: Uses parameterized queries and decrypts sensitive data."""
    conn = sqlite3.connect('secure_users.db')
    cursor = conn.cursor()

    cursor.execute(
        'SELECT id, username, role, encrypted_ssn FROM users WHERE username = ?',
        (username,)
    )
    row = cursor.fetchone()
    conn.close()

    if row:
        return {
            'id': row[0],
            'username': row[1],
            'role': row[2],
            'ssn': decrypt_data(row[3], fernet)
        }
    return None


def validate_input(username):
    """Secondary defense: validate input format."""
    if not username:
        return False
    if len(username) > 50:
        return False
    if not username.isalnum():
        return False
    return True

Your queries are now injection-proof. But there is still a problem. Anyone who opens the database file can read every user's social security number in plain text. Next up, you will encrypt those sensitive fields so they are unreadable without the key.

Encrypt Sensitive Fields at Rest

Your queries are now protected against injection attacks. But there's another threat: if someone steals the database file itself, every SSN is readable in plain text.

In this step, you'll add field-level encryption using Fernet symmetric encryption. Sensitive data gets encrypted before storage. Only your app can decrypt it with the correct key.

In this step, get ready to:
  • Generate a Fernet encryption key and store it in a .env file.
  • Write encrypt and decrypt helper functions.
  • Modify the database setup to encrypt SSN values before storage.
Generate a Fernet encryption key and store it in a .env file

Encryption requires a secret key. Anyone with this key can decrypt your data, so it must never be committed to version control.

You'll write a function that loads the key from an environment variable. If no key exists yet, the function generates one and saves it to a .env file automatically.

  • In secure_app.py, find the line that says def get_user_secure(username, fernet):.
  • Add the following function above it, right below the import statements:
def load_encryption_key():
    """Load encryption key from environment variable."""
    load_dotenv()
    key = os.getenv('ENCRYPTION_KEY')
    
    if not key:
        key = Fernet.generate_key().decode()
        with open('.env', 'w') as f:
            f.write(f'ENCRYPTION_KEY={key}\n')
        print("Generated new encryption key and saved to .env")
    
    return key.encode()

What does this code do?

load_dotenv() reads key-value pairs from your .env file into environment variables. This keeps secrets out of your source code.

If no key is found, Fernet.generate_key() creates a cryptographically secure 256-bit key. The function saves it to .env so the same key is used every time you run the app.

  • Save secure_app.py.

You won't see a change yet. The next chunk creates the .gitignore file to protect this key from accidental commits.

Your .env file and database files should never end up in version control. A .gitignore file tells Git to exclude them.

  • Create a new file called .gitignore in your project folder with this content:
.env
*.db
__pycache__/
venv/

What does this file do?

.env excludes your encryption key. *.db excludes all database files. __pycache__/ and venv/ exclude Python's compiled files and your virtual environment.

  • Save .gitignore.
  • Confirm the file exists by running:
ls -a

You should see .gitignore in the output alongside your .py files.

Don't see .gitignore in the listing?

Files starting with a dot are hidden by default. The -a flag in ls -a reveals hidden files. Make sure you used the -a flag.

help me find my .gitignore file

Write encrypt_data and decrypt_data functions

These two helper functions handle the conversion between plain text and encrypted text. Every sensitive value passes through encrypt_data before storage and decrypt_data on retrieval.

  • In secure_app.py, find your new load_encryption_key() function.
  • Add the following two functions directly below it, above get_user_secure:
def encrypt_data(data, fernet):
    """Encrypt a string value."""
    return fernet.encrypt(data.encode()).decode()


def decrypt_data(encrypted_data, fernet):
    """Decrypt an encrypted string value."""
    return fernet.decrypt(encrypted_data.encode()).decode()

What does this code do?

encrypt_data takes a plain string, encodes it to bytes, encrypts it with Fernet, then decodes back to a string for database storage.

decrypt_data reverses the process. It takes the encrypted string from the database, encodes to bytes, decrypts with the same key, then decodes back to readable text.

  • Save secure_app.py.

You won't see a change yet. The next chunk uses these functions to encrypt data during database setup.

Modify the app to encrypt SSN before storage and decrypt on retrieval

Now you need a database setup function that encrypts each SSN before inserting it. Your existing get_user_secure function already calls decrypt_data on retrieval, so you only need to handle the storage side.

  • In secure_app.py, find the line def get_user_secure(username, fernet):.
  • Add the following function above it, below your decrypt_data function:
def setup_secure_database(fernet):
    """Create a database with encrypted sensitive fields."""
    conn = sqlite3.connect('secure_users.db')
    cursor = conn.cursor()
    
    cursor.execute('DROP TABLE IF EXISTS users')
    cursor.execute('''
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL,
            role TEXT NOT NULL,
            encrypted_ssn TEXT NOT NULL
        )
    ''')
    
    users = [
        ('alice', 'admin', '123-45-6789'),
        ('bob', 'user', '987-65-4321'),
        ('charlie', 'user', '555-12-3456'),
    ]
    
    for username, role, ssn in users:
        encrypted_ssn = encrypt_data(ssn, fernet)
        cursor.execute(
            'INSERT INTO users (username, role, encrypted_ssn) VALUES (?, ?, ?)',
            (username, role, encrypted_ssn)
        )
    
    conn.commit()
    conn.close()
    print("Secure database created with encrypted SSN fields.")

What does this code do?

This function creates a fresh secure_users.db database with a column called encrypted_ssn instead of plain ssn.

The loop calls encrypt_data(ssn, fernet) on each SSN before inserting it. The database only ever stores the encrypted version. It also uses parameterized queries with ? placeholders for the INSERT statement.

  • Save secure_app.py.

You won't see a change yet. The next chunk adds a main block that ties everything together and runs the app.

The final piece is the main block that loads the key, sets up the database, and runs queries to prove everything works.

  • Scroll to the very bottom of secure_app.py, below the validate_input function.
  • Add this main block:
if __name__ == '__main__':
    key = load_encryption_key()
    fernet = Fernet(key)
    
    setup_secure_database(fernet)
    
    print("\n--- Normal query (secure) ---")
    user = get_user_secure('alice', fernet)
    print(f"User: {user}")
    
    print("\n--- SQL Injection attempt (secure) ---")
    malicious_input = "' OR '1'='1' --"
    user = get_user_secure(malicious_input, fernet)
    print(f"Result: {user}")
    print("Attack blocked! Returned None for invalid input.")
    
    print("\n--- Input validation ---")
    test_inputs = ["alice", "' OR '1'='1", "a" * 100, ""]
    for test in test_inputs:
        valid = validate_input(test)
        print(f"  '{test[:30]}...' -> {'VALID' if valid else 'REJECTED'}")

What does this code do?

load_encryption_key() retrieves (or generates) the Fernet key. Fernet(key) creates a cipher object you pass to every encrypt/decrypt call.

The script then sets up the database with encrypted SSNs, retrieves Alice's record (decrypting her SSN), and attempts the same SQL injection payload from earlier. The input validation section tests several edge cases.

  • Save secure_app.py.
  • Run the script by entering this command:
python3 secure_app.py

You should see output showing the encrypted database was created, Alice's record with her decrypted SSN 123-45-6789, the injection attack returning None, and the input validation results.

Seeing a NameError for decrypt_data or encrypt_data?

Make sure encrypt_data and decrypt_data are defined above the functions that call them. Python reads files top to bottom. encrypt_data must appear above setup_secure_database, and decrypt_data must appear above get_user_secure.

help me fix a NameError in my secure_app.py

✔️ Awesome, I've got everything!

Great. Double check you've saved your file.

ⓧ I'd like to double check the full code

import sqlite3
import os
from cryptography.fernet import Fernet
from dotenv import load_dotenv


def load_encryption_key():
    """Load encryption key from environment variable."""
    load_dotenv()
    key = os.getenv('ENCRYPTION_KEY')
    
    if not key:
        key = Fernet.generate_key().decode()
        with open('.env', 'w') as f:
            f.write(f'ENCRYPTION_KEY={key}\n')
        print("Generated new encryption key and saved to .env")
    
    return key.encode()


def encrypt_data(data, fernet):
    """Encrypt a string value."""
    return fernet.encrypt(data.encode()).decode()


def decrypt_data(encrypted_data, fernet):
    """Decrypt an encrypted string value."""
    return fernet.decrypt(encrypted_data.encode()).decode()


def setup_secure_database(fernet):
    """Create a database with encrypted sensitive fields."""
    conn = sqlite3.connect('secure_users.db')
    cursor = conn.cursor()
    
    cursor.execute('DROP TABLE IF EXISTS users')
    cursor.execute('''
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL,
            role TEXT NOT NULL,
            encrypted_ssn TEXT NOT NULL
        )
    ''')
    
    users = [
        ('alice', 'admin', '123-45-6789'),
        ('bob', 'user', '987-65-4321'),
        ('charlie', 'user', '555-12-3456'),
    ]
    
    for username, role, ssn in users:
        encrypted_ssn = encrypt_data(ssn, fernet)
        cursor.execute(
            'INSERT INTO users (username, role, encrypted_ssn) VALUES (?, ?, ?)',
            (username, role, encrypted_ssn)
        )
    
    conn.commit()
    conn.close()
    print("Secure database created with encrypted SSN fields.")


def get_user_secure(username, fernet):
    """SECURE: Uses parameterized queries and decrypts sensitive data."""
    conn = sqlite3.connect('secure_users.db')
    cursor = conn.cursor()
    
    cursor.execute(
        'SELECT id, username, role, encrypted_ssn FROM users WHERE username = ?',
        (username,)
    )
    row = cursor.fetchone()
    conn.close()
    
    if row:
        return {
            'id': row[0],
            'username': row[1],
            'role': row[2],
            'ssn': decrypt_data(row[3], fernet)
        }
    return None


def validate_input(username):
    """Secondary defense: validate input format."""
    if not username:
        return False
    if len(username) > 50:
        return False
    if not username.isalnum():
        return False
    return True


if __name__ == '__main__':
    key = load_encryption_key()
    fernet = Fernet(key)
    
    setup_secure_database(fernet)
    
    print("\n--- Normal query (secure) ---")
    user = get_user_secure('alice', fernet)
    print(f"User: {user}")
    
    print("\n--- SQL Injection attempt (secure) ---")
    malicious_input = "' OR '1'='1' --"
    user = get_user_secure(malicious_input, fernet)
    print(f"Result: {user}")
    print("Attack blocked! Returned None for invalid input.")
    
    print("\n--- Input validation ---")
    test_inputs = ["alice", "' OR '1'='1", "a" * 100, ""]
    for test in test_inputs:
        valid = validate_input(test)
        print(f"  '{test[:30]}...' -> {'VALID' if valid else 'REJECTED'}")

Your data is now encrypted at rest. Next up, you'll write a dedicated test script that throws multiple attack payloads at your defenses and inspects the raw database to confirm nothing is stored in plain text.

Verify Your Defenses

Your secure app now blocks SQL injection with parameterized queries and encrypts sensitive data with Fernet. But how do you know it actually works against real attack patterns?

In this step, you will write a dedicated test script that throws multiple injection payloads at your app, reads the raw database to confirm nothing is stored in plaintext, and verifies that a wrong encryption key cannot access the data. These three tests give you concrete proof that your defenses hold.

In this step, get ready to:
  • Write a test script that attempts multiple SQL injection payloads.
  • Inspect the raw database file to confirm SSN values are encrypted.
  • Test that a wrong encryption key fails to decrypt the data.
Write the SQL injection test function

A single test with one payload is not enough. Real attackers try dozens of variations. Your test function will fire six common injection payloads at get_user_secure and confirm every single one returns None.

  • Create a new file called test_security.py in your project folder.
  • Add the imports and the SQL injection test function by pasting this code:
import sqlite3
import os
from cryptography.fernet import Fernet
from dotenv import load_dotenv
from secure_app import (
    load_encryption_key, encrypt_data, decrypt_data,
    setup_secure_database, get_user_secure, validate_input
)


def test_sql_injection_payloads(fernet):
    """Test multiple SQL injection payloads against the secure app."""
    payloads = [
        "' OR '1'='1' --",
        "'; DROP TABLE users; --",
        "' UNION SELECT * FROM users --",
        "admin'--",
        "1; SELECT * FROM users",
        "' OR 1=1 --",
    ]
    
    print("=== SQL Injection Tests ===")
    all_passed = True
    for payload in payloads:
        result = get_user_secure(payload, fernet)
        status = "BLOCKED" if result is None else "FAILED"
        if result is not None:
            all_passed = False
        print(f"  [{status}] Payload: {payload[:40]}")
    
    if all_passed:
        print("  All injection attempts blocked!\n")
    else:
        print("  WARNING: Some attacks succeeded!\n")
    return all_passed

What does this code do?

The imports pull in the functions you built in secure_app.py so you can test them directly without duplicating code.

The test_sql_injection_payloads function loops through six common attack strings. For each one, it calls get_user_secure and checks whether the result is None. If any payload returns actual data, the test fails.

  • Save test_security.py.
Add the encryption-at-rest test

Blocking injection is only half the battle. If someone copies your database file, they should see gibberish instead of social security numbers. This test reads the raw database and confirms nothing looks like a plaintext SSN.

  • In test_security.py, add the following function below test_sql_injection_payloads:
def test_encryption_at_rest():
    """Verify that SSN values are encrypted in the database file."""
    print("=== Encryption at Rest Test ===")
    conn = sqlite3.connect('secure_users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT username, encrypted_ssn FROM users')
    rows = cursor.fetchall()
    conn.close()
    
    all_encrypted = True
    for username, encrypted_ssn in rows:
        # A plaintext SSN has exactly 11 characters with dashes
        is_plaintext = '-' in encrypted_ssn and len(encrypted_ssn) == 11
        if is_plaintext:
            all_encrypted = False
            print(f"  [FAILED] {username}: SSN stored in plaintext!")
        else:
            print(f"  [PASSED] {username}: SSN is encrypted ({encrypted_ssn[:20]}...)")
    
    if all_encrypted:
        print("  All sensitive data encrypted at rest!\n")
    return all_encrypted

What does this code do?

This function opens secure_users.db directly with SQLite and reads the raw encrypted_ssn column. It does not decrypt anything.

It then checks whether each value looks like a plaintext SSN (11 characters with dashes). If the value is a long encrypted string instead, the test passes. This proves that even with direct database access, the data is unreadable.

  • Save test_security.py.
Add the wrong key test and run all tests

Your final test checks a critical property of encryption. If someone gets the database but not the key, they still cannot read the data. This function generates a completely new Fernet key and tries to decrypt a stored value with it.

  • In test_security.py, add the wrong key test function below test_encryption_at_rest:
def test_wrong_key_fails():
    """Verify that a wrong key cannot decrypt the data."""
    print("=== Wrong Key Test ===")
    # Generate a completely different key
    wrong_key = Fernet.generate_key()
    wrong_fernet = Fernet(wrong_key)
    
    # Read an encrypted value from the database
    conn = sqlite3.connect('secure_users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT encrypted_ssn FROM users LIMIT 1')
    encrypted_ssn = cursor.fetchone()[0]
    conn.close()
    
    # Try to decrypt with the wrong key
    try:
        wrong_fernet.decrypt(encrypted_ssn.encode())
        print("  [FAILED] Wrong key was able to decrypt data!")
        return False
    except Exception:
        print("  [PASSED] Wrong key cannot decrypt data.")
        print("  Decryption correctly fails with invalid key!\n")
        return True

What does this code do?

This function creates a brand new Fernet key that has never seen your data. It then reads one encrypted SSN from the database and attempts to decrypt it with the wrong key.

Fernet will raise an exception because the wrong key cannot verify the HMAC signature. The try/except block catches this and reports a pass. If decryption somehow succeeds, something is seriously broken.

  • Save test_security.py.

Now add the main block that ties all three tests together and prints a final score.

  • At the bottom of test_security.py, add the main block:
if __name__ == '__main__':
    key = load_encryption_key()
    fernet = Fernet(key)
    
    # Rebuild the database so tests run from a known state
    setup_secure_database(fernet)
    
    # Run all three security tests
    injection_passed = test_sql_injection_payloads(fernet)
    encryption_passed = test_encryption_at_rest()
    wrong_key_passed = test_wrong_key_fails()
    
    # Print the final scorecard
    print("=== Final Results ===")
    total = sum([injection_passed, encryption_passed, wrong_key_passed])
    print(f"  {total}/3 security tests passed.")
    
    if total == 3:
        print("  Your database is secure!")
    else:
        print("  Some tests failed. Review your implementation.")

What does this main block do?

The main block loads your encryption key, rebuilds the database from scratch (so every test run starts clean), then calls all three test functions in sequence.

Each function returns True or False. The final scorecard sums them up and prints whether your database passed all checks.

  • Save test_security.py.
  • Run the test script by running this command:
python3 test_security.py

You should see all six injection payloads marked as BLOCKED, all three users' SSNs marked as encrypted, and the wrong key test passing. The final line should read 3/3 security tests passed. followed by Your database is secure!.

Seeing an import error?

Make sure you are running the command from the same folder that contains both test_security.py and secure_app.py. The import statement expects both files to be in the same directory.

Also confirm your virtual environment is still active. You should see (venv) at the start of your terminal prompt.

Why is my test_security.py import failing?

✔️ Awesome, I've got everything!

Great. Double check you have saved test_security.py and that all 3 tests passed.

ⓧ I'd like to double check the full code

Here is the complete test_security.py file. Compare it to yours and fix any differences.

import sqlite3
import os
from cryptography.fernet import Fernet
from dotenv import load_dotenv
from secure_app import (
    load_encryption_key, encrypt_data, decrypt_data,
    setup_secure_database, get_user_secure, validate_input
)


def test_sql_injection_payloads(fernet):
    """Test multiple SQL injection payloads against the secure app."""
    payloads = [
        "' OR '1'='1' --",
        "'; DROP TABLE users; --",
        "' UNION SELECT * FROM users --",
        "admin'--",
        "1; SELECT * FROM users",
        "' OR 1=1 --",
    ]
    
    print("=== SQL Injection Tests ===")
    all_passed = True
    for payload in payloads:
        result = get_user_secure(payload, fernet)
        status = "BLOCKED" if result is None else "FAILED"
        if result is not None:
            all_passed = False
        print(f"  [{status}] Payload: {payload[:40]}")
    
    if all_passed:
        print("  All injection attempts blocked!\n")
    else:
        print("  WARNING: Some attacks succeeded!\n")
    return all_passed


def test_encryption_at_rest():
    """Verify that SSN values are encrypted in the database file."""
    print("=== Encryption at Rest Test ===")
    conn = sqlite3.connect('secure_users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT username, encrypted_ssn FROM users')
    rows = cursor.fetchall()
    conn.close()
    
    all_encrypted = True
    for username, encrypted_ssn in rows:
        is_plaintext = '-' in encrypted_ssn and len(encrypted_ssn) == 11
        if is_plaintext:
            all_encrypted = False
            print(f"  [FAILED] {username}: SSN stored in plaintext!")
        else:
            print(f"  [PASSED] {username}: SSN is encrypted ({encrypted_ssn[:20]}...)")
    
    if all_encrypted:
        print("  All sensitive data encrypted at rest!\n")
    return all_encrypted


def test_wrong_key_fails():
    """Verify that a wrong key cannot decrypt the data."""
    print("=== Wrong Key Test ===")
    wrong_key = Fernet.generate_key()
    wrong_fernet = Fernet(wrong_key)
    
    conn = sqlite3.connect('secure_users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT encrypted_ssn FROM users LIMIT 1')
    encrypted_ssn = cursor.fetchone()[0]
    conn.close()
    
    try:
        wrong_fernet.decrypt(encrypted_ssn.encode())
        print("  [FAILED] Wrong key was able to decrypt data!")
        return False
    except Exception:
        print("  [PASSED] Wrong key cannot decrypt data.")
        print("  Decryption correctly fails with invalid key!\n")
        return True


if __name__ == '__main__':
    key = load_encryption_key()
    fernet = Fernet(key)
    
    setup_secure_database(fernet)
    
    injection_passed = test_sql_injection_payloads(fernet)
    encryption_passed = test_encryption_at_rest()
    wrong_key_passed = test_wrong_key_fails()
    
    print("=== Final Results ===")
    total = sum([injection_passed, encryption_passed, wrong_key_passed])
    print(f"  {total}/3 security tests passed.")
    
    if total == 3:
        print("  Your database is secure!")
    else:
        print("  Some tests failed. Review your implementation.")

You now have automated proof that your defenses work. These tests can run any time you change the code, catching regressions before they reach production. Next up is a challenge that takes your security even further.

Secret mission

Add Password Hashing and a Login System

Your database is hardened against injection and your sensitive fields are encrypted. But what about the login form itself? In this secret mission, you will build a login system that hashes passwords with bcrypt, resists SQL injection on authentication queries, and locks accounts after repeated failed attempts.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • Project folder with Python files (vulnerable_app.py, secure_app.py, test_security.py, .env, .gitignore).
  • Virtual environment (venv/ folder).
  • SQLite database files (users.db, secure_users.db).

Keep everything running

No action needed. Choose this if you're still actively building or want to revisit the project later.

  • Your project folder stays on your Desktop with all files intact.
  • The virtual environment stays ready to activate whenever you return.
  • The SQLite database files remain in the project folder.

Pause - I'll come back to this later

Deactivate your virtual environment to free up your terminal session. Your files remain on disk for when you come back.

  • Deactivate your virtual environment by running:
deactivate
  • When you return, navigate back to your project folder and reactivate with source venv/bin/activate.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes your project folder and everything inside it.

  • Deactivate your virtual environment first (if active):
deactivate
  • Delete the entire project folder by running:
cd ~/Desktop
rm -rf secure-database

On Windows, use this command instead:

cd ~\Desktop
rmdir /s /q secure-database
  • This removes all Python files, the virtual environment, the .env file with your encryption key, and both SQLite database files.

That's a Wrap!

That's a Wrap!

Nice work! You have built a secure database application in Python that defends against real-world attacks.

You now have hands-on proof that your defenses hold up.

You've learned how to:

  • Exploit a SQL injection vulnerability and then defend against it using parameterized queries.
  • Encrypt sensitive data at rest using Fernet symmetric encryption with proper key management through environment variables.
  • Write a security test suite that verifies your defenses against multiple attack payloads.
  • Added password hashing with bcrypt and brute-force protection to a login system.

Ready to quiz yourself?