Skip to content

A Practical Guide to SQL Injection

Web applications use Structured Query Language (SQL) to read and change data. SQL injection occurs when an application lets untrusted input alter the structure of a SQL command. An attacker may then bypass authentication, read private records, or change stored data.

This guide shows how the vulnerability works, introduces its common forms, and explains how parameterized queries keep user input separate from executable SQL.

Navigate this post

What is SQL Injection?

SQL Injection (SQLi) is a security vulnerability that occurs when untrusted user input is directly concatenated into a Structured Query Language database command. Database systems execute SQL commands to retrieve, insert, or modify stored information. When an application fails to separate data from code, an attacker can manipulate the query structure itself.

SQL

Structured Query Language, the standard programming language used to manage and query relational databases.

Relational Database

A structured collection of data organized into tables with rows and columns, managed by systems such as SQLite, PostgreSQL, or MySQL.

Database Query

A specific SQL request sent to the database to perform actions like searching, updating, or deleting records.

Parameterized Query

A database query technique that separates SQL code execution from user input parameters, ensuring inputs are treated strictly as data.

Consider an everyday online form like a login box or search bar. When a user submits information, the web application generates a SQL query to check credentials or fetch records. Under normal operation, the database receives expected data values. However, if the input includes SQL syntax characters like single quotes, string delimiters, or comment markers, the database engine interprets those characters as active code rather than passive text.

Core Security Principle

The primary root cause of SQL Injection is the failure to maintain a clear boundary between executable code and user-supplied data. Treating user inputs as executable code opens direct access to backend storage.

How SQL Injection Works

To understand the mechanics of SQL Injection, examine how a standard web application processes user authentication credentials. Suppose a web service checks a username and password against a users table.

Vulnerable Query Construction

When code constructs SQL statements using string formatting or direct string concatenation, user input becomes part of the command structure.

vulnerable_login.py
def authenticate_user(db_cursor, username_input, password_input):
    # Vulnerable direct string formatting
    query = f"SELECT * FROM users WHERE username = '{username_input}' AND password = '{password_input}'"  # (1)
    db_cursor.execute(query)  # (2)
    return db_cursor.fetchone()
  1. Concatenates untrusted input directly into the query string without escaping or parameterization.
  2. Executes the concatenated SQL string directly against the relational database engine.

If an attacker enters ' OR 1=1 -- in the username_input field, the string formatting produces this query in databases where -- starts a comment:

executed_vulnerable_query.sql
SELECT * FROM users WHERE username = '' OR 1=1 -- ' AND password = 'password123'

The injected condition is true for every row, and the comment marker hides the password check. fetchone() may therefore authenticate whichever row the database returns first; row order is not guaranteed unless the query specifies it.

Secure Parameterized Query Construction

Parameterized queries fix this flaw by defining the query structure with placeholders. The database API sends the statement and parameter values separately, so the values are handled as data rather than SQL syntax.

secure_login.py
def authenticate_user_secure(db_cursor, username_input, password_input):
    # Secure parameterized query placeholder syntax
    query = "SELECT * FROM users WHERE username = ? AND password = ?"  # (1)
    db_cursor.execute(query, (username_input, password_input))  # (2)
    return db_cursor.fetchone()
  1. Defines placeholders (?) for user inputs, establishing a fixed query structure before evaluation.
  2. Passes input parameters as a tuple separate from the SQL command, instructing the engine to treat inputs as literal text.

When the secure function receives ' OR 1=1 --, it searches for a literal username containing those characters. The input does not alter the query logic.

---
title: "Comparison of Vulnerable vs Parameterized Execution Flow"
---
flowchart TB
    accTitle: Vulnerable and parameterized SQL execution flows
    accDescr: String concatenation can turn input into executable SQL, while a parameterized query keeps input as literal data.
    U["User Input Received"] --> C{"Processing Method"}

    C -->|"String Concatenation"| V1["Input Merged into SQL Code"]
    V1 --> V2["Database Compiles Merged String"]
    V2 --> V3["Malicious Syntax Executed"]

    C -->|"Parameterized Query"| S1["Query Structure Kept Separate"]
    S1 --> S2["Input Bound as Literal Data"]
    S2 --> S3["Query Structure Unchanged"]

The workflow above illustrates why parameterization neutralizes this attack: user input never becomes part of the SQL structure.

Types of SQL Injection Attacks

SQL injection attacks are categorized based on how the attacker receives response data from the vulnerable database:

In-band (Direct) Attacks

The attacker sends malicious SQL and receives results or error messages directly on the web page or HTTP response.

Inferential (Blind) Attacks

The attacker cannot see database outputs directly on screen, but infers database contents byte-by-byte by observing true/false application behavior or response time delays.

Out-of-band Attacks

The attacker uses specialized database commands to force the server to transmit extracted data directly to an external server under the attacker's control.

The table below summarizes five common attack techniques:

Attack Category Attack Type Execution Mechanism Impact and Visibility
In-band (Direct) Error-based Triggers detailed database error messages containing internal table and column names. Immediate error output visible in web response.
In-band (Direct) Union-based Appends results from secondary tables using the UNION SQL operator. Extracted records returned directly in application output.
Inferential (Blind) Boolean-based Tests true/false logical conditions to guess database letters and numbers one by one. Indirect verification via changes in page layout or status codes.
Inferential (Blind) Time-based Forces the database to pause (e.g., using SLEEP()) if a guessed letter is correct. Verified by measuring response delays in seconds.
Out-of-band DNS / HTTP Exfiltration Triggers DNS or HTTP requests from the database server to an external listener. Data exfiltrated outside the standard application web loop.

Direct attacks provide immediate feedback because query results display on screen. Inferential (blind) attacks require more automated requests but succeed even when applications hide database errors.

Risk of Error-based Output

Exposing raw database error messages in production environments provides attackers with internal table names, column structures, and SQL syntax errors. Applications should display generic error messages while logging details securely server-side.

Preventing SQL Injection

Defending web applications against SQL Injection requires defensive layers, with parameterized queries acting as the primary line of protection.

Primary Defense: Prepared Statements

Prepared statements enforce parameterization at the database driver level. All modern programming languages and framework drivers support prepared statements.

sqlite_parameterized.py
import sqlite3

def fetch_product_by_category(db_connection, category_name):
    cursor = db_connection.cursor()
    # Safe query using named parameters
    query = "SELECT id, name, price FROM products WHERE category = :cat AND in_stock = 1"
    cursor.execute(query, {"cat": category_name})
    return cursor.fetchall()

Secondary Defenses

While parameterization solves the core vulnerability, implementing secondary defense mechanisms strengthens overall system resilience.

  1. Object-Relational Mapping (ORM) Frameworks: Modern ORMs like SQLAlchemy or Django ORM generate parameterized SQL queries automatically when using standard query APIs.
  2. Principle of Least Privilege: Restrict database user permissions so that web application connections can only access necessary tables and cannot perform administrative actions like DROP TABLE or database reconfigurations.
  3. Input Validation and Typing: Validate input formats (such as ensuring numeric IDs contain only integers) before handling parameters.
Go deeper on secure coding practices

Interactive Browser Demo

To help developers understand SQL Injection risks and testing methods hands-on, an interactive demonstration environment is available.

The project provides interactive code notebooks that execute real SQLite database queries directly inside your web browser using Pyodide and WebAssembly - no installation or local database setup required.

  • Interactive SQL Queries
    Try vulnerable and parameterized queries live in your browser and inspect instant execution results.

  • Step-by-Step Defense Tutorials
    Learn how to rewrite vulnerable string concatenations into safe parameterized statements using Python code.

  • Zero Setup Required
    Runs entirely on client-side WebAssembly, making it accessible from any modern web browser immediately.

  • Open-Source Repository
    Inspect full source code, clone locally, or contribute improvements directly on GitHub.

Explore the full demo code and runnable notebooks on GitHub:

Interactive SQL Injection Demo Repository

Conclusion

SQL Injection remains one of the most prevalent web security vulnerabilities due to unsafe string concatenation during query construction. By separating executable SQL logic from user-supplied data using parameterized queries and prepared statements, applications completely eliminate SQL Injection risks. Combining parameterization with least-privilege database permissions and structured ORM frameworks ensures long-term database security and application reliability.

References and further reading

Open the complete reference catalog

Primary Sources