PHP Security: Preventing SQL Injection

SQL injection is one of the most dangerous web vulnerabilities. Learn how to protect your applications using modern PHP security practices.

Understanding SQL Injection

The Vulnerability

php

1// DANGEROUS - Never do this!
2$id = $_GET['id'];
3$query = "SELECT * FROM users WHERE id = $id";
4$result = mysqli_query($conn, $query);

An attacker could send: ?id=1 OR 1=1 and retrieve all users!

The Attack Vector

sql

1-- Original query
2SELECT * FROM users WHERE id = 1
3
4-- Injected query
5SELECT * FROM users WHERE id = 1 OR 1=1
6
7-- Result: Returns ALL users instead of one!

Using PDO Prepared Statements

Correct Approach

php

1$pdo = new PDO('mysql:host=localhost;dbname=myapp', 'user', 'pass');
2$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
3
4$id = $_GET['id'];
5$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
6$stmt->execute([$id]);
7$user = $stmt->fetch(PDO::FETCH_ASSOC);

Named Parameters

php

1$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND status = :status');
2$stmt->execute([
3    ':email' => $email,
4    ':status' => 'active'
5]);
6$user = $stmt->fetch();

Building a Secure Database Class

php

1class Database
2{
3    private PDO $pdo;
4    
5    public function __construct(string $dsn, string $user, string $pass)
6    {
7        $this->pdo = new PDO($dsn, $user, $pass, [
8            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
9            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
10            PDO::ATTR_EMULATE_PREPARES => false,
11        ]);
12    }
13    
14    public function query(string $sql, array $params = []): array
15    {
16        $stmt = $this->pdo->prepare($sql);
17        $stmt->execute($params);
18        return $stmt->fetchAll();
19    }
20    
21    public function execute(string $sql, array $params = []): bool
22    {
23        $stmt = $this->pdo->prepare($sql);
24        return $stmt->execute($params);
25    }
26    
27    public function lastInsertId(): string
28    {
29        return $this->pdo->lastInsertId();
30    }
31}

Practical Examples

User Registration

php

1function registerUser(Database $db, string $email, string $password): bool
2{
3    // Hash password
4    $hashedPassword = password_hash($password, PASSWORD_ARGON2ID);
5    
6    // Use prepared statement
7    return $db->execute(
8        'INSERT INTO users (email, password, created_at) VALUES (?, ?, NOW())',
9        [$email, $hashedPassword]
10    );
11}

User Login

php

1function loginUser(Database $db, string $email, string $password): ?array
2{
3    $users = $db->query(
4        'SELECT * FROM users WHERE email = ? LIMIT 1',
5        [$email]
6    );
7    
8    if (empty($users)) {
9        return null;
10    }
11    
12    $user = $users[0];
13    
14    if (password_verify($password, $user['password'])) {
15        return $user;
16    }
17    
18    return null;
19}

Search Functionality

php

1function searchPosts(Database $db, string $keyword): array
2{
3    // Use LIKE with prepared statements
4    $searchTerm = '%' . $keyword . '%';
5    
6    return $db->query(
7        'SELECT * FROM posts WHERE title LIKE ? OR content LIKE ?',
8        [$searchTerm, $searchTerm]
9    );
10}

Additional Security Measures

Input Validation

php

1function validateEmail(string $email): bool
2{
3    return filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
4}
5
6function sanitizeInput(string $input): string
7{
8    return htmlspecialchars(trim($input), ENT_QUOTES, 'UTF-8');
9}

Whitelisting

php

1function getOrderBy(string $column): string
2{
3    $allowed = ['name', 'email', 'created_at'];
4    
5    return in_array($column, $allowed) ? $column : 'created_at';
6}
7
8$orderBy = getOrderBy($_GET['sort'] ?? 'created_at');
9$users = $db->query("SELECT * FROM users ORDER BY $orderBy");

Common Mistakes to Avoid

1. Don't Trust User Input

php

1// BAD
2$table = $_GET['table'];
3$query = "SELECT * FROM $table";
4
5// GOOD
6$allowedTables = ['users', 'posts', 'comments'];
7$table = in_array($_GET['table'], $allowedTables) ? $_GET['table'] : 'users';

2. Don't Use String Concatenation

php

1// BAD
2$query = "SELECT * FROM users WHERE id = " . $id;
3
4// GOOD
5$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
6$stmt->execute([$id]);

3. Always Use Parameterized Queries

Even for "safe" data, use prepared statements for consistency and safety.

Security Checklist

✅ Use PDO or MySQLi with prepared statements

✅ Never concatenate user input into SQL queries

✅ Validate and sanitize all user input

✅ Use whitelist validation for dynamic queries

✅ Hash passwords with password_hash()

✅ Enable error reporting in development only

✅ Use HTTPS for all database connections

✅ Implement proper error handling

Conclusion

SQL injection is preventable with proper coding practices. Always use prepared statements, validate input, and follow the principle of least privilege for database users.