Using ext / mysqli: part i - overview and prepared statements
Zak Greant and Georg Richter March 16, 2004 Body, Center, TD, P, Li {Font-Family: Verdana, Arial, Helvetica, Sans-Serif; Font-Size: 11px; Color: # 333333} ul {margin-left : 20;} H2 {Font-Family: Arial, Helvetica, Sans-Serif; Font-size: 16px; Color: # 336699} H3 {font-family: arial, helvetica, sans-serif; font-size: 14px; color :??????? # 333333} Intended AudienceIntroduction Major Goals Major Features Why Switch ?? Warnings and SurprisesShow Me the Code !? Basic Usage Using the Object-Oriented InterfacePrepared Statements Bound Parameters Bound Results Using Bound Parameters and Bound Results TogetherSummaryGlossaryAbout 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 is a glossary at Please Note That there ..................
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 , ie 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 t he results of the query, and then discards the query result set and closes the connection.
/ * 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 Theore A Few Key D Ifferences 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. query ('SELECT NAME, POPULATION ") {print (" Very Large Cities Are: / N) "); / * Fetch the results of the query * / while ($ row = $ result-> fetch_assoc ()) {printf ("% s) / n ", $ row ['name'], $ row ['Population']);} / * destroy The result set and free the memory-> 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 IT TO ENSURE THAT IT IS Meaningful, And Stores It IT I 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, eg 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:
/ * 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 available 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. eg $ stmt-> bind_param ( 's', $ STMT-> Bind_Param ('si', $ foo, $ bar); $ stmt-> bind_param ('SID', $ FOO, $ BAR, $ baz); The bind type list 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 TYPECOLUMN TYPEi All INT typesd DOUBLE and FLOATb BLOBss 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: prepare ("Select Code, Name from Country ORDER BY Name Limit 5)) {$ stmt-> execute (); / * bind variables to prepaared statement * / $ stmt-> bind_result ($ col1, $ col2); / * fetch value * / while ($ stmt-> fetch ()) {printf ("% s / n", $ COL1, $ col2);} / * close statement * / $ stmt-> close ();} / * close connection * / $ mysqli-> close ();?> using bound parameters and bound results together here is a more completion Example That Demonstrates The Use of Both Bound Parameters An D Bound Results: prepare ("SELECT CODE, NAMIMIT 5") {$ stmt-> bind_param (" S ", $ code); $ code =" c% "; $ stmt-> execute (); / * bind variables to prepared Statement * / $ stmt-> bind_result ($ col1, $ col2); / * fetch value * / While ($ stmt-> fetch ()) {Printf ("% s% s / n", $ col1, $ col2);
} / * Close Statement * / $ stmt-> close ();} / * close connection * / $ mysqli-> close ();?> Summary in this article, we have promoted 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 '%'"
