SQLite - Injection

SQLite - Preventing SQL Injection

SQL Injection is a critical security vulnerability that occurs when an application incorporates untrusted user input directly into its SQL queries. A malicious user can supply specially crafted input that breaks out of the data context and modifies the SQL query itself, potentially leading to data theft, unauthorized access, data corruption, or full system compromise.

This chapter will teach you the modern, standard way to prevent this attack. The fundamental rule is: Never trust user input and never format queries by concatenating strings with user-provided data.

A Vulnerable Application Example

Let’s imagine a simple Python script that looks up a user from a database. A common but dangerously flawed approach is using f-strings or string concatenation to build the query:

# WARNING: This code is VULNERABLE to SQL Injection. DO NOT USE.
import sqlite3

# User input (e.g., from a web form)
username_input = "' OR '1'='1' --"

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# The vulnerability is here: embedding user input directly into the query string.
query = f"SELECT * FROM users WHERE username = '{username_input}' AND active = 1;"

print(f"Executing: {query}")
# Executing: SELECT * FROM users WHERE username = '' OR '1'='1' --' AND active = 1;

cursor.execute(query)
results = cursor.fetchall()

print("Results:", results) # This will likely return all users in the table!

In the example above, the attacker provides the input ' OR '1'='1' --. When this is inserted into the query, the WHERE clause becomes WHERE username = '' OR '1'='1' -- .... The '1'='1' condition is always true, and the -- starts a comment, causing the rest of the original query to be ignored. The attacker has successfully bypassed the login check.

The Solution: Parameterized Queries

The correct and industry-standard method to prevent SQL injection is to use parameterized queries (also known as prepared statements). This technique separates the SQL command from the user-supplied data.

With parameterization, you write the SQL query with placeholders (? in SQLite) for the data. You then provide the actual user data as a separate argument to the execution function. The database driver ensures that the data is treated strictly as a value, not as executable code.

Secure Example (Python)

Here is the safe, corrected version of the previous script using Python’s sqlite3 library:

# This is the CORRECT and SECURE way to execute queries.
import sqlite3

username_input = "David" # Normal input
# username_input = "' OR '1'='1' --" # Malicious input

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 1. The SQL query uses a placeholder (?) for the variable.
query = "SELECT * FROM users WHERE username = ? AND active = 1;"

# 2. The user input is passed as a separate tuple to the execute method.
# The library handles safe substitution.
cursor.execute(query, (username_input,))

results = cursor.fetchall()

print("Results:", results) # With malicious input, this will safely find no user and return [].

When the malicious input is provided to the parameterized query, the database looks for a user whose name is the literal string ' OR '1'='1' --. Since no user has this name, the query correctly returns no results, and the attack is thwarted.

Secure Example (Modern PHP with PDO)

The same principle applies to other languages. Here’s how to do it in modern PHP using PDO (PHP Data Objects):

<?php
// This is the CORRECT and SECURE way in modern PHP.

$userInput = "' OR '1'='1' --"; // Malicious input

try {
    $pdo = new PDO('sqlite:example.db');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 1. Prepare the statement with a placeholder.
    $stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND active = 1;');

    // 2. Bind the user input to the placeholder and execute.
    $stmt->execute([':username' => $userInput]);

    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    print_r($results); // Will correctly return an empty array.

} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
?>

Deprecated and Insecure Methods

Older tutorials might mention functions like sqlite_escape_string() or concepts like magic_quotes_gpc in PHP. These are deprecated and should not be used.

Manually escaping input is error-prone and often incomplete, leaving security holes. Blocklisting or allowlisting characters is also a flawed strategy that can be bypassed by clever attackers. Always use parameterized queries provided by your database driver. It is the simplest and most robust defense against SQL injection.