SQL injection remains one of the most dangerous and prevalent web application vulnerabilities. Despite being well-understood for decades, it continues to appear in security breaches. This guide covers how SQL injection works and, more importantly, how to prevent it.
Understanding SQL Injection
SQL injection occurs when untrusted data is sent to a database as part of a query. When user input is concatenated directly into SQL statements, attackers can manipulate the query to access, modify, or delete data they shouldn't be able to touch.
A Classic Example
Consider this vulnerable PHP code:
This snippet demonstrates the fundamental mistake that enables SQL injection. User input flows directly into the query string without any sanitization or parameterization.
$username = $_POST['username'];
$query = "SELECT * FROM users WHERE username = '$username'";
$result = mysqli_query($connection, $query);
If a user enters a normal username like "john", the query works as intended. The resulting SQL is exactly what you would expect:
SELECT * FROM users WHERE username = 'john'
This query returns the single user with that username. But what if an attacker enters ' OR '1'='1? The query becomes something entirely different:
SELECT * FROM users WHERE username = '' OR '1'='1'
Since '1'='1' is always true, this returns all users in the database. The attacker's single quote closes the intended string literal, and the rest of their input becomes part of the SQL logic. More destructive inputs could delete data or extract sensitive information from other tables.
Types of SQL Injection
In-Band SQLi
The most common type where the attacker uses the same communication channel to launch the attack and gather results.
Error-based: Uses database error messages to reveal information about structure.
Union-based: Uses UNION SQL operator to combine results with data from other tables.
Blind SQLi
The application doesn't show database errors, but the attacker can still extract information.
Boolean-based: Sends queries that return true or false, observing page behavior changes.
Time-based: Uses database sleep functions. If the response is delayed, the condition was true.
Out-of-Band SQLi
Uses different channels like DNS requests to exfiltrate data. Less common but effective when in-band techniques fail.
Prevention Techniques
1. Parameterized Queries (Prepared Statements)
The most effective defense. Parameters are treated as literal values, never as SQL code.
PHP with PDO:
PDO's prepare method separates SQL structure from data. The database engine knows exactly where user input belongs and treats it as a value, never as executable code.
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');
$stmt->execute(['username' => $username]);
$user = $stmt->fetch();
The :username placeholder gets replaced safely during execution. Even if an attacker submits malicious input, it becomes a literal string value rather than SQL syntax.
Node.js with mysql2:
The question mark placeholder works the same way in Node.js. The library handles escaping and type conversion automatically.
const [rows] = await connection.execute(
'SELECT * FROM users WHERE username = ?',
[username]
);
Python with psycopg2:
Python follows the same pattern with %s placeholders. Notice the tuple syntax for the second parameter, which is required even for a single value.
cursor.execute('SELECT * FROM users WHERE username = %s', (username,))
user = cursor.fetchone()
2. Use an ORM
Object-Relational Mappers like Eloquent, Doctrine, Sequelize, and SQLAlchemy handle parameterization automatically.
Laravel Eloquent:
When you use Eloquent's fluent methods, parameterization happens behind the scenes. You get clean, readable code that's secure by default.
// Safe - Eloquent uses prepared statements
$user = User::where('username', $username)->first();
Django ORM:
Django's ORM follows the same principle. The ORM constructs parameterized queries internally, protecting you from injection without extra effort.
# Safe - Django ORM parameterizes automatically
user = User.objects.get(username=username)
Caution: ORMs can still be vulnerable if you use raw queries carelessly:
The whereRaw method exists for complex queries that the ORM can't express. However, it bypasses the ORM's automatic parameterization, so you must handle it yourself.
// DANGEROUS - raw query with concatenation
User::whereRaw("username = '$username'")->first();
// SAFE - raw query with bindings
User::whereRaw('username = ?', [$username])->first();
The difference is subtle but critical. Look closely at the quotes: the dangerous version uses double quotes around the SQL and interpolates the variable directly. The safe version uses single quotes and passes the variable as a separate binding in an array.
3. Input Validation
While not a complete solution alone, validation adds defense in depth.
Restricting input to expected patterns catches many malicious payloads before they reach the database layer. This example only allows alphanumeric characters and underscores in usernames.
// Validate expected format
if (!preg_match('/^[a-zA-Z0-9_]{3,20}$/', $username)) {
throw new InvalidArgumentException('Invalid username format');
}
Use allowlists over denylists. It's easier to define what's allowed than to catch every possible attack.
4. Stored Procedures
When written correctly, stored procedures can prevent injection. However, they can still be vulnerable if they concatenate inputs internally.
This stored procedure uses a parameter properly. The database treats p_username as a value, not as part of the SQL structure.
-- Safe stored procedure
CREATE PROCEDURE GetUser(IN p_username VARCHAR(50))
BEGIN
SELECT * FROM users WHERE username = p_username;
END;
Avoid building dynamic SQL inside stored procedures using string concatenation. That approach simply moves the vulnerability into the database layer.
5. Least Privilege
Even if injection occurs, minimize damage by restricting database account permissions:
- Application accounts shouldn't have DROP, CREATE, or ALTER privileges
- Use separate accounts for different operations (read-only for reports)
- Never use root/sa accounts in applications
6. Web Application Firewalls (WAFs)
WAFs can detect and block common injection patterns. They're useful as an additional layer but shouldn't be your only defense.
Framework-Specific Guidance
Laravel
Laravel's query builder uses parameterized queries when you pass values through its methods. The where method's second parameter for the operator and third for the value ensure proper binding.
// Always use Eloquent methods or query builder bindings
$users = DB::table('users')
->where('email', '=', $email)
->where('status', '=', $status)
->get();
// For dynamic column names, use allowlists
$allowed = ['name', 'email', 'created_at'];
$sortBy = in_array($request->sort, $allowed) ? $request->sort : 'name';
$users = User::orderBy($sortBy)->get();
You'll notice that the second example uses in_array to validate against an allowlist rather than trying to sanitize the input. This approach addresses a common mistake. Column and table names cannot be parameterized, so you must validate them against an allowlist. Never use user input directly for column names.
Express.js / Node
In Node.js with PostgreSQL, numbered placeholders like $1 and $2 indicate binding positions. The values array must match the placeholder order.
// With mysql2 or pg, always use parameterized queries
const { rows } = await pool.query(
'SELECT * FROM users WHERE id = $1 AND org_id = $2',
[userId, orgId]
);
Django
Django's ORM handles most cases automatically. When you need raw SQL for performance or complex queries, always use the parameterized form with a list of values.
# Use ORM queries
User.objects.filter(username=username, is_active=True)
# For raw queries, always parameterize
User.objects.raw('SELECT * FROM users WHERE id = %s', [user_id])
Testing for SQL Injection
Manual Testing
When testing your own applications, try these inputs in form fields to verify protection is in place. Each payload attempts a different injection technique:
'- Single quote (should trigger an error if vulnerable)' OR '1'='1- Boolean logic bypass'; DROP TABLE users;--- Statement termination and destructive command1' AND '1'='1- Conditional testing
If your application handles these gracefully without errors or unexpected behavior, your defenses are likely working. Always test on development environments, never production.
Automated Tools
- SQLMap: Automated SQL injection detection and exploitation
- OWASP ZAP: Web application security scanner
- Burp Suite: Web security testing platform
Code Analysis
Static analysis tools can find potential injection points before code reaches production. These tools scan your codebase for patterns that indicate unsafe SQL handling:
- PHPStan/Psalm with security rules
- Semgrep with SQL injection rules
- SonarQube
Integrating these tools into your CI/CD pipeline catches vulnerabilities early in the development process.
Conclusion
SQL injection is entirely preventable. Use parameterized queries consistently, leverage your framework's ORM properly, validate inputs, and follow the principle of least privilege. Make these practices habits, and SQL injection becomes a non-issue in your applications.