SQL Injection is one of the most common ways to attack a website. Using this method, hackers enter text containing SQL code into form inputs, hoping that the server’s code is poorly designed and susceptible to their trick. The purpose of this post is to show a specific example of how these kind of attacks could find success, and also how to guard against it.
The context for this example is an HTML Subscription Form. When the user submits their email and name, a post request will be sent to the server and a PHP script will attempt to enter this information into a MySQL database.
It’s worth noting that the possibility of SQL injection problems go down considerably if your project is built on a reputable framework (eg Laravel, Rails, Django, etc) and you stick to using the ORM. In this case, you may be able to avoid writing SQL queries all together.
In this example, I’ll be using PHP and it’s PDO class to connect to MySQL and write to it. Although PHP offers other APIs capable of doing the same thing, PDO comes with the advantage of being DBMS agnostic (ie it works with not only MySQL, but also Postgres, Oracle, etc), which could be handy if you want to change your project’s infrastructure down the road.
Now, onto the code. Here’s the form’s HTML:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Subscribe!</title>
</head>
<body>
<h1>Subscribe to our newsletter!</h1>
<form action="subscribe.php" method="POST">
<input name="email" type="email" placeholder="email" required>
<input name="name" type="text" placeholder="name" required>
<input type="submit" value="Submit">
</form>
</body>
</html>
And here’s the first attempt at the PHP controller responsible for interfacing with our database:
<?php
$servername = "127.0.0.1";
$username = "root";
$password = "root";
$db = "test";
$table = "users";
try {
$conn = new PDO("mysql:host=$servername;dbname={$db};", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE TABLE IF NOT EXISTS {$table} (email varchar(255), name varchar(255));";
$conn->exec($sql);
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
$email = $_POST['email'];
$name = $_POST['name'];
$sql = "INSERT INTO {$table} (email, name) VALUES ('{$email}', '{$name}');";
$conn->query($sql);
echo "<h3>hi, {$name}! Thanks for subscribing!</h3>";
Translated to english, subscribe.php is:
- Connecting to the database
- Creating a users table if one does not already exist
- Saving our form input into variables
- Using these variables to create an SQL statement intended to insert these values into a new row.
- Executing the SQL statement
- Providing confirmation to the user
This script will in fact work most of the time. So long as the user does not come with malicious intent and enters real emails and names into the form, all is well. However, assuming that all your users will come with good intent is a mistake!
In this attempt, user input is being put between quotes so that SQL will take them as literal values (line 25), but what happens when the user inserts quotes of their own? In that case, the literal string terminates and any further text will be executed as SQL. This vulnerability allows the attacker to write whatever SQL statements they want and execute them on our database!
For example, what would happen if this was put into the name field?
'); DELETE FROM users; INSERT INTO users (email, name) VALUES ('hax@gmail.com', 'Ha! Gotcha!
Originally, our database may have some users, like so:
But after inserting the above text as a name:
Whoops! And deleting users is only one example of bad things that can be done with SQL injection. Even worse situations exist, where multiple databases could be dropped, or users could fool your site into logging them in as another user (potentially an admin user). This is bad news, so how can it be prevented?
One way to neutralize this threat is using the PDO class’s prepare and bindParameter methods, which will escape any quotes it sees and ensure that user input does not leak into a SQL statement. Here’s what that looks like:
<?php
$servername = "127.0.0.1";
$username = "root";
$password = "root";
$db = "test";
$table = "users";
try {
$conn = new PDO("mysql:host=$servername;dbname={$db};", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE TABLE IF NOT EXISTS {$table} (email varchar(255), name varchar(255));";
$conn->exec($sql);
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
$email = $_POST['email'];
$name = $_POST['name'];
$sql = "INSERT INTO {$table} (email, name) VALUES (:email, :name);";
$statement = $conn->prepare($sql);
$statement->bindParam(':email', $email);
$statement->bindParam(':name', $name);
$statement->execute();
echo "<h3>hi, {$name}! Thanks for subscribing!</h3>";
Using this improved code in our subscribe controller will yield these results when injection is attempted:
Ha! This time the SQL code is entered as normal text, and our earlier entrees are preserved. Although this method of SQL execution is not necessary for all situations, it is a good way to deal with values that you can’t trust like user input. The code for this demo can also be seen at https://github.com/chris-geelhoed/sql-injection-example