Using prepared statements to insert data into a database
An SQL injection attack is a form of hacking which attempts to obtain information about your server or DataBase setup by triggering errors in your form handling code.
A hacker would fill in your form with a string that, if simply inserted in an SQL query, would prevent that query from working and, hopefully for the hacker, cause an error.
Using prepared statements allows you to send the query itself to the SQL server separately from the values, so the server won’t try to interpret the values as SQL, thus rendering the attack harmless.
Today I will demonstrate how you would use prepared statements in PHP.
First off, we need our query, but slightly different from the usual:
<?php
require_once('mysqli_connect.php');
$q = "INSERT INTO table_name (col1, col2, col3) VALUES (?, ?, ?)";
Notice how, instead of directly concatenating the values in the query itself, we use the ?
character as a token.
Next, we need to send this query to the server as a prepared statement. For this, we use the mysqli_prepare
function:
$stmt = mysqli_prepare($dbc, $q);
So now the server has prepared the query and is waiting for the values to insert.
mysqli_stmt_bind_param($stmt, 'iss', $value1, $value2, $value3);
Using this PHP function, we give it just that. The first function argument is the prepared statement, the second is a string representing the data types to be expected and the others are the values themselves, in the order in which they appear in the query (here I assume the existence of the variables $value1
, $value2
and $value3
). In the data type string, every character corresponds to a bound value, according to the following table. This also adds a layer of security, because the server will notice if a value is the wrong data type.
Letter | Represents |
---|---|
i | Integer |
d | Decimal (floating-point number) |
b | Blob (binary data) |
s | Anything else |
Now all that’s left to do is execute the statement and, of course, check it returned the expected result!
mysqli_stmt_execute($stmt);
if (mysqli_stmt_affected_rows($stmt) == 1) {
//success!
} else {
// error
}
?>
Prepared statements are also often used to repeat the same query many times more efficiently, because you don’t have to resend the same query multiple times, just the data.
Enjoy!
Useful links
More on prepared statements in PHP.