<?php
/**
* MySQL database layer class
*
* @author Edoardo Tenani <hide@address.com>
* @license GNU Public License
* <http://opensource.org/licenses/gpl-3.0.php>
* @copyright Edoardo Tenani - 2010
*
* @package cboxmanager
* @subpackage database
* @category mysql
* @since 0.0.1
*
* @todo
* implements escaping in add_data() and del_data()
*/
class Database {
/**
* If setted enable debug prints
*
* @access protected
* @var bool
*/
protected $debug = false;
/**
* The host of the MySQL Server
*
* @access protected
* @var string
*/
protected $host;
/**
* The user to access the MySQL Server
*
* @access protected
* @var string
*/
protected $user;
/**
* The password to access the MySQL Server
*
* @access protected
* @var string
*/
protected $pass;
/**
* The name of the db on the MySQL Server
*
* @access protected
* @var string
*/
protected $name;
/**
* Define placeholders used by {@link Database::query() query()} function
*
* @access protected
* @var array
*/
protected $placeholders = array("%d", "%s", "%n", "%f", "%b");
/**
* The resource(link) to manage the database
*
* @access private
* @var resource
*/
private $handler;
/**
* The last query result ( on which apply some functions )
*
* @access private
* @var resource
*/
private $result;
/**
* Constructor of the class.
*
* Initialize $user, $pass, $host, $name and return a pointer
* ($handler) to manage the database.
*
* @param string $host the host of the MySQL server
* @param string $user the user to access the MySQL server
* @param string $pass the password to access the MySQL server
* @param string $database_name the name of the database to be selected
* @return resource the simbolic link to manage the database
*
* @link http://php.net/func_num_args
* @link http://php.net/func_get_args
*/
public function __construct() {
if ( func_num_args() < 4 )
return false;
$args = func_get_args();
$this->host = $args[0];
$this->user = $args[1];
$this->pass = $args[2];
$this->name = $args[3];
$this->handler = mysql_connect($this->host, $this->user, $this->pass);
if ( !$this->handler )
return false;
$select = mysql_select_db($this->name, $this->handler);
if ( !$select )
return false;
return $this->handler;
}
/* OBJECT FUNCTION OVERRIDE
************************************************/
/**
* When the class is destroyed this function is called.
*
* @return void
*/
public function __destruct() { }
/**
* When an inaccesible method of the class is invoked this function
* is called.
*
* @return void
*/
public function __call($name, $arguments) {
echo get_class($this)."::Error in calling object method <b>'$name'</b>"."<br>\n";
}
/**
* When an inaccesible static method of the class is invoked this
* function is called.
*
*
* @return void
*/
public static function __callStatic($name, $arguments) {
echo get_class($this)."::Error in calling object static method <b>'$name'</b>"."<br>\n";
}
/**
* When the object is converted to a string this function is used to
* format the result string.
* Values are separated by "|". If the class var is an array, values
* in it will be separated by ",", or NULL if is not set.
* The string starts and ends with "-".
*
* @access public
* @return a string with all class vars values separated by |
*/
public function __toString() {
return NULL;
}
/* SET & GET FUNCTION
************************************************/
/**
* The function set a specified class var.
* Is not case sensitive.
*
* @access public
* @param string $var the name of the var to be set
* @param mixed $value the new value to set
*
* @return bool true
*/
public function set($var, $value = NULL) {
$this->{$var} = $value;
return true;
}
/**
* The function get a specified class var.
* Is case sensitive.
*
* @access public
* @param string $var the name of the var to be get
*
* @return mixed the class var
*/
public function get($var) {
return $this->{$var};
}
/* PUBLIC FUNCTION
************************************************/
##################################################
## Connection management
/**
* Check db connection
*
* Check if a connection with database is already established
*
*
* @return bool true if connection is established, false otherwise
*
* @uses Database::handler
* @internal uses $this->handler to check if connection
* exists
*
* @see Database::handler
*/
public function is_connected() {
if ( $this->handler !== false )
return true;
else
return false;
}
/**
* @ignore
*
* Create a persistent connection
*
* Not yet implemented.
*/
public function persistent_connection() {}
/**
* Close the connection to the database
*
* @return bool true if the connection is succesfully close, otherwise
* false
*
* @link http://php.net/mysql_close
*/
public function close() {
return mysql_close($this->handler) ? true : false;
}
##################################################
## Database management
/**
* Create a database with the specified name
*
* To be created, the $handler variable must be set to NULL.
*
* @param string $db_name the name of the database to be created
* @return bool true if the operation success, false if fail or if
* the $handler var is set
*/
public function create_database($db_name) {
return ( !$this->handler ) ? $this->query("CREATE DATABASE".$db_name) : false;
}
/**
* Drop a database with the specified name
*
* To be dropped, the $handler variable must be set to NULL.
*
* @param string $db_name the name of the database to be dropped
* @return bool true if the operation success, false if fail or if the
* $handler var is set
*/
public function drop_database($db_name) {
return ( !$this->handler ) ? $this->query("DROP DATABASE".$db_name) : false;
}
##################################################
## Table management
/**
* Create a table.
*
* Not yet implemented.
*
* @ignore
*/
public function create_table() {}
/**
* Drop a table with the specified name.
*
* @param string $table_name the name of the table to be dropped
* @return bool true if the operation success, false otherwise
*/
public function drop_table($table_name) {
return Database::query("DROP TABLE IF EXISTS".$table_name);
}
##################################################
## Query
/**
* Execute the query specified in the argument
*
* Execute the query, storing result in $result and returns
* results. If a % is needed inside a query, must be prepended
* by a '%' ( %% ).
*
* @param string $query the query to be executed
* @param mixed $args, ... all the argumens above first are replace for $query placeholders
* @return resource a link to the results of the query, false if the
* query fails
*
* @internal escape all arguments passed to make them "sql ready",
* reducing risk of SQL Injection; if no placeholders are found
* in the query string a E_USER_WARNING error is thrown;
*
* @uses Database::str_occurences()
* @uses Database::aescape()
* @uses Database::_query()
* @link http://php.net/debug_backtrace
* @link http://php.net/trigger_error
* @link http://php.net/vspritf
*/
public function query() {
$args = func_get_args();
$query = $args[0];
array_shift($args);
$types = $this->str_occurences($query, $this->placeholders);
// check if $types is null ( no placeholders ) and are presents
// single quotes ( ' ) and are not present double % ( which
// means that a single % is needed in the query
if ( ( !( $types ) && strpos($query, "'") ) && !strpos($query, "%%") ) {
$debug = debug_backtrace();
trigger_error("Using Database::query() without placeholder is not safe. Check your query at <b>".$debug[0]["file"]."</b> on line <b>".$debug[0]["line"]."</b>", E_USER_NOTICE);
}
$query = vsprintf($query, $this->aescape($types, $args));
return $this->_query($query);
}
/**
* Add data to a specified table.
*
* The arguments are to be passed in these order: $tablename,
* $column_1, $type_1, $value_1, $column_2, $type_2, $value_2, ...
* <br>
* After the $tablename, respecting the order ( $column, $type,
* $value ), it's possible to specify an infinite number of values.
*
* @param string $tablename the name of the table in which insert
* data
* @param string $column the column in which insert data
* @param string $type the type of the inserted data ( to be
* properly escaped )( relative to the previous defined $column
* var )
* @param mixed $value the value to be inserted ( relative to the
* previous defined $column var )
* @param mixed ... other sets of values
* @return bool true if the operation success, false otherwise
*
* @internal escapes inserted data
*
* @uses Database::query()
* @link http://php.net/func_num_args
* @link http://php.net/func_get_args
*/
public function add_data() {
if ( func_num_args() < 3 )
return false;
$args = func_get_args();
$table = $args[0];
$count = 0;
$i = 1;
while ( $i < func_num_args() ) {
$data[$count] = $args[$i];
$type[$count] = $args[$i+1];
$value[$count] = $args[$i+2];
$count++;
$i += 3;
}
$query = "INSERT INTO ".$table." (";
for ( $i = 0; $i < $count; $i++ ) {
if ( $i == $count-1 )
$query .= $data[$i];
else
$query .= $data[$i].", ";
}
$query .= ") VALUES (";
for ( $i = 0; $i < $count; $i++ ) {
if ( $i == $count-1 )
$query .= "'".$this->escape($type[$i], $value[$i])."'";
else
$query .= "'".$this->escape($type[$i], $value[$i])."', ";
}
$query .= ")";
return $this->_query($query);
}
/**
* Delete a data from a specified table.
*
* @param string $table the name of the table in which the data
* are stored
* @param string $conditions the conditions to select the data to
* be deleted
* @param mixed $args, ... all the argumens above second are
* replace for $conditions placeholders
* @return bool true if the operation success, false if fail
*
* @internal escape all arguments passed to make them "sql
* ready", reducing risk of SQL Injection; if no placeholders
* are found in the query string a E_USER_WARNING error is
* thrown;
*
* @uses Database::str_occurences()
* @uses Database::aescape()
* @uses Database::_query()
* @link http://php.net/debug_backtrace
* @link http://php.net/trigger_error
* @link http://php.net/vspritf
*/
public function del_data() {
$args = func_get_args();
$table = $args[0];
array_shift($args);
$conditions = $args[0];
array_shift($args);
$query = "DELETE FROM ".$table." WHERE ".$conditions;
$types = $this->str_occurences($query, $this->placeholders);
// check if $types is null ( no placeholders ) and are presents
// single quotes ( ' ) and are not present double % ( which
// means that a single % is needed in the query
if ( ( !( $types ) && strpos($query, "'") ) && !strpos($query, "%%") ) {
$debug = debug_backtrace();
trigger_error("Using Database::del_data() without placeholder is not safe. Check your query at <b>".$debug[0]["file"]."</b> on line <b>".$debug[0]["line"]."</b>", E_USER_WARNING);
}
$query = vsprintf($query, $this->aescape($types, $args));
return $this->_query($query);
}
/**
* Modify data in a table.
*
* Not yet implemented.
*
* @ignore
*/
public function mod_data() {}
##################################################
## Utilities
/**
* Execute mysql_num_rows() on the last query result
*
* Execute mysql_num_rows() on $this->result
*
* @access public
* @return mixed the number of rows in a result set on success or
* false on failure
*
* @internal use the class private var result as argument to the
* mysql_num_rows() function
*
* @link http://php.net/mysql_num_rows
*/
public function num_rows() {
return mysql_num_rows($this->result);
}
/**
* Execute mysql_fetch_array() on the last query result
*
* Execute mysql_fetch_array() on $this->result
*
* @access public
* @param mixed $query_result results of a Database::query() call
* can be specified to execute mysql_fetch_array on the specified
* results set
* @param string $type specify the type of fetch to be performed:
* - BOTH ( default ) return both an associative and numerical
* array
* - ASSOC return an associative array
* - NUM return a numerical array
* @return mixed an array with the row data ( based on the $type
* param ) or false on failure
*
* @internal use the class private var result as argument to the
* mysql_fetch_array() function
*
* @link http://php.net/mysql_fetch_array
*/
public function fetch_array($query_result = NULL, $type = "BOTH") {
if ( isset($query_result) && !empty($query_result) )
$result = $query_result;
else
$result = $this->result;
switch ( $type ) {
case "BOTH":
$ret = mysql_fetch_array($result, MYSQL_BOTH);
break;
case "ASSOC":
$ret = mysql_fetch_array($result, MYSQL_ASSOC);
break;
case "NUM":
$ret = mysql_fetch_array($result, MYSQL_NUM);
break;
default:
$ret = false;
break;
}
return $ret;
}
/**
* Free result memory
*
* Free all memory associated with the last result
*
* @return true on success, false otherwise
*
* @link http://php.net/mysql_free_result
*/
public function free() {
return mysql_free_result($this->result);
}
/* PRIVATE FUNCTION
************************************************/
/**
* Execute a query
*
* Execute the specified query, saving result to $this->result.
* Is private becaus doesn't do an escape on values; is useful because is called to perform the query
*
* @param string $query the query to be executed
* @return resource a link to the results of the query, false if the
* query fails
*
* @internal store result for further use ( like mysql_num_rows or
* mysql_fetch_array without need of an argument)
*
* @link http://php.net/mysql_query
*/
private function _query($query) {
if ( $this->debug )
echo "<pre>", var_dump($query), "</pre>";
$this->result = mysql_query($query);
return $this->result;
}
/**
* Escape a string
*
* The function escapes passed value basing on the specified type
*
* @param string $type can be one of the following:
* - %d: decimal value
* - %s: text value
* - %n: numeric value ( not exadecimal )
* - %f: float value
* - %b: binary value
* @param mixed $value the value to be escaped
* @return mixed the escaped value or false on failure
*
* @internal in case of binary data, data are not escaped and must be
* binary data before calling this function on them
*
* @link http://php.net/mysql_real_escape_string
* @link http://php.net/is_numeric
* @link http://php.net/preg_match
* @link http://php.net/manual/en/language.types.type-juggling.php
*/
private function escape($type, $value = "") {
switch ($type) {
case '%d':
return (int) $value;
break;
case '%s':
return mysql_real_escape_string($value);
break;
case '%n':
return is_numeric($value) && !preg_match('/x/i', $value) ? $value : '0';
break;
case '%f':
return (float) $value;
break;
case '%b':
return $value;
break;
}
return false;
}
/**
* Escape all array values
*
* Escapes array values basing on key to detect type; like {@link Database::escape() escape()}, but accepts an array as second argument
*
* @param array $types an array with all the types of the values to be escaped
* @param array $values an array with all the values to be escaped
* @return array an array with escaped values or false on error
*
* @see Database::escape()
*/
private function aescape($types, $values) {
if ( count($types) == count($values) ) {
$iteration = count($types);
for ( $i = 0; $i < $iteration; $i++ ) {
$array[] = $this->escape($types[$i], $values[$i]);
}
return $array;
}
return false;
}
/**
* Return all occurences of a string
*
* Return an array with all the occurences oa the specified string
*
* @param string $haystack the string to search in
* @param mixed $needle the string to search for; can be a single string or an array of strings
* @return an array of needed strings ordered by postiion in string or false on failure( no string found or errors )
*
* @link hhtp://php.net/is_array
* @link hhtp://php.net/is_int
* @link hhtp://php.net/strpos
* @link hhtp://php.net/ksort
* @link hhtp://php.net/array_values
*/
private function str_occurences($haystack, $needle) {
$offset = 0;
$array = array();
if ( is_array($needle) ) {
foreach ( $needle as $need ) {
while ( is_int($offset = strpos($haystack, $need, $offset)) ) {
$array[$offset] = $need;
$offset++;
}
}
}
else {
while ( is_int($offset = strpos($haystack, $needle, $offset)) ) {
$array[$offset] = $need;
$offset++;
}
}
if ( $array ) {
ksort($array);
return array_values($array);
}
return false;
}
}
?>