How to Prevent SQL Injection

What you’ll build or solve

You’ll secure database queries by separating user input from SQL logic.

When this approach works best

This approach works best when:

• You accept user input in forms such as login or search.
• You build APIs that generate SQL from request parameters.
• You use dynamic queries in backend code.

Skip this only if your system never accepts external input, which is uncommon in real applications.

Prerequisites

• Basic understanding of SQL queries
• A backend language such as JavaScript, Python, PHP, or Java
• Access to your database driver or ORM

Step-by-step instructions

Step 1: Replace string concatenation with parameterized queries

SQL injection happens when you build queries by inserting raw user input into a string.

Unsafe example:

constquery="SELECT * FROM users WHERE email = '"+email+"'";

If a user submits:

' OR 1=1 --

The query becomes:

SELECT*FROM usersWHERE email=''OR1=1--'

The condition is bypassed.

Instead, use parameterized queries. They keep SQL structure separate from data.

Option A: JavaScript with Node.js

constquery="SELECT * FROM users WHERE email = ?";
db.query(query, [email]);

Option B: Python with psycopg2

cursor.execute(
"SELECT * FROM users WHERE email = %s",
    (email,)
)

Option C: PHP with PDO

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);

In all cases, placeholders such as ? or %s prevent user input from changing SQL logic.

What to look for

• Your SQL contains placeholders, not concatenated variables.
• User values are passed separately as parameters.
• No user input appears directly inside quotes in the query string.

This is the primary defense against SQL injection.

Step 2: Whitelist dynamic identifiers

Placeholders protect values, not table names or column names.

Unsafe:

constquery=`SELECT * FROM${tableName}`;

A malicious value could inject additional SQL.

Instead, restrict identifiers to a predefined list:

constallowedTables= ["users","orders"];

if (!allowedTables.includes(tableName)) {
thrownewError("Invalid table");
}

constquery=`SELECT * FROM${tableName}`;

Only allow known, approved identifiers.

Use the same pattern for sorting fields:

constallowedFields= ["name","created_at"];

if (!allowedFields.includes(sortField)) {
thrownewError("Invalid sort field");
}

constquery=`SELECT * FROM users ORDER BY${sortField}`;

This prevents structural manipulation of the query.

Step 3: Add defense-in-depth layers

Parameterized queries are the core protection. The following measures reduce risk further.

Validate input on the server

Validation improves security and data quality.

JavaScript:

if (!email.includes("@")) {
thrownewError("Invalid email");
}

Python:

ifnotuser_id.isdigit():
raiseValueError("Invalid ID")

Validation does not replace parameterization. It reduces unexpected input and logic errors.

Use least-privilege database accounts

Even if injection succeeds, limit damage by restricting permissions.

Example in MySQL:

GRANTSELECT,INSERT,UPDATE
ON database_name.*
TO'app_user'@'localhost';

Avoid using administrative accounts in application code.

Limited privileges prevent attackers from dropping tables or modifying schema.

Examples you can copy

Example 1: Secure login query in Node.js

constquery="SELECT id FROM users WHERE email = ? AND password = ?";
db.query(query, [email,password]);

User input cannot modify query structure.

Example 2: Secure user lookup in Python

cursor.execute(
"SELECT id, name FROM users WHERE id = %s",
    (user_id,)
)

The driver treats user_id strictly as data.

Example 3: Secure dynamic sorting

constallowedFields= ["name","created_at"];

if (!allowedFields.includes(sortField)) {
thrownewError("Invalid sort field");
}

constquery=`SELECT * FROM users ORDER BY${sortField}`;

The sorting column is restricted to approved values.

Example 4: Combined protection pattern

if (!email.includes("@")) {
thrownewError("Invalid email");
}

constquery="SELECT * FROM users WHERE email = ?";
db.query(query, [email]);

Validation plus parameterization provides layered protection.

Common mistakes and how to fix them

Mistake 1: Escaping instead of parameterizing

You might manually escape quotes:

email=email.replace("'","\\'");
constquery="SELECT * FROM users WHERE email = '"+email+"'";

Why it breaks:

Escaping rules vary by database and are easy to miss.

Correct approach:

constquery="SELECT * FROM users WHERE email = ?";
db.query(query, [email]);

Always use prepared statements.

Mistake 2: Trusting client-side validation

You might validate input only in the browser.

Why it breaks:

Attackers can bypass frontend checks using API tools.

Correct approach:

Validate and parameterize on the server side.

Mistake 3: Using high-privilege database credentials

You might connect using a full admin account.

Why it breaks:

If injection occurs, attackers can alter or delete data.

Correct approach:

GRANTSELECT,INSERT,UPDATE
ON database_name.*
TO'app_user'@'localhost';

Grant only required permissions.

Troubleshooting

If injection still works, confirm every query uses parameterized statements.

If placeholders do not work, verify that your database driver supports prepared statements.

If dynamic identifiers are required, confirm they come from a strict whitelist.

If login queries fail after refactoring, check parameter order matches placeholder order.

If errors mention %s or ?, confirm you are using the correct placeholder style for your database driver.

Quick recap

• Never concatenate user input into SQL strings.
• Use parameterized queries as the primary defense.
• Whitelist dynamic table and column names.
• Validate input on the server side.
• Use least-privilege database accounts.
• Layer protections to reduce risk and impact.