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.
|
Download
