📖 PHP Prepared Statements

PHP has a new, more secure way of interacting with the database to discourage SQL injections. SQL injections are a form of hacking where a bad actor tries to invade your database for the purpose of stealing, manipulating or destroying your site data. PHP Prepared Statements provide a better way to send your MySQL queries to help reduce the effects of these attacks.

Like other PHP built-in functions, there are procedural and OOP versions of this code. We will be using the OOP version as both are equally difficult, they take no special knowledge of OOP to use them and they are good to know. They have several steps to employ which is the most confusing part of this, but the pattern is much them same. They work by sending the server a prepared query request notifying the server a database query is coming and then in a separate message, they send the parameters for the query. Since the query parameters are sent separately from the query, highjacking the query becomes more difficult using information from a form input.

There are 4 SQL prepared statements (queries) we will need to run. One of them, the SELECT query, has some variations in use. We'll take a look at 2 cases. Let's review them in the order you would typically use them in a script and in their order of complexity.

  • DELETE
  • UPDATE
  • INSERT
  • SELECT (2 cases)

DELETE

The delete statement is the most straight forward. It uses a simple query just like you've used before with only 1 optional, but important, condition. Which record do you want to delete?

Example
$stmt = $conn->stmt_init(); // initialize a db connection and assign it to a variable
if ($stmt->prepare("DELETE FROM `membership` WHERE `memberID` = ?")){ // send the prepared statement for review by the server
	$stmt->bind_param("i", $memberID); // send the bind information to be inserted into the query
	$stmt->execute(); // execute the script (query)
	$stmt->close(); // close the connection
}

The format you see above is almost identical for the INSERT and UPDATE queries, as well. The queries are written the same as other MySQL queries except there is that funny looking question mark (?) at the end. The question mark is a placeholder for information to be fed into the query from a form or other coding source. Because the query is prepared and sent first, the server will only fill in the ? with the information sent in the next message. This defeats the efforts to break a typical query to inject an external SQL command.

There are some other interesting parts in the code. The first line is simply to make a database connection. Notice we are using the same connection information we used in our traditional queries. The if statement just ensures we have a connection before we continue sending the query. If the server receives a properly formatted prepared statement from the if statement, it will return true and the code will continue. If not, it will return an error.

Once the server accespts the prepared statement (query) the rest of the code is sent. The bind_param("i", $memberID) tells the server the value(s) to insert into the ? placeholder(s). There can be more than one, but must have at least one. The i tells the server the data type is an integer. You can also have an s for string data type, but they must match the data in the bind parameter.

The last two commands execute the script and close the connection.

Here are some things to remember about prepared statements.

  • The number, order and type of i's and s's must match and the bind parameters.
  • The number and order of bind parameters must match the ? in the query.
  • The ? can only represent the data part of the query, not the database elements.
  • You should always use standard MySQL queries.

UPDATE

The UPDATE prepared statement is very similar to the DELETE statement. In both cases we are attempting to edit (or delete) an existing record(s) in the database. We need to tell the database which record(s) need to be updated.

Example
$stmt = $conn->stmt_init();
if ($stmt->prepare("UPDATE `membership` SET `firstname` = ?, `lastname` = ?, `username` = ?, `email` = ? WHERE `memberID`= ?")) {
	$stmt->bind_param("ssssi", $firstname, $lastname, $username, $email, $memberID);
	$stmt->execute();
	$stmt->close();
}

As you can see, the code is very similar to the DELETE statement. The difference is we need to include the information that is being updated in the query which means we have several question marks (?) in the prepared statement to hold a place for the information. There are matching data type indicators and binding parameters. Other than these differences, the code is much the same.

INSERT

As you might expect, the INSERT statement is much the same with only some changes to the query structure. We don't need to include the default fields since we defined the fields for the database to use. Since this is an INSERT query, the database will create a new unique primary key for the record. The queries still follow all the MySQL rules for queries. The prepared statements only improve it's integrity.

Example
$stmt = $conn->stmt_init();
if ($stmt->prepare("INSERT INTO `membership` (`firstname`, `lastname`, `username`, `email`, `password`) VALUES (?, ?, ?, ?, ?)")) {
	$stmt->bind_param("sssss", $firstname, $lastname, $username, $email, $password);
	$stmt->execute();
	$stmt->close();
}

SELECT

Case 1

The SELECT statement is a little more involved than the previous ones. The two cases I mentioned earlier are whether you are only getting a stand-alone list or if you need to store the data from the query for additional use by the program. We'll start with the easiest case where we are going to employ the data immediately and then release the results.

Example
$stmt = $conn->stmt_init();
if ($stmt->prepare("SELECT `firstname`, `lastname` FROM `membership` WHERE `memberID` = ?")) {
	$stmt->bind_param("i", $memberID);
	$stmt->execute();
	$stmt->bind_result($firstname, $lastname);
	$stmt->fetch();
	$stmt->close();
}

At first glance the SELECT statement looks like the others, but there are 2 new steps. After we execute the query, we need to collect the data being returned from the query. After all, that's why we use the SELECT statement; to retrieve data from the database. We first define the variables by name to assign the results to. These must be in the same order as the query. In this query we are getting firstname and then lastname, so the bind_result must be in that order. Now, you can set the name of the variable(s) to anything you wish. We could use $myFirstName if we wanted. Whatever you choose is what will be used later in our code to put this information to use. After we assign the data to variables we fetch() the data from the result and close the connection.

Case 2

The last SELECT statement will wil review is one where you want the results to be avaibale after you close the connection. I know it sounds like we did that in the last SELECT statement, but not really. In that one we assigned the result values to variables for later use and then dumped the result set. In this case we are going to store the result for more processing. Often these kinds of SELECT statements are used to select rows of data then loop through them building lists, arrays or to run nested queries. Let's look at an example.

In this example, we are going to get a list of members from the membership table where the members are all have the same last name. This query could be conditioned on many other attributes like where they live, if they have outstanding orders or maybe belong to the same group or class. This is just an example.

Example
$stmt = $conn->stmt_init();
if ($stmt->prepare("SELECT `firstname`, `email` FROM `membership` WHERE `lastname` = ?")) {
	$stmt->bind_param("s", $lastname);
	$stmt->execute();
	$stmt->bind_result($firstname, $email);
	$stmt->store_result();
	$classList_row_cnt = $stmt->num_rows();

	if($classList_row_cnt > 0){ // make sure we have at least 1 record
		$selectEmail = <<<HERE
		<ul>\n
HERE;
		while($stmt->fetch()){ // loop through the result set to build our list
			$selectEmail .= <<<HERE
			<li>This is the email for <a href="mailto:$email">$email</a> $firstname.</li>\n
HERE;
		}
		$selectEmail .= <<<HERE
		</ul>\n
HERE;
	} else {
		$selectEmail = "<p>There is no one on the email list.</p>";
	}
	$stmt->free_result();
	$stmt->close();
} else {
	$selectEmail = "<p>The email system is down now. Please try again later.</p>";
}

In this case, we need the data so we can build the list. We have muliple rows of records. So, instead of just using fetch() and then close() the connection, we are going to store_result() before we fetch() it. This puts the result set into memory. Now we can work with it longer, building our output as needed. When we're finally finished we can free_result() the close() teh connection. This is not a huge change, but enough to make you scratch your head the first few times you use it.

Prepared statements are slightly slower than traditional queries and require bind parameters. If your query is a select query where the entire query is created within the code, with no user input, it is probably easier and faster to just use a traditional MySQL query connection.

Bind Parameters

As you may see in the examples above, bind parameters are used to pass information to the query in the WHERE clause. You can run the query without a bind parameter. Sometimes it is preferred. In these cases, remove the $stmt->bind_param("s", $lastname); statement and the WHERE `lastname` = ? WHERE clause from the query.