NeuroAgent

Prevent SQL Injection in PHP: Complete Guide

Master SQL injection prevention in PHP applications with prepared statements, input validation, and security best practices. Complete guide with practical code examples and implementation tips.

#database-security#input-validation#owasp-security#php-security#prepared-statements#sql-injection-prevention#web-development
Question

How can I prevent SQL injection in PHP applications?

If user input is inserted without modification into an SQL query, the application becomes vulnerable to SQL injection, as shown in this example:

php
$unsafe_variable = $_POST['user_input']; 
mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

This vulnerability occurs because a user could input something like value'); DROP TABLE table;--, which would transform the query into:

sql
INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

What are the best practices and methods to prevent SQL injection attacks in PHP applications?

NeuroAgent

SQL injection attacks can be prevented in PHP applications by consistently using prepared statements with parameterized queries, implementing proper input validation, and applying output encoding. These methods create a defense-in-depth approach that separates SQL logic from user data, making it virtually impossible for attackers to manipulate queries through malicious input.

Contents

Understanding SQL Injection Risks

SQL injection occurs when user-supplied input is improperly handled and concatenated directly into SQL queries. This allows attackers to manipulate the query structure, potentially gaining unauthorized access to data, modifying database content, or even executing administrative commands.

The vulnerability exists because PHP’s older mysql_* functions (which are now deprecated) and improperly used mysqli or PDO connections allow raw SQL injection. Modern PHP applications must adopt secure coding practices to eliminate this risk.

Critical Risk: SQL injection remains one of the most dangerous web application vulnerabilities, with the OWASP Top 10 consistently ranking it among the top security threats.

The example you provided demonstrates the classic vulnerability pattern:

php
// VULNERABLE CODE
$unsafe_variable = $_POST['user_input']; 
mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

When a malicious user inputs value'); DROP TABLE table;--, the query becomes:

sql
INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

This executes multiple statements, potentially destroying your database.

Prepared Statements and Parameterized Queries

Prepared statements are the most effective defense against SQL injection. They work by separating SQL code from data, ensuring that user input is treated strictly as data and never as executable code.

Using MySQLi with Prepared Statements

php
$conn = new mysqli("localhost", "username", "password", "database");

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare statement
$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);

// Set parameters and execute
$username = $_POST['username'];
$email = $_POST['email'];
$stmt->execute();

echo "New records created successfully";
$stmt->close();
$conn->close();

Using PDO with Prepared Statements

php
try {
    $pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
    $stmt->bindParam(':username', $username);
    $stmt->bindParam(':email', $email);
    
    $username = $_POST['username'];
    $email = $_POST['email'];
    $stmt->execute();
    
    echo "New records created successfully";
    
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

Key Benefits of Prepared Statements:

  • Automatic escaping of special characters
  • Protection against SQL injection
  • Better performance for repeated queries
  • Clear separation of code and data

Input Validation and Sanitization

While prepared statements are the primary defense, proper input validation adds an important layer of security.

Input Validation Rules

Validate input based on expected format and business rules:

php
function validateEmail($email) {
    return filter_var($email, FILTER_VALIDATE_EMAIL);
}

function validateUsername($username) {
    // Alphanumeric, 3-20 characters
    return preg_match('/^[a-zA-Z0-9]{3,20}$/', $username);
}

// Usage
if (!validateUsername($_POST['username'])) {
    die("Invalid username format");
}

Output Encoding for Display

When displaying user data in HTML, use proper encoding:

php
echo htmlspecialchars($user_input, ENT_QUOTES, 'UTF-8');

For JSON output:

php
json_encode($data, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP);

Additional Security Best Practices

Principle of Least Privilege

Database users should have only the minimum permissions necessary:

sql
-- Create restricted user
CREATE APPLICATION USER IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT ON application.users TO APPLICATION USER;
-- Do NOT grant DROP TABLE or other dangerous permissions

Stored Procedures

Use stored procedures to encapsulate database operations:

php
$stmt = $pdo->prepare("CALL sp_add_user(:username, :email, :role)");
$stmt->execute([
    'username' => $username,
    'email' => $email,
    'role' => $role
]);

Database Connection Security

Always use secure database connections:

php
// For MySQLi
$conn = new mysqli("localhost", "username", "password", "database");
$conn->set_charset("utf8mb4");

// For PDO
$pdo = new PDO('mysql:host=localhost;dbname=database;charset=utf8mb4', 'username', 'password');

Common Vulnerabilities to Avoid

Dynamic Query Construction

Never build queries with string concatenation:

php
// VULNERABLE
$query = "SELECT * FROM users WHERE id = " . $_GET['id'];

// SECURE
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_GET['id']]);

Magic Quotes and Other Deprecated Features

Avoid relying on deprecated PHP features:

php
// Don't use - deprecated in PHP 7.4, removed in PHP 8.0
if (get_magic_quotes_gpc()) {
    $input = stripslashes($_POST['input']);
}

Error Information Leakage

Never expose detailed error information to users:

php
// VULNERABLE - shows database structure
if (!$result) {
    die(mysqli_error($conn));
}

// SECURE
if (!$result) {
    die("Database error occurred");
}

Implementation Examples

Complete Secure Registration System

php
function registerUser($pdo, $username, $email, $password) {
    // Input validation
    if (!validateUsername($username) || !validateEmail($email)) {
        throw new InvalidArgumentException("Invalid input format");
    }
    
    // Hash password
    $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
    
    try {
        $pdo->beginTransaction();
        
        // Check if username exists
        $stmt = $pdo->prepare("SELECT id FROM users WHERE username = ?");
        $stmt->execute([$username]);
        if ($stmt->fetch()) {
            throw new Exception("Username already exists");
        }
        
        // Insert user
        $stmt = $pdo->prepare("INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)");
        $stmt->execute([$username, $email, $hashedPassword]);
        
        $pdo->commit();
        return true;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Search Function with Prevention

php
function searchUsers($pdo, $searchTerm) {
    // Validate search term
    $searchTerm = trim($searchTerm);
    if (empty($searchTerm) || strlen($searchTerm) < 2) {
        return [];
    }
    
    // Use LIKE with proper escaping
    $stmt = $pdo->prepare("SELECT id, username, email FROM users 
                          WHERE username LIKE ? OR email LIKE ?");
    $likeTerm = "%" . $searchTerm . "%";
    $stmt->execute([$likeTerm, $likeTerm]);
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Testing and Monitoring

Security Testing

Test your application for SQL injection vulnerabilities:

php
// Test for SQL injection
function testSqlInjection($input) {
    $pdo = new PDO('mysql:host=localhost;dbname=test', 'test', 'test');
    
    // Test with single quote
    $testInput = $input . "'";
    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
    $stmt->execute([$testInput]);
    
    // Should return no results if secure
    return $stmt->rowCount() === 0;
}

Logging and Monitoring

Implement security logging:

php
function logSecurityEvent($eventType, $details, $userId = null) {
    $pdo = new PDO('mysql:host=localhost;dbname=security', 'logger', 'password');
    
    $stmt = $pdo->prepare("INSERT INTO security_logs (event_type, details, user_id, ip_address, user_agent) 
                          VALUES (?, ?, ?, ?, ?)");
    
    $stmt->execute([
        $eventType,
        $details,
        $userId,
        $_SERVER['REMOTE_ADDR'],
        $_SERVER['HTTP_USER_AGENT']
    ]);
}

Automated Security Scanning

Use tools like:

  • OWASP ZAP: Automated security testing
  • SQLMap: Dedicated SQL injection testing
  • SonarQube: Static code analysis
  • GitHub CodeQL: Automated vulnerability scanning

Sources

  1. OWASP SQL Injection Prevention Cheat Sheet
  2. PHP: Prepared Statements - Manual
  3. OWASP Top 10 2021 - A01:2021-Broken Access Control
  4. PHP Security Consortium - SQL Injection Prevention
  5. OWASP Testing Guide - SQL Injection Testing
  6. MySQLi Prepared Statements - PHP Manual
  7. PDO Security Best Practices

Conclusion

Preventing SQL injection in PHP applications requires a multi-layered security approach that combines several key techniques:

  1. Always use prepared statements with parameterized queries - This is the single most important defense against SQL injection attacks
  2. Implement strict input validation - Validate all user input according to expected formats and business rules
  3. Apply the principle of least privilege - Restrict database user permissions to only what’s necessary
  4. Use secure database connections - Always use proper character sets and secure connection methods
  5. Implement proper error handling - Never expose database error information to users
  6. Regular security testing - Continuously test your application for vulnerabilities

By following these best practices, you can effectively eliminate SQL injection vulnerabilities and protect your PHP applications from this dangerous attack vector. Remember that security is an ongoing process - regularly review and update your security measures to address new threats and vulnerabilities.

What are the differences between MySQLi and PDO for preventing SQL injection?How can I test my PHP application for SQL injection vulnerabilities?What are the most common SQL injection attack vectors in PHP applications?How do stored procedures help prevent SQL injection in PHP?What security headers should I implement alongside SQL injection prevention?How can I secure legacy PHP code that uses deprecated mysql_* functions?
Ask NeuroAgent