PHP Prepared Statements

Probably the place where one would see alot of spaghetti SQL is in scripting or web application development. Generally when speaking of web applications you can almost assume all times that some type of database management system is involved, with scripts to access that data.

If you have ever inherited a web application from a 3rd party that was not using OO-style design you would probably have seen alot of problems with the SQL handling on the app. This kind of problem often leads to more problems, such as SQL attacks by injection of text, and others.

But there is a solution to this problem. In PHP and MySQL, which seem to go joined at the hip you can use PEAR, and the PEAR package provides some very nice interfaces for handling SQL OO-style. So here is how its done:

A simple query to authenticate a user against a database:

require_once("DB.php"); // require PEAR-DB 1:$dsn = "mysql://myusername:mypassword@localhost/mydatabase"; 2:$db =& DB::connect($dsn, array()); 3:if (DB::isError($db)) { 4:  die($db->getMessage()); 5:} 6: 7:// setup a parameterized query 8:$sth = $db->prepare("SELECT COUNT(username) FROM login WHERE username=? and pwd=?"); 9:$res = $db->execute($sth, array($username, $pwd)); 10: 11:// loop through result set 12:$authenticated = false; 13:while(!DB::isError($res) && $row = $res->fetchRow()) { 14:  $authenticated = ($row[0] > 0); 15:  $res->free(); 16:  break; 17:} 18:$db->disconnect(); // release database

What does it all mean?
Line 1: Setup the database connection string. Credentials and database to use.
Line 2: Attempt a connection via PEAR, a database is returned otherwise an error object.
Line 3: Check if the variable is a DB error object, if it is, the database connection failed.
Line 4: Die with an error message
Line 8: Setup a "statement" using the $sth variable. The SQL string contains question marks, each indicating where paramerters will be inserted. Note that it is not necessary to enclose the question marks with quotes if your parameters are text, PEAR will automatically sanitize the SQL statement based on the type of variable you use for each parameter
Line 9: Execute the statement. When using parameters in your SQL statement use the database's execute method. When doing simple queries that don't have parameters, skip line 8 and use the "query" method directly, which takes an SQL string as a parameter. The 2nd parameter passed into the execute method is an array of variables, each corresponding in order to parameters in your SQL statement.
Line 13: Loop through all the rows in the resultset. PEAR will create an array containing values for each column on the current row. Depending on how "fetchRow" is used, the array will be indexed numerically (order 0..Number-Of-Columns), or if fetchRow is used with "DB_FETCHMODE_ASSOC" then the array returned will be an associative array mapping column names to row values.

Here is the same example but accessing the data through an associative array:

Using fetchRow and DB_FETCHMODE_ASSOC:

8:$sth = $db->prepare("SELECT COUNT(username) as CNTUID FROM login WHERE username=? and pwd=?"); 9:$res = $db->execute($sth, array($username, $pwd)); 10: 11:// loop through result set 12:$authenticated = false; 13:while(!DB::isError($res) && $row = $res->fetchRow(DB_FETCHMODE_ASSOC)) { 14:  $authenticated = ($row["CNTUID"] > 0); 15:  $res->free(); 16:  break; 17:}

And finally, here is an example of using a parameterless query with PEAR:

Using the query method:

$res = $db->query("SELECT COUNT(username) FROM login");

Note that on the above example, no parameters were needed because the query is simple, only returning the total number of rows in the login table. Although this could have been used with the prepare method and without any parameters, the query method provided a shortcut for parameterless queries.

In my opinion, PEAR provides a far superior way of handling MySQL than the C language based mysql_XXX calls.

[출처] http://prepared-statement.blogspot.com/2006/02/php-data-access-prepared-statements.html