secret
Last active

mysqli_ helper functions that escape data internally and should be SQL injection free :)

  • Download Gist
CA_MySQLi_Lite.php
PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333
<?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;
?>
CA_MySQLi_Lite_Samples.php
PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139
<?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;
});
?>

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.