SQL Injection, often abbreviated as SQLi, is a type of attack where the attacker can run malicious SQL code on a website’s database. Think of it as “tricking” the website into giving out information it shouldn’t.
When a website asks for information, like your username and password, it’s using SQL to check that information in its database. If the website isn’t protected well, an attacker can enter SQL commands instead of regular information, which the website then runs unknowingly.
Imagine a website’s login form.
The code behind might look for usernames and passwords like this:
SELECT * FROM users WHERE username='ENTERED_USERNAME' AND password='ENTERED_PASSWORD';
An attacker could enter anything’ OR ‘1’=’1 as the username.
This would change the code to:
SELECT * FROM users WHERE username='anything' OR '1'='1' AND password='ENTERED_PASSWORD';
Because 1=1 is always true, the attacker could log in without knowing the actual password.
This is a bad example because it directly includes user input in the SQL query:
<?php
$pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
$result = $pdo->query($query);
if ($result->rowCount() > 0) {
echo "Login successful!";
} else {
echo "Invalid credentials!";
}
?>
An attacker could provide the following as the username: admin’ OR ‘1’=’1 causing all rows from the users table to be selected, likely bypassing the login.
The query would look like this:
$query = "SELECT * FROM users WHERE username='admin' or '1'='1' AND password='$password'";
If a website is vulnerable to SQLi, attackers can:
Based on how they’re carried out,
This is the most common type. It happens when user input is directly included in SQL queries without validation or escaping.
e.g. Entering OR ‘1’=’1′ into a vulnerable login form might grant access without needing a correct password.
Instead of getting a useful error message or data output, the attacker determines if the hypothesis is true based on other clues like the response time or a change in application behavior.
This uses the UNION SQL operator to combine the results of the original query with results from one or more additional queries. This can be used to retrieve data from other tables.
e.g. An attacker might use UNION SELECT username, password FROM users to extract credentials from a user’s table.
Instead of using the same channel (e.g., HTTP) for both attack and data retrieval, the attacker uses a different channel (like DNS or HTTP requests) to exfiltrate data.
e.g. This method is beneficial when the server’s responses don’t display data but it’s still vulnerable to injection.
Attackers deliberately cause errors in SQL queries to force the database to reveal information in error messages.
e.g. Input like ‘ might cause a SQL error. If the error message shows database details, attackers gain insights about its structure.
Exploits stored procedures (pre-saved SQL statements in a database). Some stored procedures might be misconfigured or written without security in mind.
e.g. If an attacker identifies a vulnerable stored procedure, they can inject malicious SQL code into it.
This is a way of writing SQL code that allows the website to tell the difference between code and data.
The following example uses prepared statements to safely pass user input to the database:
<?php
$pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username=:username AND password=:password";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':password', $password, PDO::PARAM_STR);
$stmt->execute();
if ($stmt->rowCount() > 0) {
echo "Login successful!";
} else {
echo "Invalid credentials!";
}
?>
By using bindParam(), the user input is treated as data and not executable SQL code, ensuring that any injected SQL code won’t be executed.
These are set SQL commands that the database knows in advance, reducing the chances of running bad commands.
Stored procedures are pre-written SQL statements stored in the database itself. They can be executed without having to send the query directly from the application. This is one way to mitigate SQL injection, as the SQL code is predefined and parameters are passed in, rather than constructing the SQL with string concatenation.
The following example uses stored procedures.
Creating a stored procedure:
CREATE PROCEDURE GetUserByUsername(IN user_name VARCHAR(255))
BEGIN
SELECT * FROM users WHERE username = user_name;
END
The parameters are directly passed on to the stored procedure in the code:
<?php
$pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
$username = $_POST['username'];
$stmt = $pdo->prepare("CALL GetUserByUsername(:username)");
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->execute();
$userData = $stmt->fetchAll();
print_r($userData);
?>
We can also escape user input using the PDO’s quote method. This method adds quotes around the input and escapes special characters.
<?php
$pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
$username = $pdo->quote($_POST['username']);
$password = $pdo->quote($_POST['password']);
$query = "SELECT * FROM users WHERE username=$username AND password=$password";
$result = $pdo->query($query);
if ($result->rowCount() > 0) {
echo "Login successful!";
} else {
echo "Invalid credentials!";
}
?>
Note: While the quote() method can help against SQL injection, it's not always foolproof. Prepared statements are generally the more recommended method. Also, for password handling, never store or compare raw passwords. Always use proper hashing techniques (e.g., bcrypt).