Eric Campbell 6 min read Security

SQL Injection: Parameterized Queries and Prevention

SQL injection has been a known vulnerability since 1998. Twenty-five years later, it still appears in the OWASP Top 10 and accounts for a significant percentage of web application breaches. The 2023...

Key Insights

  • SQL injection remains in the OWASP Top 10 because developers still concatenate user input directly into queries—parameterized queries eliminate this class of vulnerability entirely by separating code from data.
  • Defense in depth matters: parameterized queries are necessary but not sufficient. Combine them with input validation, least-privilege database accounts, and proper ORM usage.
  • Every raw SQL query in your codebase is a potential attack vector. Treat string concatenation in database queries as a code smell that demands immediate refactoring.

The Persistent Threat

SQL injection has been a known vulnerability since 1998. Twenty-five years later, it still appears in the OWASP Top 10 and accounts for a significant percentage of web application breaches. The 2023 MOVEit breach, which affected over 2,000 organizations and 60 million individuals, exploited a SQL injection vulnerability. Heartland Payment Systems lost 130 million credit card numbers to SQL injection in 2008. These aren’t obscure edge cases—they’re preventable disasters.

The persistence of SQL injection isn’t a technology problem. We’ve had effective defenses for decades. It’s a human problem: developers under deadline pressure, legacy codebases, and insufficient security training. This article will arm you with the knowledge to eliminate SQL injection from your applications.

Anatomy of a SQL Injection Attack

SQL injection occurs when untrusted data is sent to an interpreter as part of a command. The attacker’s input changes the intended SQL query structure, allowing them to read, modify, or delete data they shouldn’t access.

Consider this vulnerable login implementation:

# VULNERABLE - Never do this
def authenticate_user(username, password):
    query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
    cursor.execute(query)
    return cursor.fetchone()

When a legitimate user enters alice and secret123, the query becomes:

SELECT * FROM users WHERE username = 'alice' AND password = 'secret123'

But an attacker entering ' OR '1'='1' -- as the username produces:

SELECT * FROM users WHERE username = '' OR '1'='1' --' AND password = 'anything'

The -- comments out the password check. The '1'='1' condition is always true. The attacker gains access without valid credentials.

Common injection points include login forms, search fields, URL parameters, cookies, HTTP headers, and any input that reaches a database query. If user-controlled data touches your SQL, it’s a potential attack vector.

Types of SQL Injection

Classic/In-band Injection returns results directly in the application response. The attacker sees query results immediately, making data extraction straightforward. The login bypass above is a classic example.

Blind Injection occurs when the application doesn’t display query results but behaves differently based on query success. Boolean-based blind injection infers data one bit at a time:

-- Attacker tests if first character of admin password is 'a'
SELECT * FROM products WHERE id = 1 AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin') = 'a'

If the product displays normally, the first character is ‘a’. If not, try ‘b’. Repeat for each character. Time-based blind injection uses delays:

-- If admin exists, wait 5 seconds
SELECT * FROM products WHERE id = 1; IF (SELECT COUNT(*) FROM users WHERE username='admin') > 0 WAITFOR DELAY '00:00:05'

Out-of-band Injection exfiltrates data through DNS lookups, HTTP requests, or other channels when in-band methods fail. This requires specific database features and network configurations but bypasses many detection mechanisms.

Parameterized Queries: The Primary Defense

Parameterized queries (prepared statements) separate SQL code from data. The database driver handles escaping and type conversion, making injection structurally impossible. The query structure is fixed; user input can only fill predefined parameter slots.

Here’s the vulnerable login rewritten safely across multiple languages:

Python with psycopg2:

def authenticate_user(username, password):
    query = "SELECT * FROM users WHERE username = %s AND password = %s"
    cursor.execute(query, (username, password))
    return cursor.fetchone()

Node.js with pg:

async function authenticateUser(username, password) {
  const query = 'SELECT * FROM users WHERE username = $1 AND password = $2';
  const result = await pool.query(query, [username, password]);
  return result.rows[0];
}

Java with PreparedStatement:

public User authenticateUser(String username, String password) throws SQLException {
    String query = "SELECT * FROM users WHERE username = ? AND password = ?";
    try (PreparedStatement stmt = connection.prepareStatement(query)) {
        stmt.setString(1, username);
        stmt.setString(2, password);
        ResultSet rs = stmt.executeQuery();
        return rs.next() ? mapUser(rs) : null;
    }
}

C# with SqlCommand:

public User AuthenticateUser(string username, string password)
{
    const string query = "SELECT * FROM Users WHERE Username = @username AND Password = @password";
    using var command = new SqlCommand(query, connection);
    command.Parameters.AddWithValue("@username", username);
    command.Parameters.AddWithValue("@password", password);
    using var reader = command.ExecuteReader();
    return reader.Read() ? MapUser(reader) : null;
}

In each case, the malicious input ' OR '1'='1' -- is treated as a literal string value, not SQL code. The query searches for a user with that exact username—and finds none.

Defense in Depth: Additional Prevention Layers

Parameterized queries are essential but shouldn’t be your only defense. Security-conscious applications layer multiple protections.

Input Validation and Allowlisting rejects obviously malicious input before it reaches the database:

import re

def validate_username(username):
    # Allowlist: only alphanumeric and underscore, 3-20 characters
    if not re.match(r'^[a-zA-Z0-9_]{3,20}$', username):
        raise ValueError("Invalid username format")
    return username

def authenticate_user(username, password):
    validated_username = validate_username(username)
    query = "SELECT * FROM users WHERE username = %s AND password = %s"
    cursor.execute(query, (validated_username, password))
    return cursor.fetchone()

Validation doesn’t replace parameterization—it complements it. Defense in depth means an attacker must bypass multiple controls.

Stored Procedures can provide an additional layer but aren’t a silver bullet. They’re vulnerable if they concatenate parameters internally:

-- VULNERABLE stored procedure
CREATE PROCEDURE SearchProducts @SearchTerm NVARCHAR(100)
AS
BEGIN
    EXEC('SELECT * FROM Products WHERE Name LIKE ''%' + @SearchTerm + '%''')
END

Use parameterized calls within stored procedures too.

Least Privilege Database Accounts limit damage when attacks succeed. Your web application shouldn’t connect as sa or root. Create dedicated accounts with minimal permissions:

-- Create a restricted application user
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT SELECT, INSERT, UPDATE ON users TO app_user;
GRANT SELECT ON products TO app_user;
-- No DELETE, no access to admin tables, no schema modification

ORMs generally use parameterized queries by default, but raw query methods remain dangerous:

# Safe - ORM handles parameterization
user = session.query(User).filter(User.username == username).first()

# VULNERABLE - raw query with string formatting
user = session.execute(f"SELECT * FROM users WHERE username = '{username}'").first()

# Safe - raw query with parameters
user = session.execute(
    text("SELECT * FROM users WHERE username = :username"),
    {"username": username}
).first()

Audit every raw query in your codebase. They exist for legitimate reasons but demand extra scrutiny.

Testing and Detection

Manual Testing starts with single quotes. Enter ' in form fields and URL parameters. If you see database errors, you’ve found a potential vulnerability. Try these payloads:

  • ' OR '1'='1
  • '; DROP TABLE users; --
  • 1 UNION SELECT NULL, NULL, NULL --

Automated Tools scale testing across your application. SQLMap is the industry standard for exploitation testing:

# Test a URL parameter for injection
sqlmap -u "https://example.com/products?id=1" --batch --level=3

# Test a POST form
sqlmap -u "https://example.com/login" --data="username=test&password=test" --batch

Burp Suite’s scanner identifies injection points during penetration testing. OWASP ZAP provides free automated scanning.

Code Review Red Flags demand immediate attention:

# Red flag: String concatenation or formatting in queries
query = "SELECT * FROM users WHERE id = " + user_id
query = f"SELECT * FROM users WHERE id = {user_id}"
query = "SELECT * FROM users WHERE id = %s" % user_id

# Red flag: Dynamic table or column names
query = f"SELECT * FROM {table_name} WHERE {column_name} = %s"

Write automated tests that attempt injection:

import pytest

class TestSQLInjection:
    injection_payloads = [
        "' OR '1'='1",
        "'; DROP TABLE users; --",
        "1 UNION SELECT NULL--",
        "1' AND SLEEP(5)--",
    ]
    
    def test_login_injection_resistance(self, client):
        for payload in self.injection_payloads:
            response = client.post('/login', data={
                'username': payload,
                'password': payload
            })
            # Should not authenticate with injection payloads
            assert response.status_code != 200 or 'Welcome' not in response.text
            # Should not reveal database errors
            assert 'SQL' not in response.text
            assert 'syntax' not in response.text.lower()

Building a Security-First Mindset

SQL injection is a solved problem. The solutions have existed for over two decades. Yet breaches continue because security isn’t prioritized during development.

Your checklist:

  1. Use parameterized queries for every database interaction—no exceptions
  2. Validate and sanitize all user input at application boundaries
  3. Run database connections with least-privilege accounts
  4. Audit raw SQL queries in ORM-based applications
  5. Include SQL injection tests in your CI/CD pipeline
  6. Train your team to recognize and prevent injection vulnerabilities

Treat every string concatenation in a database query as a bug. Make parameterized queries the default, the habit, the only acceptable approach. The next breach doesn’t have to be yours.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.