Using ext/mysqli: Part I - Overview and Prepared Statements

By Zak Greant and Georg Richter

March 16, 2004

 

Intended Audience
Introduction
?   Major Goals
?   Major Features
?   Why Switch?
?   Warnings and Surprises
Show Me the Code!
?   Basic Usage
?   Using the Object-Oriented Interface
Prepared Statements
?   Bound Parameters
?   Bound Results
?   Using Bound Parameters and Bound Results Together
Summary
Glossary
About the Author

Intended Audience

The article is intended for readers with some experience of using PHP and MySQL. It assumes that the basic concepts behind databases and programming are understood, and that readers know how to use a PHP script to send a query to a MySQL server.

Please note that there are footnotes to help support certain passages, and that there is also a glossary at the end of the document.

Full instructions on PHP and MySQL installation are beyond the scope of this article.

• For information about installing PHP 5, visit http://www.php.net/installation
• Information about compiling PHP 5 with support for ext/mysqli is available at http://www.php.net/mysqli
• For information about installing MySQL 4.1.2 or higher, go to http://www.mysql.com/doc/en/Installing.html

Introduction

Since the mid-90s, ext/mysql has served as the major bridge between PHP and MySQL. Although there have been a few misfeatures and growing pains over the years, in general, ext/mysql has performed its duty quite well, and kept pace with the changes both in PHP and in MySQL.

However, since the introduction of PHP 5 and MySQL 4.1, this has changed - a few rather large cracks are starting to show.

There are existing misfeatures in ext/mysql, most notably mysql_pconnect()^[1] and the automatic and default connections^[2]. Additionally, incompatibilities between the feature set supported by ext/mysql and that supported by the MySQL client library, which ext/mysql and ext/mysqli both rely on, have arisen.

In an effort to correct these issues, Georg Richter has created a new MySQL extension for PHP5 that will support new features in MySQL Version 4.1 and higher.

The extension is called ext/mysqli, with the 'i' standing for any one of: improved, interface, ingenious, incompatible or incomplete.^[3]

Major Goals

Some of the major design goals of the new extension were:

• Easier maintainability The ext/mysql code base has become somewhat complex and messy. Major enhancements in the functionality of MySQL required that various feature sets be enabled or disabled based on the version of the client library. Other issues required that some features be disabled for particular operating systems.
• Better compatibility The extension needed to conform more closely to the MySQL client library, so that future enhancements to the library could be supported more easily in PHP
• Backwards compatibility Although the compatibility between ext/mysql and ext/mysqli is not perfect, effort was made to simplify porting applications from ext/mysql to ext/mysqli.

Major Features

ext/mysqli supports the new features found in recent versions of MySQL and introduces a few features of its own.

The major features of the extension are:

• A procedural interface that looks very much like the ext/mysql interface
• An object-oriented interface that allows for a use idiom that is both more convenient and easier to extend than the procedural interface
• Support for the new MySQL binary protocol that was introduced in MySQL 4.1. (The new protocol is more efficient that the old one and allows for the support of a broader range of features, such as prepared statements.)
• Support for the full feature set of the MySQL C client library, including the ability to set advanced connection options via mysqli_init() and related functions.

Additionally, the extension has support for additional tracing, debugging, load balancing and replication functionality.

 

Why Switch?

Beyond gaining access to the new features of MySQL 4.1+, why would anyone want to switch to using ext/mysqli?

In addition to the functionality mentioned above, ext/mysqli also has some other serious benefits:

• Greater speed. Enhancements in both the extension and in MySQL have made most operations faster, with certain operations becoming up to 40 times faster as compared to ext/mysql.
• Better security. In older versions of the MySQL RDBMS, the possibility existed for an attacker to extract weak password hashes from the network and then recreate a user's password. The new authentication procedure is much more robust and mirrors the attack-resistant authentication procedure of tools like SSH.

Warnings and Surprises

Some aspects of ext/mysqli are quite different from the old extension. In an effort to correct certain design flaws or bug-prone behaviors, specific features have been removed:

• Default database connection. If you do not explicitly connect to the server, ext/mysqli will not do it for you.
• Default link. The database server connection that you wish to use must be explicitly referenced when you use ext/mysqli via its procedural context, i.e. mysqli_query($link, $query);

Note that open connections (and similar resources) are automatically destroyed at the end of script execution. However, you should still close or free all connections, result sets and statement handles as soon as they are no longer required. This will help return resources to PHP and MySQL faster.

 

Show Me the Code!

Now that you know why things have changed, we should start reviewing code that demonstrates how the new extension looks and operates. All standalone code in this article utilizes the ‘world’ database, which is freely available from www.mysql.com.

Basic Usage

Here is a simple script that connects to a MySQL server, sends a query to the server using the established connection, displays the results of the query, and then discards the query result set and closes the connection.


/* Connect to a MySQL server */
$link = mysqli_connect(
            'localhost',  /* The host to connect to */
            'user',       /* The user to connect as */
            'password',   /* The password to use */
            'world');     /* The default database to query */

if (!$link) {
   printf("Can't connect to MySQL Server. Errorcode: %s/n", mysqli_connect_error());
   exit;
}

/* Send a query to the server */
if ($result = mysqli_query($link, 'SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5')) {

    print("Very large cities are:/n");

    /* Fetch the results of the query */
    while( $row = mysqli_fetch_assoc($result) ){
        printf("%s (%s)/n", $row['Name'], $row['Population']);
    }

    /* Destroy the result set and free the memory used for it */
    mysqli_free_result($result);
}

/* Close the connection */
mysqli_close($link);
?>

The above script should output something like:

Very large cities are:

Mumbai (Bombay) (10500000)
Seoul (9981619)
S?o Paulo (9968485)
Shanghai (9696300)
Jakarta (9604900)

As the code shows, ext/mysqli and ext/mysql can be quite similar. The only major differences are that ext/mysqli is slightly more verbose when used in a procedural fashion.

Note that, without error checking, the above script could fail at any point and display an ugly error message to the user.

Using the Object-Oriented Interface

The object-oriented interface provides a slightly terser, and less error-susceptible, way to use ext/mysqli. The code below performs the same tasks as the code above, however there are a few key differences to note:

• We do not need to explicitly specify the connection to use for our commands. The connection information is stored in our $mysqli and $result objects and is accessed as needed when methods are called.
• When fetching rows of query data from the results set using fetch_assoc() we do not have to explicitly specify the result set handle to use. As with the connection information, the result handle is stored in the $result object.


/* Connect to a MySQL server */
$mysqli = new mysqli('localhost', 'user', 'password', 'world');

if (mysqli_connect_errno()) {
   printf("Can't connect to MySQL Server. Errorcode: %s/n", mysqli_connect_error());
   exit;
}

/* Send a query to the server */
if ($result = $mysqli->query('SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5')) {

    print("Very large cities are:/n");

    /* Fetch the results of the query */
    while( $row = $result->fetch_assoc() ){
        printf("%s (%s)/n", $row['Name'], $row['Population']);
    }

    /* Destroy the result set and free the memory used for it */
    $result->close();
}

/* Close the connection */
$mysqli->close();
?>

Prepared Statements

Now that we have seen the basic use of the extension, let’s examine a few of the new features.

Prepared statements provide developers with the ability to create queries that are more secure, have better performance, and are more convenient to write.

They come in two flavors: bound parameter prepared statements, and bound result prepared statements.

Bound Parameters

Bound parameter prepared statements allow query templates to be created and then stored on the MySQL server. When a query needs to be made, data to fill in the template is sent to the MySQL server, and a complete query is formed and then executed.

The basic process for creating and using bound parameter prepared statements is simple.

A query template is created and sent to the MySQL server. The MySQL server receives the query template, validates it to ensure that it is well-formed, parses it to ensure that it is meaningful, and stores it in a special buffer. It then returns a special handle that can later be used to reference the prepared statement.

When a query needs to be made, data to fill in the template is sent to the MySQL server, and then a complete query is formed and then executed.

This process has some very important behaviors wrapped up in it.

The body of the query is only sent to the MySQL server once. On requests to execute the query, only the data to fill in the template needs to be delivered to the MySQL server.

Most of the work required to validate and parse the query only needs to be done a single time, instead of each time that the query is executed.

Additionally, for queries that contain a small amount of data, the overhead of sending the query is greatly reduced. For example, if you have a query like:

INSERT INTO City (ID, Name) VALUES (NULL, 'Calgary');

then each time that you execute the query, you will only need to send about 16 bytes of query data, instead of 60 or more bytes. (These approximate numbers include overhead for the foo and bar query data like the id of the prepared statement, the length of the query data for binary safety, etc, but do not include extra overhead for the query string.)

The data for the query does not need to be passed through a function like mysql_real_escape_string() to ensure that no SQL injection attacks^[4] occur. Instead, the MySQL client and server work together to ensure that the sent data is handled safely when it is combined with the prepared statement.

The query templates look something like:

INSERT INTO City (ID, Name) VALUES (?, ?);

The '?' placeholders can be used in most places that could have literal data, e.g. a query could be transformed from

SELECT Name FROM City WHERE Name = 'Calgary';

to

SELECT Name FROM City WHERE name = ?;

Here is a more complete example that demonstrates the entire process:

$mysqli = new mysqli('localhost', 'user', 'password', 'world');

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s/n", mysqli_connect_error());
    exit();
}

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

/* execute prepared statement */
$stmt->execute();

printf("%d Row inserted./n", $stmt->affected_rows);

/* close statement and connection */
$stmt->close();

/* Clean up table CountryLanguage */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted./n", $mysqli->affected_rows);

/* close connection */
$mysqli->close();
?>

Note that bind_param() has a short string as its first parameter. This is a format string that is used to specify how the data in the bound variables should be treated.

In the case of the above script, ‘sssd’ indicates that the values of the first three parameters $code, $language and $official will be sent as a strings, while the fourth parameter $percent will contain a double or float value.

For each bound variable in bind_param(), there should be another letter in the format string that specifies how the variable should be handled. e.g.

$stmt->bind_param('s', $foo);
$stmt->bind_param('si', $foo, $bar);
$stmt->bind_param('sid', $foo, $bar, $baz);

The bind types let the mysqli extension know how to encode the data that it sends for greater efficiency.

The type definitions are very simple: data in the bound variables will be treated as an integer value, a rational number (double) or a string.

There is also a special type that allows long blobs to be sent to the MySQL server in chunks.

The following table shows the types and when to use them:

BIND TYPE	COLUMN TYPE
i	All INT types
d	DOUBLE and FLOAT
b	BLOBs
s	All other types

Bound Results

Bound result prepared statements allow the value of variables in a PHP script to be tied to the value of fields of data in a query result set.

The process of setting up this binding is:

• Create a query
• Ask the MySQL server to prepare the query
• Bind PHP variables to columns in the prepared query
• Ask the MySQL server to execute the query
• Request that a new row of data be loaded into the bound variables.

Here is a simple code snippet that illustrates this process:

 

$mysqli = new mysqli("localhost", "user", "password", "world");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s/n", mysqli_connect_error());
    exit();
}

/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country ORDER BY Name LIMIT 5")) {
    $stmt->execute();

    /* bind variables to prepared statement */
    $stmt->bind_result($col1, $col2);

    /* fetch values */
    while ($stmt->fetch()) {
        printf("%s %s/n", $col1, $col2);
    }

    /* close statement */
    $stmt->close();
}
/* close connection */
$mysqli->close();

?>

Using Bound Parameters and Bound Results Together

Here is a more complete example that demonstrates the use of both bound parameters and bound results:

$mysqli = new mysqli("localhost", "user", "password", "world");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s/n", mysqli_connect_error());
    exit();
}

/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country WHERE Code LIKE ? LIMIT 5")) {

    $stmt->bind_param("s", $code);
    $code = "C%";

    $stmt->execute();

    /* bind variables to prepared statement */
    $stmt->bind_result($col1, $col2);

    /* fetch values */
    while ($stmt->fetch()) {
        printf("%s %s/n", $col1, $col2);
    }

    /* close statement */
    $stmt->close();
}
/* close connection */
$mysqli->close();

?>

Summary

In this article, we have provided an overview of the features and architecture of ext/mysqli, along with a quick summary of its development history. You should now understand how to use and benefit from MySQL's prepared statements, and should be fairly comfortable using the object-oriented interface to ext/mysqli.

Glossary

ext/mysql - PHP's old MySQL extension. Does not support the full feature set of MySQL version 4.1

ext/mysqli - The new MySQL extension for PHP 5. Supports the features available in MySQL versions 3.22 to 5.0

MySQL client library - The component of the MySQL RDBMS[*] that allows programs to communicate with the RDBMS

MySQL server - The component of the MySQL RDBMS that does the work of processing and responding to queries, managing the disk-level representation of data within the database, etc.

---

[1] - The mysql_pconnect() function was designed to provide a mechanism for reducing the cost of establishing and closing connections to the MySQL server. Unfortunately, due to an interaction between the architecture of the Apache server and the architecture of PHP, high traffic on a site that used pconnects could quickly clog up the MySQL server with many unused connections that could prevent many of the active connections from accessing the database.

[2] - The automatic connection features allowed certain function calls to automatically connect to the database (as long as valid connection data was stored in the php.ini configuration file). The default connection feature operated so that the last opened connection to a MySQL database would be the connection used for database operations unless a connection parameter were explicitly specified in the function arguments.

[3] - This extension is still in development. While the core feature set should be fairly stable, neither MySQL 4.1 nor PHP 5.0 are stable software releases. Also, the supporting feature set that does not cleanly map to the MySQL client library is still in development.

[4] - A SQL injection attack occurs when data is input into a query that cases the query to behave in an unexpected and/or malicious way. For example, given a simple query in a PHP script like "DELETE FROM grades WHERE class_name='test_$class'", an attacker who can gain control over the value of $class can force unintended deletes to occur by changing the value of $class to something like "oops' or class_name LIKE '%'".

About the Authors

Zak Greant is a professional Open Source advocate, writer and programmer. He works for MySQL AB as their Community Advocate. Zak is a maintainer of both PHP’s MySQL extensions, and is also a co-author of PHP Functions Essential Reference.

Georg Richter is the author of the mysqli extension, and also maintains the mysql and ncurses extensions. He works for MySQL AB as a Senior Developer, and is a member of the Apache Software Foundation.

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=49802