MENU


Injection Attacks

By submitting carefully devised input to a poorly designed system, an attacker can trick a server script into executing SQL commands that a client was never meant to run. Open-source software can be especially vulnerable, because its database architecture is publicly known. No form of client input should ever be trusted — not a select box, not a hidden field, not a cookie.

To prevent injection attacks, a web developer should properly validate all input received from the client. Prepared statements with parameterized queries are particularly effective, because they compile the runnable parts of a query beforehand and accept input purely as data that cannot be executed — they also improve the efficiency of repeated SQL commands, since the statement need not be reparsed on every execution. Limiting the privileges granted to casual visitors is an additional layer of defense; see Privileges.

Hacking Techniques

Each example below shows server-side code that interpolates unvalidated input directly into a query string, the injected input an attacker supplies, and the resulting command actually executed.
Server code: SELECT * FROM users WHERE userid = $input
Injected $input: 99 OR TRUE
Executed: SELECT * FROM users WHERE userid = 99 OR TRUE

The WHERE clause always evaluates to TRUE, which may let the attacker read every row — including other users' passwords.

Server code: SELECT * FROM users WHERE username = '$input'
Injected $input: ' OR TRUE --
Executed: SELECT * FROM users WHERE username = '' OR TRUE -- '

Ending the string immediately with a single quote and commenting out the remainder of the query is one of the most common injection patterns.

Server code: SELECT id, name, dt, size FROM products WHERE size='$size'
Injected $size: ' UNION SELECT '1', CONCAT(uname,'-',passwd), '2000-01-01', '0' FROM users --
Executed: SELECT id, name, dt, size FROM products WHERE size=''
UNION SELECT '1', CONCAT(uname,'-',passwd), '2000-01-01', '0' FROM users --'

A UNION-based injection can extract usernames and passwords even when the users table is never referenced directly by the vulnerable query.

Server code: UPDATE usertable SET password='$pwd' WHERE username='$uid'
Injected $pwd: xxx — Injected $uid: ' OR uid LIKE '%admin%
Executed: UPDATE usertable SET password='xxx' WHERE username='' OR uid LIKE '%admin%'

This overwrites the administrator's password.

Server code: UPDATE usertable SET password='$pwd' WHERE username='$uid'
Injected $pwd: xxx', trusted=100, admin='yes
Executed: UPDATE usertable SET password='xxx', trusted=100, admin='yes' WHERE username='...'

Injecting extra column assignments illegally grants elevated privileges to an account.

Server code: SELECT * FROM products WHERE id LIKE '%$prod%'
Injected $prod: a%' exec master..xp_cmdshell 'net user test testpass /ADD' --
Executed: SELECT * FROM products WHERE id LIKE '%a%' exec master..xp_cmdshell 'net user test testpass /ADD' -- %'

On a system that allows it (historically SQL Server), this gives the attacker a foothold on the machine hosting the database.

Server code: SELECT * FROM users WHERE userid = $input
Injected $input: 99; DROP TABLE suppliers
Executed: SELECT * FROM users WHERE userid = 99; DROP TABLE suppliers

Illegally deletes a table. This works only on drivers that allow multiple batched statements per query — many PHP MySQL APIs allow only one statement per query by default.

Server code: SELECT id, name FROM products ORDER BY name LIMIT 30 OFFSET $offset
Injected $offset: 0; INSERT INTO pg_shadow(usename,usesysid,usesuper,usecatupd,passwd) SELECT 'crack', usesysid, 't', 't', 'crack' FROM pg_shadow WHERE usename='postgres'; --

On a system permitting batched statements, this grants the attacker superuser access by injecting a fabricated row into a privileged system table.

Validating Input

Simplistic preventive measures include converting input to a number when a number is expected, and escaping single quotes when a string is expected. Three PHP functions are useful here: These measures reduce risk but do not prevent every form of injection attack — prepared statements below are the reliable defense.

MySQL Prepared Statements

PREPARE ps_name FROM preparable_stmt
EXECUTE ps_name [USING @var1 [,@var2]...]
{DEALLOCATE | DROP} PREPARE ps_name
A prepared statement is deallocated automatically at the end of a session and is not shared with other sessions. Prepared statements cannot be nested or contain multiple statements. One constructed inside a stored routine is not cleared when the routine exits, and consequently cannot refer to the routine's parameters or local variables. A question mark ? is used as a parameter marker in preparable_stmt.
Prepared statements resist SQL injection because parameter values, transmitted later using a different protocol, need not be escaped at all — incoming data is treated purely as data, with no way for it to be interpreted as part of the SQL text. Most statement types are preparable, including ALTER TABLE, CALL, DELETE, GRANT, INSERT, REPLACE, SELECT, SET, {CREATE|DROP} {INDEX|TABLE|VIEW|DATABASE|USER}, and the various SHOW statements; notable exceptions include statements that would themselves prepare, execute, or deallocate a statement. MySQL allows a statement to be prepared once and executed many times with different parameter values, without the need for repreparation, reducing the overhead of repeated execution.

PHP Prepared Statements

mysqli::prepare($s) or mysqli_prepare($l, $s) prepares a single query $s and returns a mysqli_stmt object. mysqli::stmt_init() or mysqli_stmt_init($l) initializes a prepared statement suitable for mysqli_stmt_prepare(). The methods below belong to the mysqli_stmt class; each has an equivalent procedural form obtained by prepending mysqli_stmt_ to the method name and passing the statement object as the first argument — for example, the procedural form of PS->prepare($s) is mysqli_stmt_prepare($stmt, $s). Unless noted otherwise, these functions return true on success or false on failure.

PDO

PHP Data Objects (PDO) defines a lightweight, consistent interface for accessing databases from PHP, supporting both direct execution (exec(), query()) and prepared statements with either positional (?) or named (:name) placeholders. Values can be bound by value with bindValue() or by reference with bindParam(); result columns can be bound with bindColumn() and retrieved with PDO::FETCH_BOUND.

Possible values for the first parameter of fetch() include PDO::FETCH_ASSOC (array indexed by column name), PDO::FETCH_BOTH (indexed by both name and number), PDO::FETCH_BOUND (assigns values to variables bound with bindColumn()), PDO::FETCH_CLASS (an object of a requested class), PDO::FETCH_INTO (updates an existing object), PDO::FETCH_NAMED (like FETCH_ASSOC, but a repeated column name yields an array of values), PDO::FETCH_NUM (indexed by column number from 0), and PDO::FETCH_OBJ (an object whose property names match the column names). The third parameter of bindParam()/bindValue() accepts PDO::PARAM_BOOL, PDO::PARAM_NULL, PDO::PARAM_INT, PDO::PARAM_STR, or PDO::PARAM_LOB. 

<?php
// Example 1: force a numeric type
$input = "99 OR TRUE";
settype($input, "int");
echo $input . "\n"; // 99

// Example 2: escape special characters before use in SQL
$input = "' OR TRUE --";
$link = mysqli_connect("localhost", "root", "passwd", "testDB");
echo mysqli_real_escape_string($link, $input) . "\n"; // \' OR TRUE --

// Example 3: swap single quotes for a safe placeholder and back
$size = "' UNION SELECT '1', CONCAT(uname,'-',passwd), '2000-01-01', '0' FROM users -- ";
$size = str_replace("'", "--SQ--", $size);
echo $size . "\n";
$size = str_replace("--SQ--", "'", $size);
echo $size;
?>

PREPARE ps FROM 'SELECT ? + ?;';
SET @a = 1, @b = 2;
EXECUTE ps USING @a, @b;
DEALLOCATE PREPARE ps;

PREPARE ps FROM 'SELECT ? + ?;';
SET @a = 1, @b = 2;
EXECUTE ps USING @a, @b;

? + ?
3

<?php
// 1. Connect and create a table
$S = mysqli_connect("localhost", "root", "password", "testDB");
$S->query("DROP TABLE IF EXISTS tbl");
$S->query("CREATE TABLE tbl (a INT, b DOUBLE, c VARCHAR(10), d BLOB)");

// 2. Insert rows with a prepared statement
$PS = $S->prepare("INSERT INTO tbl VALUES (?,?,?,?)");
$PS->bind_param("idsb", $i, $d, $s, $b); // i=int, d=double, s=string, b=blob
$i = 100; $d = 3.14; $s = "Hello"; $b = "Some long text";
$PS->execute();

// 3. Read rows back
$PS->prepare("SELECT a,b FROM tbl");
$PS->execute();
$PS->bind_result($i, $d);
while ($PS->fetch()) {
    echo "$i,$d\n";
}

echo "num_rows:" . $PS->num_rows . "\n";
echo "affected_rows:" . $PS->affected_rows . "\n";
echo "insert_id:" . $PS->insert_id . "\n";

$PS->free_result();
$PS->close();
?>

<?php
$P = new PDO("mysql:dbname=test;host=localhost", "root", "password");
$P->exec("DROP TABLE IF EXISTS tbl;");
$P->exec("CREATE TABLE tbl (a INT, b VARCHAR(5))");

// Style 1: direct exec()
$P->exec("INSERT INTO tbl VALUES (1,'abc')");

// Style 2: prepare() with named placeholders
$PS = $P->prepare("INSERT INTO tbl VALUES (:a,:b)");
$PS->execute([":a" => 4, ":b" => "jkl"]);

// Style 3: bindValue() / bindParam()
$PS = $P->prepare("INSERT INTO tbl VALUES (:a,:b)");
$PS->bindValue(":a", 6, PDO::PARAM_INT);   // by value
$PS->bindParam(":b", $b, PDO::PARAM_STR, 3); // by reference
$b = "pqr";
$PS->execute();

// Style 4: transactions
$P->beginTransaction();
$P->exec("INSERT INTO tbl VALUES(9,'yz')");
$P->commit();

// Bound output columns
$PS = $P->prepare("SELECT a,b FROM tbl WHERE a > ? AND a < ?");
$PS->execute([0, 10]);
$PS->bindColumn(1, $a);
$PS->bindColumn('b', $b);
while ($PS->fetch(PDO::FETCH_BOUND)) {
    echo "$a,$b\n";
}
?>