Prepared MySQL statements in PHP (structured examples)

Tuesday, July 12, 2011 | En Español

Examples updated on July 23, 2011

I find sad how many online systems are out there unprotected against even the simplest type of attack, SQL injections. This is a guide and a set of examples about the use of prepared MySQL statements in PHP. If you want to see how an SQL injection attack works you can read the previous article An SQL injection attack. There is a similar guide to this but using the object oriented interface of MysqlI in the examples at Prepared MySQL statements in PHP (object oriented examples).

Topics
Verifying that MysqlI is enabled
How to use this guide
Download the examples
Create a mysqli object and connect to the database
Issue a simple query
Retrieve data from a simple query
Other useful functions
How to use prepared statements with MysqlI
Retrieve the information returned after executing a prepared statement
Other useful functions
Foot notes
Related Articles
References

Verifying that MysqlI is enabled

MysqlI (MySQL Improved Extension) allows us to use features and functionality provided by MySQL 4.1 and above. This extension is included with PHP version 5 and later, therefore every example in this guide requires at least PHP version 5. If you don't have at least this version of PHP, you will not be able to take advantage of prepared statements. Of course you can protect your systems against SQL injection attacks, although I will only publish a guide for PHP versions prior to 5 if there are requests to do so, I do have the code, I am not sure that a guide for that version of PHP is still that relevant. MySQL version 4.1 or above is also necessary (4.1.3+ is recommended).

Some advantages that MysqlI offer us over the older MySQL library are:

  • An object oriented interface (The other guide covers this).
  • Support for prepared statements
  • Support for multiple statements
  • Support for transactions (although I won't use them in these guides)

If you have access to the PHP command in the computer where PHP is installed, you can check the support by issuing the command:

php --info | grep "MysqlI Support"

This will show you MysqlI Support => enabled if MysqlI is enabled.

If you don't have access to the PHP command, you can create a new PHP file with the following content:

<?php
phpinfo();
?>

Save this file, put it in the server, and access the file from a web browser, this will show you all the information about PHP. Search for the MysqlI section.

Once you are sure that the MysqlI support is enabled, you can download the examples and continue with the guide.

How to use this guide

This guide explain the functions and the logic of using prepared statements. The examples contain working code showing its usage. The examples contain the insertion, edition, deletion and display of information in a database table, as well as a script to create the table that the examples utilize.

You will need the information to connect to a database (username, password, host) and a database to play with. Fill this information in the settings.php file, then open the file mysql_php_create_table.php in the web browser, this file will create a table and fill it with some information. It also contains links to see the other examples working.

Download the examples

mysqli_php_structured.tar.gz
Size: 6563 bytes
MD5: e72259061ee8713844e37b3f300b0df4
SHA1: e6a1ef22f626dce05d85b40a2603b1af8dd0d517
License: BSD-3 like

The reason I am adding a BSD-3 like license is the disclaimer, I honestly don't mind if the code is used, I'll be happy if it is. However, if the examples are redistributed or republished I would do like a link back to my website.

Create a mysqli object and connect to the database

The four parameters that we need to connect to the database are:

  • Host - The address to the server where the database resides, in many hosts this is often localhost.
  • Username - A valid username for the database, this user must have the necessary permissions to perform the required operations in the database. It is highly advisable to create a secondary user other than the administrator, with just the necessary permissions required for the operation of the system, and use this secondary user for the PHP programs, while keeping the administrative user for, well, the administration of the database.
  • Password - The password of said user.
  • Database - This parameter is optional, you can connect to the database with only the previous parameters and once connected, select a database to use, however, I use this parameter since the connect function to save a few lines of code and the operation of selecting a database.

The function to connect to the database is mysqli_connect():

$link = mysqli_connect("localhost", "username", "password", "db_name");

This function returns a link identifier that we use to handle all the subsequent requests to the database.

After this we check if the connection to the database was successful, or else we print an error message and exit the program:

if (!$link) {
    die("Can not connect to the database: " . mysqli_connect_error());
}

All queries can be made after this lines. Once we are done with all the MySQL queries, we should close the database connection by using:

$mysqli_close($link);

Issue a simple query

I call simple queries the ones that does not involve any sort of input from the user, or any information over which we don't have complete control (such as information stored in the computers of users in the form of cookies). To perform a simple query just make use of the function mysqli_query(), this one probable doesn't need any explanation, an example of this is:

$query = "select * from `jveweb_net_test_table`";
mysqli_query($link, $query);

I made a list of common queries in my previous post Managing MySQL from the command line, mostly for my personal reference, but I hope others can find it useful too.

Retrieve data from a simple query

Many of the queries that we issue to the database will give us information back. These queries give us back this information in the form of a table.

Once we have the result of the query, we retrieve it by using the functions mysqli_fetch_array(), mysqli_fetch_assoc() or mysqli_fetch_row(), calling any of this functions once will retrieve the first element in the table that we obtained as a result of our query, and it will provide us an array with the values. If the table contains more than one row, every time that we call any of this functions we will get the next available row in the resulting table, therefore we will need to call it until we have retrieved every element in the table.

For example, lets imagine that we have this table:

field_id field_name field_address field_email
1 Juan 140 Random Street, Random City juan@jveweb.net
2 Valencia 72 Random Drive, Random City valencia@jveweb.net
3 Roger 23 Some Street, Random City roger@jveweb.net
4 Krista 42 X Street, Random City krista@jveweb.net
5 Mery 763 X Drive, Random City mery@jveweb.net
6 Trevor 21 Random Avenue, Random City trevor@jveweb.net

mysqli_fetch_array() can give us either a numeric array, an associative array, or both:

mysqli_fetch_array($result, MYSQLI_ASSOC); or mysqli_fetch_assoc($result); would associate the name of the field with the array and give us:

Array
(
    ["field_id"]      => 1
    ["field_name"]    => "Juan"
    ["field_address"] => "140 Random Street, Random City"
    ["field_email"]   => "juan@jveweb.net"
)

While mysqli_fetch_array($result, MYSQLI_NUM); or mysqli_fetch_row($result); would associate the number of the column with the array and give us instead:

Array
(
    [1] => 1
    [2] => "Juan"
    [3] => "140 Random Street, Random City"
    [4] => "juan@jveweb.net"
)

And of course mysqli_fetch_array($result, MYSQLI_BOTH); would gives us an array with 8 elements, which would have both arrays for us to use as we wish, this is the default behavior.

Summing up, the code for retrieve this information from the database would be as follows:

// Capture the result from the query in the variable $result
$result = mysqli_query( [link identifier], [query]);
if (!$result) {
    die("Can't execute query" . mysqli_error());
}

// Retrieve the rows in the resulting table until we obtained all
while ($row = mysql_fetch_array($result) {
    echo $row[field_name];
    echo $row[field_name];
}

Other useful functions

Two more functions that allow us to retrieve some information from the last used queries are mysqli_insert_id() and mysqli_affected_rows()

mysqli_insert_id([link identifier]);

This function give us the ID of the last record that was inserted. The only parameter is the link identifier

mysqli_affected_rows([link identifier]);

This function tell us how many rows were affected by the last query that we performed. Its only parameter is the link identifier.

How to use prepared statements with MysqlI

If our query to the database contains information that the user of the system provide us, or any information that was was stored outside our control, such as information stored in cookies (session information is usually stored this way, therefore it can be tampered with), prepared SQL statements allows us to securely communicate with the database, since it separates the logic from the data that we send to the database.

While in a simple SQL query we only need to create the query including all the data that needs to be used, such as the ID of the record that we need to work with or the information to be inserted or updated, in a prepared statement we create this query marking the spaces where data is going to be given with question marks, and then bind variables to this marked spaces. An example will explain this better than words, first of all, we create a prepared SQL statement:

$query = "insert into `jveweb_net_test_table` (`field_name`,`field_email`) values(?,?)";
$stmt = mysqli_prepare($link, $query);

This function give us back a statement object which we store in $stmt.

Once the statement is prepared, we need to bind variables by using mysqli_stmt_bind_param(), this takes three or more parameters, the first one is the statement object, the second one the types of variables in the form of a string (s for string, d for number), and from the third variable on, the list of variable separated by a comma. Let see an example of this:

$name = "Juan";
$email = "juan@jveweb.net";
mysqli_stmt_bind_param($stmt,"ss",$name, $email);

And with the variables binded, we can execute the query with mysqli_stmt_execute(). When we are done with the prepared statement we close it with mysqli_stmt_close() (this always goes before mysqli_close() of course).

mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);

Lets see another example, lets update two records with the same prepared statement:

$query = "update `jveweb_net_test_table` set `field_name`=?, `field_email`=? where `field_id` = ?";
if ($stmt = mysqli_prepare($link, $query)) {

    // Lets create the variables
    $id = 2;
    $name = "Juan";
    $email = "test@jveweb.net";

    // Bind the variables and execute the query
    mysqli_stmt_bind_param($stmt,"ssd", $name, $email, $id);
    mysqli_stmt_execute($stmt);

    // Since we already have the parameters binded, we can simply change
    // the values and execute it again
    $id = 3;
    $name = "Valencia";
    $email = "other@jveweb.net";
    mysqli_stmt_execute($stmt);
    // And now we close the statement
    mysqli_stmt_close($stmt);
}

Retrieve the information returned after executing a prepared statement

Just as simple queries, prepared statements may give us back information, but the way to retrieve this information after executing a prepared statement is different from that of a simple query.

To retrieve information from a prepared statement we need to use the functions mysqli_stmt_bind_result() and mysqli_stmt_fetch(). We don't end up with arrays when we retrieve this information, instead we create a set of variables, bind them to the result, and upon executing mysqli_stmt_fetch() our binded variables end up with the information. And, as in the simple queries, we will need to call mysqli_stmt_fetch() until we have obtained all the resulting data if there is more than one row as a result.

Lets do a select query:

$query = "select `field_name`,`field_email` from `jveweb_net_test_table` where `field_id` between ? and ?";
if ($stmt = mysqli_stmt_prepare($link, $query)) {

    // Bind the variables and execute the statement
    $start = 2;
    $end = 4;
    mysqli_stmt_bind_param($stmt, "dd", $start, $end);
    mysqli_stmt_execute($stmt);

    // Bind the result and retrieve the data
    $name = "";
    $email = "";
    mysqli_stmt_bind_result($stmt, $name, $email);
    while (mysqli_stmt_fetch($stmt)) {
        echo $name;
        echo $email;
    }

    // And close the statement
    mysqli_stmt_close($stmt);
}

Since we are asking for two different fields (field_name and field_email), the first one will be stored in the first variable that we bind ($name) and of course the second one is stored in the second variable that we bind ($email), the order here is very important.

Other useful functions

Just as with the simple queries, in prepared statements we also have two functions that allow us to retrieve some information from the last used queries: mysqli_stmt_insert_id() and mysqli_stmt_affected_rows()

mysqli_stmt_insert_id([stmt object]);

This function give us the ID of the last record that was inserted. The only parameter is the link identifier

mysqli_stmt_affected_rows([stmt object]);

This function tell us how many rows were affected by the last query that we performed. Its only parameter is the link identifier.

Foot notes

If I find something to correct, it will be corrected, in the examples I include a link to this section.

The other guide is very similar, only it uses the mysqli object instead of functions, there are some differences, but they are minor, however I thought that mixing both in the same article or in the same examples may be a little confusing.

Related Articles

References

Categories: MySQL, PHP, Programming