Prepared MySQL statements in PHP (object oriented 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 regular structured functions from the MysqlI library at Prepared MySQL statements in PHP (structured examples).

Topics
Verifying that MysqlI is enabled
How to use this guide
Download the examples
Create a mysqli object and connect to the database
A simple SQL query (the mysqli class)
Retrieve data from a simple query (the Mysqli_result class)
Some useful values of the MysqlI class
How to use prepared statements with MysqlI (the Mysqli_stmt class)
Retrieve the information returned after executing a prepared statement
Some useful values of the Mysqli_stmt class
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 (This 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_object_oriented.tar.gz
Size: 6556 bytes
MD5: 8ee4536a2877337416a1ffcd4f152553
SHA1: 574206c149e6af2f76e32e3e3d2b68aaf05f8114
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.

To connect to the database we can use the parameters upon the creation of the mysqli object:

$mysqli = new mysqli("localhost", "username", "password", "db_name");

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

if (mysqli_connect_errno()) {
    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 the method close():

$mysqli->close();

A simple SQL query (the mysqli class)

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 method query() of the mysqli object, this one probable doesn't need any explanation, an example of this is:

$query = "select * from `jveweb_net_test_table`";
$mysqli->query($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 (the Mysqli_result class)

Many of the queries that we issue to the database will give us information back. When queries gives us information back they create a new object class mysqli_result. This new object contains the information in the form of a table, and the necessary methods to retrieve this information. An example of how to obtain this new object would be using:

$result = $mysqli->query($query);

Once we have the new object, we retrieve the information by using the methods fetch_array(), fetch_assoc() or fetch_row() of the object mysqli_result, calling any of this methods 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 methods 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

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

$result->fetch_array(MYSQLI_ASSOC); or $result->fetch_assoc(); 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 $result->fetch_array(MYSQLI_NUM); or $result->fetch_row(); 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 $result->fetch_array(MYSQLI_BOTH); would give 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 object $result
if ($result = $mysqli->query( [query] ) ) {

    // Retrieve the rows in the resulting table until we obtained all
    while ($row = $result->fetch_array() {
        echo $row[field_name];
        echo $row[field_name];
    }
} else {
    die("Can't execute query" . mysqli_error());
}

Some useful values of the MysqlI class

The mysqli class contains some values that may be useful to us:

$mysqli->insert_id

This contains the ID of the last record that was inserted.

$mysqli->affected_rows

This tell us how many rows were affected by the last query that we performed.

How to use prepared statements with MysqlI (the Mysqli_stmt class)

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), the 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, create a mysqli_stmt object, and then bind variables to this marked spaces. An example will explain this better than words, first of all, we create a statement object and prepare a query:

$query = "insert into `jveweb_net_test_table` (`field_name`,`field_email`) values(?,?)";
$stmt = mysqli->prepare($query);

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

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

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

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

$stmt->execute();
$stmt->close();

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($query)) {

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

    // Bind the variables and execute the query
    $stmt->bind_param("ssd", $name, $email, $id);
    $stmt->execute();

    // 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";
    $stmt->execute();

    // And now we close the statement
    $stmt->close();
}

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 methods bind_result() and fetch() of the mysqli_stmt class. 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 fetch() our binded variables end up with the information. And, as in the simple queries, we will need to call 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->prepare($query)) {

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

    // Bind the result and retrieve the data
    $name = "";
    $email = "";
    $stmt->bind_result($name, $email);
    while ($stmt_fetch()) {
        echo $name;
        echo $email;
    }

    // And close the statement
    $stmt->close();
}

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.

Some useful values of the Mysqli_stmt class

Just like the mysqli class, the mysqli_stmt class contains two values that may be useful to us:

$stmt->insert_id

This contains the ID of the last record that was inserted.

$stmt->affected_rows

This tell us how many rows were affected by the last query that we performed.

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 simple functions from the MysqlI library instead of work with objects, 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