Skip to content

@CodeAngry /CA_MySQLi_Lite.php secret
Last active

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
mysqli_ helper functions that escape data internally and should be SQL injection free :)
<?php
/**
* ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
* This code is a free part of CA::MySQLi PHP Library.
* The full Library has Prepared Statement support and A LOT more functionality.
* It will be available at http://mysqliphp.com/ sooner or later... depending on several factors.
*
* To be notified of its release, follow me on http://codeangry.com/ ;)
* ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
* @author Claude "CodeAngry" Adrian
* @copyright mysqliphp.com
* @license http://www.wtfpl.net/
*
* ENJOY!
*/
/**
* mysqli_escaped() is assumed to be pre-escaped and is untouched by:
*
* - mysqli_format()
* - mysqli_escape_one()
* - mysqli_escape_many()
* - mysqli_escape()
*/
class mysqli_escaped {
/**
* Stores the literal.
*
* @var string
*/
private $literal = null;
/**
* Prepares the literal.
* If more then one arguments, it sprintf()s in the literal WITHOUT escaping.
* It relies on the developers sanity. If you need to escape arguments, use mysqli_format().
*
* @param string $literal
* @return mysqli_escaped
*/
public function __construct($literal){
// let's assume $literal objects with __toString() know what they're doing!
if(is_object($literal) and method_exists($literal, '__toString')){
$literal = strval($literal);
}
if(!is_string($literal) or !strlen($literal = trim($literal))){
throw new \InvalidArgumentException('$literal must be a non-empty trimmed string.');
}
if(func_num_args() > 1){
$arguments = func_get_args();
array_shift($arguments);
$literal = vsprintf($literal, $arguments);
}
$this->literal = $literal;
}
/**
* Converts to a plain old string.
*
* @return string
*/
public function __toString(){
return $this->literal;
}
} // class mysqli_escaped;
/**
* Alias of new mysqli_escaped($literal, ...);
* Takes ... arguments expanded like call_user_func().
*
* @param string $literal
* @return mysqli_escaped
*/
function mysqli_escaped($literal){
static $reflector;
if(!isset($reflector)){
$reflector = new \ReflectionClass(__NAMESPACE__.'\\mysqli_escaped');
}
return $reflector->newInstanceArgs(func_get_args());
} // function mysqli_escaped;
/**
* Alias of new mysqli_escaped($literal, ...);
* Takes ... arguments like call_user_func_array().
*
* @param string $literal
* @param array $arguments
* @return mysqli_escaped
*/
function mysqli_escaped_array($literal, array $arguments = null){
if(empty($arguments)){
return mysqli_escaped($literal);
}
array_splice($arguments, 0, 0, array($literal));
return call_user_func_array(__NAMESPACE__.'\\mysqli_escaped', $arguments);
} // function mysqli_escaped_array;
/**
* Escapes the $arguments array string elements and serializes non-scalars.
*
* @param mysqli $handle
* @param array $arguments
* @return array
*/
function mysqli_escape_one(\mysqli $handle, $argument){
// null is cool
if(is_null($argument)){
return 'NULL';
}
// resources cannot be stored
if(is_resource($argument)){
throw new \InvalidArgumentException('$argument cannot be a resource.');
}
// convert bools to literal
if(is_bool($argument)){
return $argument ? 'TRUE' : 'FALSE';
}
// leave integers and floats intact
if(is_integer($argument) or is_float($argument)){
return $argument;
}
// try to serialize non-strings
if(!is_string($argument)){
if($argument instanceof mysqli_escaped){
return strval($argument);
}
if(is_object($argument) and method_exists($argument, '__toSQL')){
return $argument->__toSQL(); // we assume it's escaped
}
if(($argument = serialize($argument)) === false){
throw new \InvalidArgumentException('$argument cannot be serialized.');
}
}
// we have a string so we escape it (or if empty, return it)
if(is_string($argument)){
return empty($argument) ? "''" : sprintf("'%s'", mysqli_real_escape_string($handle, $argument));
}
// impossible :)
throw new \InvalidArgumentException('$argument is impossible.');
} // function mysqli_escape_one;
/**
* Escapes the $arguments array string elements and serializes non-scalars.
*
* @param mysqli $handle
* @param array $arguments
* @return array
*/
function mysqli_escape_many(\mysqli $handle, array $arguments){
// convert $arguments to sql friendly data
array_walk($arguments, function(&$argument) use ($handle){
$argument = mysqli_escape_one($handle, $argument);
});
return $arguments;
} // function mysqli_escape_many;
/**
* If $payload is an array, it forwards the call to mysqli_escape_many().
* If $payload is anything else, it forwards the call to mysqli_escape_one().
*
* @param mysqli $handle
* @param mixed $payload
* @return mixed
*/
function mysqli_escape(\mysqli $handle, $payload){
return call_user_func(
sprintf('%s\\mysqli_escape_%s', __NAMESPACE__, is_array($payload) ? 'many' : 'one'),
$handle, $payload);
} // function mysqli_escape;
/**
* Builds a query like sprintf() but escapes arguments first.
* Takes ... arguments like call_user_func().
*
* @param mysqli $handle
* @param string $query
* @return mysqli_escaped
*/
function mysqli_format(\mysqli $handle, $query){
// let's assume $query objects with __toString() know what they're doing!
if(is_object($query) and method_exists($query, '__toString')){
$query = strval($query);
}
if(empty($query) or !is_string($query)){
throw new \InvalidArgumentException('$query must be a non-empty string.');
}
// no payload, no escaping
if(func_num_args() == 2){
return $query;
}
// we handle the payload and we sprintf() it in the main string
$arguments = func_get_args();
array_splice($arguments, 0, 2);
$arguments = mysqli_escape_many($handle, $arguments);
return new mysqli_escaped(vsprintf($query, $arguments));
} // function mysqli_format;
/**
* Builds a query like sprintf() but escapes arguments first.
* Takes ... arguments like call_user_func_array().
*
* @param mysqli $handle
* @param string $query
* @return mysqli_escaped
*/
function mysqli_format_array(\mysqli $handle, $query, array $arguments = null){
if(empty($arguments)){
return mysqli_format($handle, $query);
}
array_splice($arguments, 0, 0, array($handle, $query));
return call_user_func_array(__NAMESPACE__.'\\mysqli_format', $arguments);
} // function mysqli_format_array;
/**
* put your comment there...
*
* @param mysqli $handle
* @param array $key_values
* @param string $glue
* @return mysqli_escaped
*/
function mysqli_format_glue(\mysqli $handle, array $key_values, $glue = ', '){
if(!is_string($glue) or empty($glue)){
throw new \InvalidArgumentException('$glue must be a non-empty string.');
}
$output = array();
foreach($key_values as $key => &$value){
if(!is_string($key) or empty($key)){
throw new \InvalidArgumentException('$key must be a non-empty string.');
}
if(!preg_match('~^`.+`$~', $key)){
$key = "`{$key}`";
}
$value = mysqli_escape_one($handle, $value);
$output[] = "{$key} = {$value}";
}
return new mysqli_escaped(implode($glue, $output));
} // function mysqli_format_glue;
/**
* Formats the input array into "...";
* [...] means `key1`, `key2`, ....
*
* @param mysqli $handle
* @param array $key_values
* @return mysqli_escaped
*/
function mysqli_format_keys(array $key_values){
$output = array();
foreach($key_values as $key => &$value){
if(!is_string($key) or empty($key)){
throw new \InvalidArgumentException('$key must be a non-empty string.');
}
if(!preg_match('~^`.+`$~', $key)){
$key = "`{$key}`";
}
$output[] = $key;
}
// mysqli_escaped is left untouched in mysqli_format() and we need that!
return new mysqli_escaped(implode(', ', $output));
} // function mysqli_format_keys;
/**
* Formats the input array into "...";
* [...] means 'value1', 'value2', ....
*
* @param mysqli $handle
* @param array $key_values
* @return mysqli_escaped
*/
function mysqli_format_values(\mysqli $handle, array $key_values){
$output = array();
foreach($key_values as $key => &$value){
$output[] = mysqli_escape_one($handle, $value);
}
// mysqli_escaped is left untouched in mysqli_format() and we need that!
return new mysqli_escaped(implode(', ', $output));
} // function mysqli_format_values;
/**
* Formats the input array into "(...) VALUES (...)";
* [...] see mysqli_format_keys() and mysqli_format_values().
* This prepares a record for INSERT/REPLACE.
*
* @param mysqli $handle
* @param array $key_values
* @return mysqli_escaped
*/
function mysqli_format_key_values(\mysqli $handle, array $key_values){
$keys = $values = array();
foreach($key_values as $key => &$value){
if(!is_string($key) or empty($key)){
throw new \InvalidArgumentException('$key must be a non-empty string.');
}
if(!preg_match('~^`.+`$~', $key)){
$key = "`{$key}`";
}
$keys[] = $key;
$values[] = mysqli_escape_one($handle, $value);
}
$keys = implode(', ', $keys);
$values = implode(', ', $values);
// mysqli_escaped is left untouched in mysqli_format() and we need that!
return new mysqli_escaped('(%s) VALUES (%s)', $keys, $values);
} // function mysqli_format_key_values;
/**
* Formats Keys and Escapes Values but returns an array.
* Use it as you with afterwards.
*
* @param mysqli $handle
* @param array $key_values
* @param string $glue
* @return array
*/
function mysqli_format_in_place(\mysqli $handle, array $key_values){
$output = array();
foreach($key_values as $key => &$value){
if(!is_string($key) or empty($key)){
throw new \InvalidArgumentException('$key must be a non-empty string.');
}
if(!preg_match('~^`.+`$~', $key)){
$key = "`{$key}`";
}
$value = mysqli_escape_one($handle, $value);
$output[$key] = $value;
}
return $output;
} // function mysqli_format_in_place;
?>
<?php
require_once __DIR__.'/MySQLi_Lite.php'; // pull the Library
header('Content-Type: text/plain; charset="utf-8"', true); // plain/text output
$client = mysqli_connect(); // change to fit your needs
/**
* The following code shows how to use mysqli_escaped object.
* The mysqli_escaped object and the function allows you to feed arguments to mysqli_format()
* that will not be escaped/modified in any way.
* This allows sending SQL instructions as arguments without escaping them.
*/
call_user_func(function() use ($client){
echo PHP_EOL;
echo str_pad(null, 75, ':'), PHP_EOL;
echo 'mysqli_escaped()', PHP_EOL;
echo str_pad(null, 75, ':'), PHP_EOL;
echo PHP_EOL;
// easy sending MySQL instruction as arguments
echo new mysqli_escaped('UTC_TIMESTAMP()'), PHP_EOL;
// the date will NOT BE escaped so we wrap it in '%s'
echo mysqli_escaped("UTC_TIMESTAMP() > '%s'", gmstrftime('%Y-%m-%d')), PHP_EOL;
// the date will BE escaped (we force it with mysqli_escape())
echo mysqli_escaped('UTC_TIMESTAMP() > %s', mysqli_escape($client, gmstrftime('%Y-%m-%d'))), PHP_EOL;
});
/**
* The following code shows how to use mysqli_escape_one(), mysqli_escape_many() and mysqli_escape().
* mysqli_escape() looks at 2nd argument and if it's an array it calls mysqli_escape_many() otherwise mysqli_escape_one().
*/
call_user_func(function() use ($client){
echo PHP_EOL;
echo str_pad(null, 75, ':'), PHP_EOL;
echo 'mysqli_escape_*() functions', PHP_EOL;
echo str_pad(null, 75, ':'), PHP_EOL;
echo PHP_EOL;
// is turned into a string
echo mysqli_escape($client, 'UTC_TIMESTAMP()'), PHP_EOL;
// array is escaped as it takes on argument
echo mysqli_escape_one($client, array(1, 2, 3, false)); echo PHP_EOL;
// each array element is escaped as it takes multiple arguments
var_export(mysqli_escape_many($client, array(1, 2, 3, false))); echo PHP_EOL;
});
/**
* The following code shows how mysqli_format() works.
*/
call_user_func(function() use ($client){
echo PHP_EOL;
echo str_pad(null, 75, ':'), PHP_EOL;
echo 'mysqli_format() functions', PHP_EOL;
echo str_pad(null, 75, ':'), PHP_EOL;
echo PHP_EOL;
// format a query with inline arguments ... like call_user_func()
// data types of arguments are escaped while mysqli_escaped() is untouched
echo mysqli_format($client, 'SELECT * FROM `books` WHERE `title` = %s AND `price` = %0.2f AND `published` = %s LIMIT 1;',
"A 'book title'", 25.6, mysqli_escaped('DATE(UTC_TIMESTAMP())')), PHP_EOL;
// format a query with array arguments ... like call_user_func_array()
// data types of arguments are escaped while mysqli_escaped() is untouched
echo mysqli_format_array($client, 'SELECT * FROM `books` WHERE `title` = %s AND `price` = %0.2f AND `published` = %s LIMIT 1;',
array("A 'book title'", 25.6, mysqli_escaped('DATE(UTC_TIMESTAMP())'))), PHP_EOL;
// A more complex query with some argument magic
echo mysqli_format($client, "SELECT * FROM `books` WHERE `published` < %s AND (%s OR %s);",
$year = mysqli_escaped('DATE(`published`) IN (%s)',
implode(', ', mysqli_escape_many($client, array('2013-01-01', '2013-01-02')))
), // no escaping of 2nd argument (mysqli_escaped() DOES NOT ESCAPE)
// $title1 and $title2 are the same thing with different call types
$title1 = mysqli_format($client, '`title` IN (%s)',
"A title with a 'sub title in it'"
), // 3rd argument is escaped (mysqli_format() DOES ESCAPE)
$title2 = mysqli_format_array($client, '`title` IN (%s)',
array("A title with a 'sub title in it'")
) // 3rd argument is escaped (mysqli_format() DOES ESCAPE)
), PHP_EOL;
});
/**
* The following code shows how to use mysqli_format_*() functions for inserting data.
*/
call_user_func(function() use ($client){
echo PHP_EOL;
echo str_pad(null, 75, ':'), PHP_EOL;
echo 'mysqli_format_*() functions', PHP_EOL;
echo str_pad(null, 75, ':'), PHP_EOL;
echo PHP_EOL;
// a new record
$new_record = array(
'title' => "A title with a 'sub title in it'", // is a string hence escaped
'price' => 25.6, // stays untouched
'discount' => 25, // stays untouched
'for_sale' => true, // becomes TRUE string
'published' => mysqli_escaped('UTC_TIMESTAMP()'), // stays untouched
// 'volumes' => array(1, 2, 3, 4), // gets serialized
);
// formatting glue: AND (for SELECTs, UPDATEs and MySQL Conditions)
$glue_and = mysqli_format_glue($client, $new_record, ' AND ');
echo 'mysqli_format_glue(AND): ', $glue_and, PHP_EOL;
echo PHP_EOL;
// formatting glue: OR (for SELECTs, UPDATEs and MySQL Conditions)
$glue_and = mysqli_format_glue($client, $new_record, ' OR ');
echo 'mysqli_format_glue(OR): ', $glue_and, PHP_EOL;
echo PHP_EOL;
// formatting glue: , (for UPDATEs, ON DUPLICATE KEY UPDATE, ...)
$glue_and = mysqli_format_glue($client, $new_record, ', ');
echo 'mysqli_format_glue(,): ', $glue_and, PHP_EOL;
echo PHP_EOL;
// formatting keys only
$keys = mysqli_format_keys($new_record);
echo 'mysqli_format_keys(): ', $keys, PHP_EOL;
echo PHP_EOL;
// formatting values only
$values = mysqli_format_values($client, $new_record);
echo 'mysqli_format_values(): ', $values, PHP_EOL;
echo "INSERT INTO `books` VALUES ({$values});", PHP_EOL;
echo PHP_EOL;
// formatting keys and values (INSERT/REPLACE ready)
$key_values = mysqli_format_key_values($client, $new_record);
echo 'mysqli_format_key_values(): ', $key_values, PHP_EOL;
echo "INSERT INTO `books` {$key_values};", PHP_EOL;
echo PHP_EOL;
// formatting keys and values and returns an array so you use it as you wish (INSERT/REPLACE ready)
$fmt_record = mysqli_format_in_place($client, $new_record);
echo 'mysqli_format_in_place(): '; var_export($fmt_record); echo PHP_EOL;
echo PHP_EOL;
});
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.