Banner: ZumGuy Publications and Network

ZumGuy Publications and Network

Using prepared statements to insert data into a DataBase

Posted by Sean on Thursday, 26th December 2013 15:39
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:
$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.

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!
if (mysqli_stmt_affected_rows($stmt) == 1) {
}  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.

Useful links

More on prepared statements in PHP.
Posted by Andrew on Friday, 27th December 2013 13:53

Thanks Sean, this will be useful.


You must be logged in to post messages.

Quote of the day...

ZumGuy Internet Promotions