When to use prepared statements in PHP Mysqli? - User form search input vs select queries

Question

I am trying to understand when I should use prepared statements in php/mysqli. Should every php/mysqli query use prepared statements or just queries and instances where user input is involved ... such as an html form that asks a user to enter data to search within a database?

I am migrating my old php5/mysql code to php7/mysqli. I have many php files that query a mysql db. I would like clarification if I need to use prepared statements for every php file that connects to a mysql db ... for example php files that are referenced via "php require" and include simple sql select statements to render images and links to a html page?

<?php

//establish connection

$con = new mysqli('localhost','uid','pw','db');

//check connection

if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);  
}

//search variable that stores user input

$search = "%{$_POST['search']}%";

//prepare, bind and fetch

$stmt = $con->prepare("SELECT image, caption FROM `tblimages`
WHERE catid = 3 AND caption LIKE ? order by caption ASC");
$stmt->bind_param("s", $search);
$stmt->execute();
$stmt->bind_result($image,$caption);

while ($stmt->fetch()) {
echo "{$image} <br> {$caption} <br>";    
}

$stmt->close();

//close database connection

mysqli_close($con);

?>

The code above works and is the first I've ever used prepared statements. It takes user input from a form (blank box to enter a search term - POST) and searches a db ... then renders results to an html page. This seems like a logical use of prepared statements. However ... I have other php files where users select data from a drop down box in a form to render a result (the user does not enter data into a search box like above). Do I use prepared statements for that instance as well? Plus do I use prepared statements for php files that are referenced via "php require" and include simple sql select statements to render images and links to a html page? I've yet to find clarification of the specific instances to use prepared statements to prevent sql injections. Any clarification or references welcome.

Answer 1

Short answer: Always use prepared statements.

Long answer:

Prepared statements separate your data from SQL commands. They are provided by PDO or by MySQLi. Their biggest advantage is that it is impossible to have SQL injection if your data is treated as data. Another advantage is that you can execute the same query over and over again with different set of data, which might be better for your performance and often keeps your code cleaner.

However, there are times when you would like to have some kind of dynamic query based on user's selection or actions. As you probably know table and column names are not data, but part of SQL query, therefore you can't keep them separated. The alternative to prepared statements then is to have a white list of possible values and only allow user input validated against the white list.

You might ask what are query, real_query, multi_query and PDO::exec good for?
As the PHP Manual shows they are good at times when you only need to execute constant query without any variables or when you have a query which can't be prepared. e.g.

$mysqli->query('SELECT Name FROM City LIMIT 10');
$pdo->exec('DELETE FROM fruit');
$mysqli->multi_query('DELETE FROM fruit; DELETE FROM pets;');

What if you know the type and values of your data? Should you also prepare/bind?
Yes! Get into a habit of binding all data going with SQL query. There is no reason to make exceptions. It is much more difficult to trace those exceptions in your code and always be sure you do not overwrite the "safe" value with some unknown input.

If you are still not sure how to use prepared statements or you think that they are too complicated (they are not) you can take a look at an amazing PHP tutorial at https://phpdelusions.net

Answer 2

This is how MySQLi prepared statements work in PHP:

Prepare an SQL query with empty values as placeholders (with a question mark for each value). Bind variables to the placeholders by stating each variable, along with its type. Execute query. The four variable types allowed:

i - Integer

d - Double

s - String

b - Blob

A prepared statement, as its name implies, is a way of preparing the MySQL call, without storing the variables. You tell it that variables will go there eventually — just not yet. The best way to demonstrate it is by example.

$stmt = $mysqli->prepare("SELECT * FROM myTable WHERE name = ? AND age = ?");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
//fetching result would go here, but will be covered later
$stmt->close();

If you've never seen prepared statements before, this may look a little weird. Basically what's happening is that you are creating a template for what the SQL statement will be. In this case, we are selecting everything from myTable, where name and age equal ?. The question mark is just a placeholder for where the values will go.

The bind_param() method is where you attach variables to the dummy values in the prepared template. Notice how there are two letters in quotes before the variables. This tells the database the variable types. The s specifies that name will be a string value, while the i forces age to be an integer. This is precisely why I didn't add quotation marks around the question mark for name, like I normally would for a string in an SQL call. You probably thought I just forgot to, but the reality is that there is simply no need to (In fact, it actually won't work if you do put quotes around the ?, since it will be treated as a string literal, rather than a dummy placeholder.).

You are already telling it that it will be a string literal when you call bind_param(), so even if a malicious user tries to insert SQL into your user inputs, it will still be treated as a string. $stmt->execute() then actually runs the code; the last line simply closes the prepared statement. We will cover fetching results in the Select section.