DEV Community

Cover image for SQL Injection Prevention: Developer's Security Checklist
Rasim Çarkçı
Rasim Çarkçı

Posted on Originally published at moreonlinetools.com

SQL Injection Prevention: Developer's Security Checklist

#security #webdev #sql #beginners

SQL injection is still the number one cause of data breaches — and it is entirely preventable. This checklist covers every layer of defense: parameterized queries, ORMs, input validation, least-privilege databases, and WAF rules.

Originally published on MoreOnlineTools Blog.

Why SQL Injection Is Still Top of the OWASP List

SQL injection (SQLi) has topped the OWASP Top 10 list of web application security risks for over a decade. Despite being a well-understood attack, it causes millions of breaches every year — from small WordPress sites to Fortune 500 databases. The reason is simple: developers under deadline skip validation, copy unsafe code snippets from tutorials, or trust that ORMs handle everything automatically (they do not, if you bypass them).

A successful SQL injection lets an attacker:

  • Dump your entire database (usernames, passwords, credit cards, PII)

  • Bypass authentication (login as any user without knowing their password)

  • Modify or delete data silently

  • Execute OS-level commands on poorly configured database servers

  • Use your database server as a beachhead for lateral movement

How SQL Injection Works

The root cause is always the same: user-controlled input is concatenated directly into a SQL query. When the database engine cannot distinguish between SQL code and data, an attacker can change the structure of the query itself.

Classic example — unsafe PHP login:

// UNSAFE — never do this
$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "'
          AND password = '" . md5($_POST['password']) . "'";
Enter fullscreen mode Exit fullscreen mode

An attacker enters the username: admin'--

The resulting query becomes:

SELECT * FROM users WHERE username = 'admin'--' AND password = '...'
Enter fullscreen mode Exit fullscreen mode

The -- starts a SQL comment, nullifying the password check. The attacker is now logged in as admin without knowing the password. More destructive payloads can use UNION SELECT to exfiltrate data or ; DROP TABLE users; -- to destroy it.

Defense Layer 1: Parameterized Queries (Prepared Statements)

This is the single most important defense. With parameterized queries, you send the SQL structure to the database engine separately from the data. The engine compiles the query first, then binds the data — no matter what the data contains, it can never alter the query structure.

PHP (PDO) 

// SAFE — parameterized query with PDO
$pdo = new PDO('mysql:host=localhost;dbname=mydb', $user, $pass, [
    PDO::ATTR_EMULATE_PREPARES => false,  // critical: use real prepared statements
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare('SELECT * FROM users WHERE username = ? AND active = ?');
$stmt->execute([$_POST['username'], 1]);
$user = $stmt->fetch();
Enter fullscreen mode Exit fullscreen mode

Important: Set PDO::ATTR_EMULATE_PREPARES => false. Without it, PDO emulates prepared statements in PHP rather than using real DB-level parameterization.

PHP (MySQLi) 

$conn = new mysqli('localhost', $user, $pass, 'mydb');
$stmt = $conn->prepare('SELECT * FROM users WHERE username = ? AND active = ?');
$stmt->bind_param('si', $_POST['username'], 1);
$stmt->execute();
$result = $stmt->get_result();
Enter fullscreen mode Exit fullscreen mode

Python (psycopg2 / PostgreSQL) 

cursor.execute(
    "SELECT * FROM users WHERE username = %s AND active = %s",
    (request.form['username'], True)
)
# Never use f-strings or % formatting for SQL — always pass values as a tuple
Enter fullscreen mode Exit fullscreen mode

Node.js (mysql2) 

// Using ? placeholders — mysql2 escapes automatically
const [rows] = await connection.execute(
    'SELECT * FROM users WHERE username = ? AND active = ?',
    [req.body.username, 1]
);
Enter fullscreen mode Exit fullscreen mode

Java (JDBC) 

PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE username = ? AND active = ?"
);
stmt.setString(1, request.getParameter("username"));
stmt.setInt(2, 1);
ResultSet rs = stmt.executeQuery();
Enter fullscreen mode Exit fullscreen mode

Defense Layer 2: ORM Usage — and Its Limits

Modern ORMs like Django ORM, Hibernate, Eloquent, and Sequelize generate parameterized queries by default. If you use the ORM API correctly, you are largely protected. The danger zones:

Raw SQL escapes within ORMs 

# Django — UNSAFE: user input in raw()
User.objects.raw(f"SELECT * FROM users WHERE name = '{username}'")

# Django — SAFE: parameterized raw()
User.objects.raw("SELECT * FROM users WHERE name = %s", [username])
Enter fullscreen mode Exit fullscreen mode

Laravel / Eloquent — UNSAFE 

// Never interpolate into whereRaw
DB::table('users')->whereRaw("name = '{$name}'");

// SAFE — use bindings
DB::table('users')->whereRaw("name = ?", [$name]);
Enter fullscreen mode Exit fullscreen mode

Sequelize (Node.js) — UNSAFE 

// Never use template literals in where
User.findAll({ where: sequelize.literal(`name = '${name}'`) });

// SAFE
User.findAll({ where: { name: name } });
Enter fullscreen mode Exit fullscreen mode

The rule: never interpolate or concatenate user input into any SQL string, even inside an ORM. Always use the ORM's parameterization bindings.

Defense Layer 3: Stored Procedures

Stored procedures define SQL logic on the database side. When called with parameters (not string concatenation inside the procedure), they provide parameterization at the database level:

-- MySQL stored procedure (safe)
DELIMITER $$
CREATE PROCEDURE GetUser(IN p_username VARCHAR(100))
BEGIN
    SELECT * FROM users WHERE username = p_username;
END$$

-- Calling it safely from PHP:
$stmt = $pdo->prepare('CALL GetUser(?)');
$stmt->execute([$username]);
Enter fullscreen mode Exit fullscreen mode

Note: stored procedures are not a silver bullet. If the procedure itself concatenates dynamic SQL internally (e.g. uses EXECUTE IMMEDIATE with string building), the vulnerability exists inside the procedure.

Defense Layer 4: Input Validation and Allowlisting

Parameterized queries handle the SQL injection vector, but adding input validation reduces attack surface further:

  • Allowlist expected characters — if a field should be a numeric ID, reject anything that is not a number.

  • Enforce length limits — reject inputs that are too long to be legitimate.

  • Validate type before use — cast to integer, float, or UUID where appropriate.

// PHP — validate a numeric ID before using it
$user_id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
if ($user_id === false || $user_id <= 0) {
    http_response_code(400);
    exit('Invalid ID');
}
// $user_id is now a safe integer — even concatenated in SQL it cannot inject
Enter fullscreen mode Exit fullscreen mode

Allowlisting does not replace parameterization — column names and table names cannot be parameterized (only values can). If you must handle dynamic column/table names, allowlist them explicitly:

$allowed_columns = ['name', 'email', 'created_at'];
$sort_col = $_GET['sort'] ?? 'name';
if (!in_array($sort_col, $allowed_columns, true)) {
    $sort_col = 'name'; // fallback to safe default
}
$sql = "SELECT * FROM users ORDER BY {$sort_col} ASC"; // safe because column name is allowlisted
Enter fullscreen mode Exit fullscreen mode

Defense Layer 5: Least Privilege Database Users

Even if an attacker finds an SQL injection, the blast radius depends on what the database user can do. Your application should connect as a user with only the permissions it needs:

Application role Required permissions Should NOT have
Web app (read/write) SELECT, INSERT, UPDATE, DELETE on specific tables DROP, CREATE, ALTER, FILE, SUPER, GRANT
Reporting / read-only SELECT on specific views or tables INSERT, UPDATE, DELETE, DDL
Migrations / admin DDL permissions (run separately, never from app) Should not be the runtime app user 
-- MySQL: create a minimal-privilege app user
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'strong_random_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.users TO 'appuser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.orders TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
-- No DROP, no FILE, no SUPER — limits what injection can do
Enter fullscreen mode Exit fullscreen mode

Defense Layer 6: Disable Detailed Database Error Messages

Database error messages in production responses are a gift to attackers — they reveal table names, column names, query structure, and database type. This is called error-based SQLi reconnaissance.

// PHP — never expose DB errors to users in production
try {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
} catch (PDOException $e) {
    error_log($e->getMessage()); // log internally
    http_response_code(500);
    exit('An error occurred. Please try again later.'); // generic message only
}
Enter fullscreen mode Exit fullscreen mode

Set display_errors = Off in php.ini in production. Similarly, configure your ORM to suppress raw SQL errors from HTTP responses.

Defense Layer 7: Web Application Firewall (WAF)

A WAF provides a last-resort safety net by detecting and blocking known SQL injection patterns before they reach your application. Common WAF solutions include Cloudflare WAF, AWS WAF, ModSecurity (for Apache/Nginx), and Sucuri.

WAFs are not a substitute for parameterized queries — a sufficiently obfuscated payload can bypass WAF rules. But they raise the cost of attack significantly and stop automated scanners and script-kiddies.

ModSecurity with OWASP CRS (Core Rule Set) provides a solid baseline:

# nginx + ModSecurity + OWASP CRS
# modsecurity.conf:
SecRuleEngine On
SecRequestBodyAccess On
Include /etc/nginx/modsec/crs/crs-setup.conf
Include /etc/nginx/modsec/crs/rules/*.conf
Enter fullscreen mode Exit fullscreen mode

SQL Injection Prevention Checklist

  • ✅ Use parameterized queries / prepared statements everywhere — no exceptions

  • ✅ Set PDO::ATTR_EMULATE_PREPARES => false in PHP/PDO

  • ✅ Never use string concatenation or interpolation to build SQL queries

  • ✅ In ORMs, always use parameterized raw() calls — never f-string / template interpolation

  • ✅ Allowlist dynamic column/table names — they cannot be parameterized

  • ✅ Validate and cast input types before use (integer IDs, UUIDs, etc.)

  • ✅ Run DB connections under least-privilege users — no DDL rights for the app user

  • ✅ Suppress database error details in HTTP responses — log internally only

  • ✅ Deploy a WAF with OWASP CRS rules as a defence-in-depth layer

  • ✅ Run automated scanning (SQLMap, OWASP ZAP) against your own app regularly

  • ✅ Set up a bug bounty or responsible disclosure policy for external researchers

Testing Your Own App for SQL Injection

Prevention is best confirmed by testing. Tools you should run against your own application in a staging environment:

  • SQLMap — automatic SQL injection detection and exploitation tool (use only on apps you own)

  • OWASP ZAP — open-source web application scanner with active SQL injection rules

  • Burp Suite Community — manual proxy for intercepting and modifying requests to test injection points

# SQLMap basic scan (staging/dev only — never against live production without permission)
sqlmap -u "https://staging.myapp.com/users?id=1" --dbs --batch
Enter fullscreen mode Exit fullscreen mode

If any finding comes back, treat it as critical (P0) and fix immediately before it reaches production.

Want to check whether your server leaks database error messages or lacks security headers? Try our free HTTP Security Headers Analyzer — paste any URL for an instant scan. Runs entirely in your browser.

Top comments (0)