Prevent SQL injection attacks with prepared statements

Bit of a hot topic lately in my inbox.

Here’s what an old, vulnerable query might look like:

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";
$result = $mysqli->query($query);

The problem here is we’re injecting user-submitted data directly into our SQL statement without any sort of escaping or validation. So, a hacker could enter something like this in our form:

1; DROP TABLE users;

Changing our full query to:

SELECT * FROM users where id=1; DROP TABLE users;

Which, as you can probably see, will execute the SELECT statement but then drop our users table. No bueno. And, that’s a simple example. SQL injection attacks can be used to do all sorts of things: getting passwords, gaining privileges, making superusers… and all sorts of stuff.

Luckily, there’s an easy way to prevent this class of SQL injection:

Prepared statements.

Prepared statements split the query from the data so that the data submitted can’t be used to alter how the query is run; thus preventing injection attacks. Here’s an example of how our code would change:

$expected_data = 1;
$stmt = $mysqli->prepare("SELECT * FROM users where id=?");
$stmt->bind_param("d", $expected_data);
$stmt->execute();
$result = $stmt->get_result();

Notice how we separated the data from the query. We send the query to the server first and then we bind the data to that. This prevents the submitted data from altering the query and letting the hacker in.

This is how you should write your queries.

Now, if this stuff is new to you or you don’t feel like you quite fully get it yet, then I recommend diving into it. One good way to learn everything PHP is with this tutorial. You’ll learn prepared statements… but also OOP, all of MySQL with PHP, $_GET, $_POST, working with files, folders, if/else/switch… all of it. Plus, you build a fully-functioning CMS so you learn how to put it all together and build something real.

No brainer, IMNSHO: http://www.johnmorrisonline.com/phpcourse

Later,

John “Don’t Hack Me Bro” Morris

Originally published at John Morris.