Python: Prevent SQL Injection with Best Practices

The main purpose of parameterized queries is to separate SQL code from data. This technique prevents attackers from inserting malicious code into the SQL query. In a parameterized query we need to get the parameter value from the user and execute the query. 

In the Python code below, if we look into the “get_user_by_username” function, it takes a single parameter called “user_name.” The function has a parameterized SQL query called “query” to safely retrieve user data by username by using a placeholder (?) for user input. By passing user_input as a separate argument, it prevents SQL injection by ensuring the input is treated as data rather than executable code.

Python Code

import sqlite3

#Function to create a sample database and table (for demonstration)
def create_database():
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL,
            password TEXT NOT NULL
        )
    ''')
    # Insert sample data
    cursor.execute("INSERT INTO users (username, password) VALUES (?, ?)", ("admin", "admin_pass"))
    cursor.execute("INSERT INTO users (username, password) VALUES (?, ?)", ("user", "user_pass"))
    conn.commit()
    conn.close()

# Function to query users safely using parameterized queries
def get_user_by_username(user_input):
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
   
    # Parameterized query to prevent SQL injection
    query = "SELECT * FROM users WHERE username = ?"
    cursor.execute(query, (user_input,))
   
    # Fetch and return results
    results = cursor.fetchall()
    conn.close()
    return results
   
create_database()
user_input = input("Enter username to search: ")

# Retrieve user by username
users = get_user_by_username(user_input)

if users:
    for user in users:
        print(f"ID: {user[0]}, Username: {user[1]}, Password: {user[2]}")
else:
    print("No user found.")