Using PHP & MySQLi With A MySQL Database Part 3
This third part of explaining how to use PHP & MySQLi was inspired by ‘abr4xas’ who commented on part 2 of this tutorial. He/she asked how they would go about safely inserting data from the $_POST superglobal into one of their MySQLi queries. So here is how to do exactly that.
Prepared Statements
One of the best ways to use data that you have received from an untrusted source, is to use a prepared statement. Arguments can be made that it incurs a performance penalty if you are not using it for a repeating query, however I believe the automatic injection prevention you get is worth it. If you feel differently then please feel free to use your own injection prevention code or a ORM such as Eloquent.
To use a prepared statement is slightly more complicated then you may be used to if you have used MySQLi in the past, but please stick with it, it’s worth it.
Insert/Update Data
I’m going to list the code piece by piece, then the whole thing if you want to eat some copypasta. First let’s connect to our database & write out our query.
|
1 2 |
$mysqli = new mysqli("127.0.0.1", "my_user", "my_password", "my_database"); $query = "INSERT INTO singers SET id=NULL, firstName=?, lastName=?"; |
Now let’s create our statement object & prepare our query.
|
1 2 |
$stmt = $mysqli->stmt_init(); $stmt->prepare($query); |
Now let’s bind our parameters & insert the data we need to use into the variables.
|
1 2 3 4 |
$stmt->bind_param('ss', $firstName, $lastName); $firstName = $_POST['firstName']; $lastName = $_POST['lastName']; |
Now let’s run the query & clean up.
|
1 2 3 |
$stmt->execute(); $stmt->close(); $mysqli->close(); |
That’s it. So, what exactly is happening here?
We first create a connection to our database & write out our query replacing the areas where we would normally enter our data with ? (question marks). These question marks are used as bind points by the MySQLi driver. We then create a statement object and use it to prepare the query. Although more complicated, you can think of the prepare method as running through the query looking for bind points & readying them for the data you wish to bind to them.
Next we bind the data to those bind points. You need to tell MySQLi the data type to be used. To do this you use the first parameter of bind_param(). The options are:
- i – for any type of integer
- d – for any type of float/double
- b – for blob types
- s – all others (including strings)
You must list them with no spaces & in the order they are were listed in the query you created. There are infinite parameters after the first one so you can provide the variables your data is held in. Again they should be given in the order that matches your query & the bind types. Next we assign the data to those variables. Finally we run the query & then clean up. If you need to know how many rows were affected before destroying the MySQLi/Stmt object you can by checking the value of $stmt->affected_rows.
Here is the entire code block in one go:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$mysqli = new mysqli("127.0.0.1", "my_user", "my_password", "my_database"); $query = "INSERT INTO singers SET id=NULL, firstName=?, lastName=?"; $stmt = $mysqli->stmt_init(); $stmt->prepare($query); $stmt->bind_param('ss', $firstName, $lastName); $firstName = $_POST['firstName']; $lastName = $_POST['lastName']; $stmt->execute(); $stmt->close(); $mysqli->close(); |
One of the largest benefits of prepared statement is the ability to run looped queries without incurring a massive performance penalty. Let’s take a quick look at how to do that. The main change to the code only appears after binding the parameters. Like this.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$firstNameA = $_POST['firstName']; $lastNameA = $_POST['lastName']; $i = 0; $s = sizeof($firstNameA); while( $i < $s ) { $firstName = $firstNameA[$i]; $lastName = $lastNameA[$i]; $stmt->execute(); //You could also check $stmt->affected_rows to check if the update/insert worked } $stmt->close(); $mysqli->close(); |
Retrieve Data
I can hear you shouting at the screen. What if you want to retrieve data & not insert it? Well you can use prepared statements for that too. The code is shockingly similar so I’m just going to write the whole code instead of going through it line by line.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$mysqli = new mysqli("127.0.0.1", "my_user", "my_password", "my_database"); $query = "SELECT firstName, lastName FROM singers WHERE firstName = ?"; $stmt = $mysqli->stmt_init(); $stmt->prepare($query); //Assuming $firstName is already set. $stmt->bind_param('s', $firstName); $stmt->execute(); $result = $stmt->get_result(); while($row = $result->fetch_assoc()) { printf('%s %s is a singer.', $row['firstName'], $row['lastName']); $stmt->close(); $mysqli->close(); |
If you want to do a select query that doesn’t need any bound parameters I would advise using the non-prepared method of querying. However it is possible, just miss out the bind_param() section and run execute straight away.
Conclusions
Remember to only use prepared statements when it would be right to do so, generally where you need to run a query loop. However using it to prevent again SQL injection, in my opinion, is a pretty good excuse as the performance drop you get can be worth it. Remember though, if you need some form of custom validation then you may be better off with an ORM such as Eloquent.
If you are still confused about MySQLi, or you are worried that some developers say ‘PDO or go home’, then you could try an ORM such as Laravel’s excellent Eloquent which can be used very easily now independent of the Laravel 4 framework.
