MySQL statements using MySqli and PHP7. How to migrate to prepared statements?

Question

I am redesigning a PHP/MySql site that was about 7 years old and had lots of MySql statements. I had updated them all to use MySqli, but have recently heard about "prepared statements", so now I'm wondering if I should take the time to change all the MySqli statements to use prepared statements.

For example, the majority of mysql statements in the current code follow this general format...

$sql = "SELECT * FROM users WHERE userid = '$userid'"; $getnotifyoption = mysqli_query($connection, $sql); if (!$getnotifyoption ) { die("Database query failed: " . mysqli_error()); } else { while ($row = mysqli_fetch_array($getnotifyoption )) { $firstname=$row['firstname']; //etc } } 

That's a SELECT obviously, but I also have lots of INSERT statements that follow the same general format. And if I was inserting a first name into the database, I would first do this to it...

$firstname=mysqli_real_escape_string($connection,$_POST['firstname']); 

From my recent reading, it seems like that a good thing to do, but definitely not as safe from SQL injection as it could/should be.

Now, I'm reading about prepared statements and struggling to fully grasp them (if anyone has any good tutorials for somewhat newbies, please share a link). And if anyone's feeling generous, let me know how the example statement I gave earlier would be re-written using a prepared statement. But other than that...

Do I need to install something to use prepared statements, or would they just work within my mysqli/php7 environment?

More importantly, the feedback I'm looking for is whether you think I should definitely add what I think would be significant time to the redesign and go back and change ALL of my MySqli statements to use prepared statements?

How much more efficient/safe are prepared statements than the format I use above?

Is my way TERRIBLE, or is it OK to use for now?

Will my way eventually not work? If so, are we talking a year or like 10 years? (i know probably can't answer that :) )

Answer 1

Dynamically generating SQL statements by interpolating variables is frowned upon, because it is too easy to forget to escape those variables first. So your current way of executing SQL is very error prone. You only need to forget escaping once to open your website up to SQL injection attacks.

Prepared statements are like defining a function that you can call at a later point. You don't dynamically generate the code, but you just provide an array of parameters. The primary benefit is that you don't have to bother with escaping – the database driver is now responsible for handling the data safely. Additionally, you avoid parsing the same SQL statement again and again: prepared statements are reused, which can improve performance.

PHP has two options for prepared statements:

• The mysqli interface has $stmt = mysqli_prepare($mysqli, "SQL") and $stmt = $mysqli->prepare("SQL") functions. The SQL string can contain ? as placeholders for value that you can later bind to the statement before executing it. This works, but is a bit annoying.

• The PDO interface has a $stmt = $pdo->prepare("SQL") function. The SQL string can contain named :placeholders. When exectuting a prepared statement, you must provide a name-value array for the parameter values. Using PDO is the preferred way to access databases from PHP.

For details and examples, please read the appropriate sections in the PHP manual.

So is updating your code to use prepared statements worth it?

The potentially improved security from using prepared statements may or may not have sufficient business value. This is basically a check whether cost of breach * (probability of breach before fixing - probability of breach after fixing) > cost of fixing. But since this involves “unknown unknowns” this is basically impossible to estimate. Another way to look at this is whether the cost of a breach could be business-critical, e.g. through loss of public image, regulatory response, or legal defense costs. If so, investing in good security practices seems wise.

If the application is under active maintenance and you are already familiar with the code, the cost of moving to prepared statements is very low, probably on the order of 5 minutes per statement. With a bit more effort, this can be used at the same time to improve the architecture of the code, e.g. by introducing a clean data access layer that concentrates all DB interaction.