SQL Injection Prevention: Parameterized Queries Guide

What is SQL Injection?

SQL injection is one of the oldest, most well-known, and most dangerous classes of web application vulnerabilities. It occurs when an attacker is able to insert or "inject" malicious SQL code into a query that the application sends to its database. The root cause is always the same: user-supplied input is concatenated directly into a SQL string without proper sanitization or parameterization.

Despite decades of awareness, SQL injection consistently ranks in the OWASP Top 10 and continues to be exploited in real-world breaches. The reason is simple: it is easy to introduce when developers build queries by string concatenation, and it is invisible until exploited. A single vulnerable endpoint can expose an entire database, including user credentials, personal data, financial records, and administrative secrets.

How SQL Injection Works

Consider a login form that takes a username and password, and the application constructs a SQL query by string interpolation:

// VULNERABLE - Never do this
const query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

If a user enters a normal username like alice and password secret123, the query becomes:

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

But if an attacker enters ' OR '1'='1 as the password, the query becomes:

SELECT * FROM users
WHERE username = 'alice'
  AND password = '' OR '1'='1';

Since '1'='1' is always true, this query returns all rows in the users table. The attacker bypasses authentication entirely. More destructive payloads can extract data, modify records, or even execute administrative operations like dropping tables.

Types of SQL Injection

• Classic (in-band) injection: The attacker receives the results of the injected query directly in the application's response. This is the most straightforward form and the example above demonstrates it.
• Blind injection: The application does not display query results, but the attacker can infer information based on the application's behavior (e.g., different error messages, response times, or boolean true/false responses).
• Out-of-band injection: The attacker uses database features to send data to an external server they control (e.g., using xp_cmdshell in SQL Server or UTL_HTTP in Oracle).
• Second-order injection: The malicious payload is stored in the database first (e.g., in a username field), then executed later when it is retrieved and used in a different query without sanitization.

The Solution: Parameterized Queries

Parameterized queries (also called prepared statements) are the primary defense against SQL injection. Instead of concatenating user input into the SQL string, you define the query structure with placeholder parameters and pass the values separately. The database driver handles escaping and quoting automatically, making it impossible for user input to alter the query structure.

The key principle: the SQL query structure is fixed at development time, and user input is always treated as data, never as code. No amount of clever input can break out of a properly parameterized query.

Parameterized Queries by Language

Node.js (PostgreSQL with pg)

// SAFE - Parameterized query
const { rows } = await pool.query(
  'SELECT * FROM users WHERE username = $1 AND password = $2',
  [username, password]
);

// Also safe with named parameters (using pg-named)
const { rows } = await pool.query(
  'SELECT * FROM users WHERE username = :username AND active = :active',
  { username: 'alice', active: true }
);

Node.js (MySQL with mysql2)

// SAFE - Parameterized query
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE username = ? AND password = ?',
  [username, password]
);

Python (psycopg2 for PostgreSQL)

# SAFE - Parameterized query
cursor.execute(
    "SELECT * FROM users WHERE username = %s AND password = %s",
    (username, password)
)
rows = cursor.fetchall()

# Also safe with named parameters
cursor.execute(
    "SELECT * FROM users WHERE username = %(user)s AND active = %(active)s",
    {"user": "alice", "active": True}
)

Python (SQLite3)

# SAFE - Parameterized query
cursor.execute(
    "SELECT * FROM users WHERE username = ? AND password = ?",
    (username, password)
)
rows = cursor.fetchall()

Java (JDBC PreparedStatement)

// SAFE - PreparedStatement with parameterized query
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
    stmt.setString(1, username);
    stmt.setString(2, password);
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
        // process row
    }
}

Go (database/sql)

// SAFE - Parameterized query
rows, err := db.Query(
    "SELECT * FROM users WHERE username = $1 AND password = $2",
    username, password,
)
if err != nil {
    return fmt.Errorf("query failed: %w", err)
}
defer rows.Close()

for rows.Next() {
    // process row
}

PHP (PDO)

// SAFE - PDO prepared statement
$stmt = $pdo->prepare(
    'SELECT * FROM users WHERE username = :username AND password = :password'
);
$stmt->execute([
    ':username' => $username,
    ':password' => $password,
]);
$rows = $stmt->fetchAll();

ORMs vs Raw Queries

Object-Relational Mappers (ORMs) like Prisma, SQLAlchemy, Django ORM, Hibernate, and GORM provide an additional layer of protection by generating parameterized SQL automatically. When you use an ORM's query builder, SQL injection is virtually impossible because user input never becomes part of the SQL string.

// Prisma (Node.js) - automatically parameterized
const user = await prisma.user.findFirst({
  where: {
    username: username,
    password: password,
  },
});

# Django ORM (Python) - automatically parameterized
user = User.objects.filter(
    username=username,
    password=password,
).first()

# SQLAlchemy (Python) - automatically parameterized
user = session.query(User).filter(
    User.username == username,
    User.password == password,
).first()

However, every ORM provides an escape hatch for writing raw SQL. These raw query methods are where SQL injection can re-emerge if you are not careful. Always use parameterized raw queries:

// Prisma raw query - SAFE with parameterization
const users = await prisma.$queryRaw`
  SELECT * FROM users WHERE username = ${username}
`;

// Prisma raw query - UNSAFE (string concatenation)
// const users = await prisma.$queryRawUnsafe(
//   "SELECT * FROM users WHERE username = '" + username + "'"
// );

# Django raw query - SAFE with parameterization
users = User.objects.raw(
    'SELECT * FROM users WHERE username = %s',
    [username]
)

Defense in Depth

Parameterized queries are the primary defense, but a robust security posture uses multiple layers:

• Input validation: Validate that user input matches expected patterns before it reaches the database layer. A username should match [a-zA-Z0-9_], an email should match a valid email pattern, and a numeric ID should be a positive integer. Validation does not replace parameterization, but it catches malicious input early. For URL-encoded inputs, ensure you are properly decoding before validation -- see our URL encoding/decoding tool for reference.
• Least privilege: The database user that your application connects with should have the minimum necessary permissions. An application that only needs to read and write data should not have DROP TABLE, CREATE USER, or GRANT permissions. This limits the damage even if injection occurs.
• Stored procedures: Stored procedures can encapsulate SQL logic and accept only typed parameters, adding another layer of separation between user input and SQL execution. However, stored procedures that internally use dynamic SQL with string concatenation are still vulnerable.
• Web Application Firewall (WAF): A WAF can detect and block common SQL injection patterns in HTTP requests. This is a useful additional layer but should never be the sole defense, as WAFs can be bypassed with encoding tricks and novel payloads.
• Error handling: Never expose raw database error messages to users. Detailed SQL errors can reveal table names, column names, and database structure that help attackers craft more targeted injection payloads. Log errors server-side and return generic error messages to the client.

Testing for SQL Injection

Proactive testing is essential. Do not wait for a penetration test or, worse, an actual breach to discover injection vulnerabilities.

Manual Testing

For any input field that is used in a database query, try entering these common test payloads:

' OR '1'='1
' OR '1'='1' --
' UNION SELECT NULL, NULL, NULL --
1; DROP TABLE users --
' AND 1=CONVERT(int, (SELECT TOP 1 table_name FROM information_schema.tables)) --

If any of these inputs causes unexpected behavior (authentication bypass, error messages containing SQL syntax, or altered query results), the endpoint is vulnerable.

Automated Testing Tools

• sqlmap: The most comprehensive open-source SQL injection testing tool. It automates detection and exploitation of SQL injection flaws and supports all major database engines.
• OWASP ZAP: A free web application security scanner that includes SQL injection detection among its many test modules.
• Burp Suite: A professional web security testing platform with advanced SQL injection detection capabilities in its active scanner.

Code Review Checklist

During code reviews, look for these red flags:

• String concatenation or template literals that include user input in SQL strings
• Use of ORM "raw query" or "unsafe" methods without parameterization
• Dynamic table or column names derived from user input (these cannot be parameterized and require allowlist validation)
• Stored procedures that build dynamic SQL internally using concatenation
• Absence of input validation on fields used in database queries

What Cannot Be Parameterized

Parameterized queries protect values (WHERE conditions, INSERT values, UPDATE sets), but they cannot parameterize structural elements of SQL: table names, column names, ORDER BY directions, and LIMIT values in some databases. When these elements must be dynamic, use strict allowlist validation:

// SAFE - allowlist for dynamic column names
const ALLOWED_COLUMNS = ['name', 'email', 'created_at'];
const ALLOWED_DIRECTIONS = ['ASC', 'DESC'];

const sortColumn = ALLOWED_COLUMNS.includes(userInput.column)
  ? userInput.column
  : 'created_at';

const sortDirection = ALLOWED_DIRECTIONS.includes(userInput.direction?.toUpperCase())
  ? userInput.direction.toUpperCase()
  : 'DESC';

const { rows } = await pool.query(
  `SELECT * FROM users ORDER BY ${sortColumn} ${sortDirection} LIMIT $1`,
  [limit]
);

Summary

SQL injection is entirely preventable. Use parameterized queries for every database interaction, validate input early, apply the principle of least privilege to database accounts, and test regularly with both manual and automated methods. Every language and framework provides the tools to write safe SQL -- the vulnerability exists only when developers bypass those tools by building queries through string concatenation.

Use our SQL Formatter to inspect and clean up queries before deploying them, and use the Analyze tab to verify the structure of complex queries.