MySQL Done Right: Execute prepared queries to MySQL databases

Class         : mysqli_ext
Purpose       : README.txt for mysqli_ext.
Version       : 0.24
Filename      : README.txt
Author        : Daren Schwenke
Date released : 2009-10-19 
License       : http://www.gnu.org/licenses/lgpl.txt

###############################
Overview
 
This PHP5.0+ class provides a SAFE, clean, object oriented, and efficient way 
to do Mysql database development.

All queries except those with multiple row results return exactly what you need
in ONE LINE OF CODE.

This is all done while still maintaining use of prepared queries and bound 
parameters to operate more efficiently and eliminate the risk of SQL injection 
attacks (provided you DO NOT put user variables in the query string).

No extra coding is needed on your part to reuse prepared queries. You simply 
write the same query again, and if you have done that one before, the prepared 
handle is reused. The newly bound parameters are used against the existing 
prepared handle, and the results are recomputed. The results themselves are not
cached.

So there you have it. Simple use of prepared queries with bound parameters, 
while still having access to the fetch_object and fetch_assoc methods. Now you 
have no excuse.

###############################
Usage

See examples.php for database connection setup details.

General format for all queries:

  $return_result = $db->method_name('MySQL query with ? substituted for variables','corresponding placeholder types',$corresponding, $variables, $for_placeholders);

That format explained:
  SQL queries themselves are beyond the scope of this document.  Some common examples are below.

  Placeholders are allowed to be these types.  Straight from http://php.net/manual/en/mysqli-stmt.bind-param.php 
  i  	corresponding variable has type integer
  d 	corresponding variable has type double
  s 	corresponding variable has type string
  b 	corresponding variable is a blob and will be sent in packets
  Sending data of type blob or text using internal methods is currently limited to the size of max_allowed_packet.  
  Sending longer data can be accomplished via $db handle using standard methods.
  Example of this: http://us.php.net/manual/en/mysqli-stmt.send-long-data.php
  
  A list of variables to assign to each type.  Should be scalars.

###############################
Examples

Set up database connection.
define('DB_USERNAME', 'your_db_username');
define('DB_PASSWORD','your_db_password');
define('DB_NAME','database_name');
define('DB_SERVER','localhost');
Next line when set to TRUE will enable caching of the prepared queries.  
define('DB_CACHE',true);

Require in the class.  Using require instead of include as we also open the 
db connection.
require('dbconnect.inc');

Other mysqli object methods will work as well.  If you need to do something
like change your character set, you can do it against the normal handle.
$db->set_charset("utf8");


Inserts return insert ID:
$insert_id = $db->pinsert('INSERT INTO sometable (somestring,someint,somedouble,someblob) VALUES (?,?,?,?)','sidb',$string,$int,$double,$blob);

Updates return count of rows affected:
$rows_affected = $db->pexecute('UPDATE sometable SET somestring = ? WHERE someint = ? LIMIT 1','si',$string,$int);

Deletes return count of rows affected:
$rows_affected = $db->pexecute('DELETE FROM sometable WHERE someint = ? LIMIT 1','i',$int);

Just count rows return without reading the data:
$row_returned = $db->prows('SELECT something FROM sometable WHERE somestring = ? AND someint >= ?', 'si' , $string,$int );

Select a single row and return an object containing the data.
$obj = $db->psingle('SELECT someint,somestring FROM sometable WHERE someint = ? LIMIT 1','i',$int);
print $obj->someint; print $obj->somestring;

Select multiple rows and return a handle.
$some_sth = $db->pbind('SELECT somestring FROM sometable WHERE someint = ?','i',$int);
while ( $row = $some_sth->fetch_object() ) { print $row->somestring; }
$some_sth->close();


###############################
Using LIMIT and ORDER BY

The ? placeholder in the query string can only be used for string literals.  
This basically means that things like LIMIT ? and ORDER BY ? will not work and 
will still have to be placed in the query portion.  Putting variables in the 
query portion effectively eliminates the security provided by bound parameters 
so this data MUST be sanitized.  Some simple examples of how I do this.

For user provided input that is supposed to be an integer, ensure it is by 
silently changing the the user input type.
settype($limit, 'integer');

For user provided input destined for ORDER BY, I limit the user input to a 
pre-defined list of specific valid answers.  It is more secure than a generic 
regex, and generally faster anyway.

$valid_orders = array('someint','somestring','someint DESC,somestring ASC');
if ( in_array($unsafe_order,$valid_orders) ) { 
  $order = $unsafe_order; # Exact match to one of our valid_orders. 
} else { 
  $order = 'someint'; # You hacker!  Fall back to something.
}

Now we can still do our queries with LIMIT and ORDER BY without worry of SQL 
injection attacks.

$some_sth = $db->pbind('SELECT someint,somestring FROM sometable WHERE someint > ? ORDER BY ' . $order . ' LIMIT ' . $limit,'i',$int);

In my experience these methods have been effective, but of course YMMV.