Prevent SQL injection attacks with prepared statements

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);
$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.

Source: John Morris