SQL injection is one of the oldest and most damaging web vulnerabilities, and it still appears in PHP applications every year. It happens when a program puts user input straight into an SQL query, so that an attacker can change what the query does. This guide explains how SQL injection works in PHP, shows a vulnerable example and its fix, and covers the defences you should use in every project, including PDO, mysqli, CodeIgniter and WordPress.

Quick answer

  • SQL injection happens when user input is joined into an SQL string, such as “WHERE email = ‘$email’”.
  • The fix is to use prepared statements with bound parameters, so input is always treated as data and never as SQL.
  • Prepared statements cannot protect table and column names, or ORDER BY directions. Use an allowlist for those.
  • Also use a database user with the least privileges, hide database errors from visitors, and keep your software updated.

 

What is SQL injection?

A PHP page often builds an SQL query from what a visitor types, such as a login email or a search term. If the code joins that text into the query as it is, a visitor can type characters that change the structure of the query. The database cannot tell which part came from the developer and which part came from the attacker, so it runs all of it.

A successful SQL injection can let an attacker:

  • Read data that they should not see, such as user records and password hashes
  • Log in without a valid password
  • Change or delete data, if the database account has the rights
  • In some setups, go further and affect the server

 

A vulnerable example

The code below uses a small in-memory SQLite database, so that you can run it yourself. It has two users.

<?php
$pdo = new PDO('sqlite::memory:', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, role TEXT)');
$pdo->exec("INSERT INTO users (email, role) VALUES ('asha@example.com', 'user'), ('admin@example.com', 'admin')");

// VULNERABLE: the input is joined into the query
$email = 'asha@example.com';
$sql = "SELECT * FROM users WHERE email = '$email'";
echo $sql . "\n";
echo count($pdo->query($sql)->fetchAll()) . " row(s)\n";

Output:

SELECT * FROM users WHERE email = 'asha@example.com'
1 row(s)

With normal input, the code works. Now look at what happens when the visitor types a value that contains a quote:

$email = "' OR '1'='1";
$sql = "SELECT * FROM users WHERE email = '$email'";
echo $sql . "\n";
echo count($pdo->query($sql)->fetchAll()) . " row(s)\n";

Output:

SELECT * FROM users WHERE email = '' OR '1'='1'
2 row(s)

The input closed the string and added a condition that is always true. The query now returns every user instead of one. If this were a login form, the attacker could get in without knowing a password.

Even an innocent quote breaks the code. A visitor whose name is O’Brien would cause an error:

$e = "o'brien@example.com";
$pdo->query("SELECT * FROM users WHERE email = '$e'");
Error: SQLSTATE[HY000]: General error: 1 near "brien": syntax error

The exact message depends on your database. If your site shows messages like this to visitors, it also tells attackers about your database.

 

The fix: prepared statements

A prepared statement sends the SQL structure and the data separately. The database parses the query first, and the values are only filled in as data. Input can never change the structure.

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');

$stmt->execute(['email' => "' OR '1'='1"]);
echo count($stmt->fetchAll()) . " row(s)\n";

$stmt->execute(['email' => 'asha@example.com']);
print_r($stmt->fetchAll());

Output:

0 row(s)
Array
(
    [0] => Array
        (
            [id] => 1
            [email] => asha@example.com
            [role] => user
        )

)

The attack text is now just a string that is compared with the email column, and it matches nobody. The normal email still works.

Storing dangerous text is safe as well. This insert saves the text as it is, and nothing is dropped:

$stmt = $pdo->prepare('INSERT INTO users (email, role) VALUES (:e, :r)');
$stmt->execute(['e' => "x'); DROP TABLE users;--", 'r' => 'user']);

echo $pdo->query('SELECT COUNT(*) c FROM users')->fetch()['c'] . " users\n";
3 users

Use placeholders in different ways

Positional placeholders (?):

$stmt = $pdo->prepare('SELECT * FROM users WHERE role = ? AND id > ?');
$stmt->execute(['user', 0]);

A list of values with IN: build the right number of placeholders, and pass the values.

$ids = [1, 2];
$placeholders = implode(',', array_fill(0, count($ids), '?'));

$stmt = $pdo->prepare("SELECT email FROM users WHERE id IN ($placeholders)");
$stmt->execute($ids);
echo json_encode($stmt->fetchAll());
[{"email":"asha@example.com"},{"email":"admin@example.com"}]

LIKE searches: add the % signs to the value, not to the SQL.

$stmt = $pdo->prepare('SELECT email FROM users WHERE email LIKE :q');
$stmt->execute(['q' => '%admin%']);

A safe PDO connection for MySQL

$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

Setting ATTR_EMULATE_PREPARES to false makes MySQL use real prepared statements. ERRMODE_EXCEPTION makes errors throw exceptions, which you can log instead of showing to visitors.

Prepared statements with mysqli

$stmt = $mysqli->prepare('SELECT id, email FROM users WHERE email = ?');
$stmt->bind_param('s', $email);
$stmt->execute();
$result = $stmt->get_result();

The letter s means that the value is a string. Use i for integers and d for decimals. get_result() needs the mysqlnd driver, which is the default in modern PHP.

 

What prepared statements cannot protect

Placeholders can only stand in for values. They cannot replace table names, column names or keywords such as ASC and DESC. If you let users choose a sort column, use an allowlist:

$allowed = ['email', 'id'];
$sort = $_GET['sort'] ?? 'id';
$column = in_array($sort, $allowed, true) ? $sort : 'id';

$sql = "SELECT * FROM users ORDER BY $column";

If the visitor sends a column that is not in the list, the code falls back to a safe default.

For numbers, validate the type before you use them:

var_dump(filter_var('5 OR 1=1', FILTER_VALIDATE_INT));
var_dump(filter_var('5', FILTER_VALIDATE_INT));
bool(false)
int(5)

Do not rely on escaping or filtering alone

Functions such as addslashes() and mysqli_real_escape_string() try to escape dangerous characters. They are easy to use wrongly (for example, around numbers without quotes, or with the wrong character set), so they are not a replacement for prepared statements. Blocking words such as SELECT or DROP in the input does not work either, because attackers find ways around the filter, and legitimate users may need those words.

 

Frameworks and CMS

CodeIgniter 4. The Query Builder escapes values for you, and you can bind values in raw queries too:

$user = $db->table('users')->where('email', $email)->get()->getRowArray();

$query = $db->query('SELECT * FROM users WHERE email = ?', [$email]);

The danger is in raw SQL strings that you build by hand. Never join input into them.

WordPress. Use $wpdb->prepare():

$row = $wpdb->get_row(
    $wpdb->prepare("SELECT * FROM {$wpdb->users} WHERE user_email = %s", $email)
);

Laravel and other ORMs. Eloquent and the query builder use bindings, but raw expressions such as whereRaw() and DB::raw() need bindings too.

 

More defences

  1. Least privilege. Give your web application a database user that can only do what it needs, usually SELECT, INSERT, UPDATE and DELETE on its own database. It should not be able to drop tables or read other databases.
  2. Hide error details. Show a generic error to visitors, and write the details to a log. In php.ini, set display_errors = Off and log_errors = On.
  3. Validate input. Check type, length and format. This is good practice, but it is not a substitute for prepared statements.
  4. Update your software. SQL injection flaws are often found in plugins and old scripts, so keep your CMS, plugins and libraries updated, and remove the ones you do not use.
  5. Add a WAF as an extra layer. A WAF can block many attacks, but it can be bypassed, so it does not replace safe code.
  6. Watch your logs for repeated requests that contain quotes, comment markers or unusual SQL words.
  7. Test. Review your code for queries built with string joining, and use a security scanner on a test copy of your site.

 

Second-order injection

Some attacks do not run immediately. The attacker stores a malicious value, for example in a profile name, and the application uses it unsafely in a later query. So treat all data from the database as untrusted too, and use prepared statements everywhere, not only for form input.

Quick checklist

  • Every query that uses outside data uses prepared statements
  • Table names, column names and sort directions come from an allowlist
  • The database user has the least privileges
  • Errors are logged, and not shown to visitors
  • Plugins, themes and libraries are updated
  • You searched your code for queries built with string joining

 

FAQs

What is SQL injection in simple words?

It is an attack where someone types SQL code into a form or URL, and the website runs it as part of its own database query because the code did not separate data from commands.

Is PDO safer than mysqli?

Both are safe when you use prepared statements. PDO works with many databases and has simpler named placeholders, which many developers prefer.

Does mysqli_real_escape_string() prevent SQL injection?

Not reliably. It helps only in some cases, and it is easy to use incorrectly. Use prepared statements.

Can SQL injection happen with numbers?

Yes. If you put a number from the URL into a query without quotes and without validation, an attacker can add SQL after it. Validate numbers and bind them as parameters.

How do I test my site for SQL injection?

Review your code for string-joined queries, and test on a copy of your site with a security scanner. Do not test on websites that you do not own, because that is illegal.

PreviousNext