123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663 |
- <?php
- /**
- * @author col.shrapnel@gmail.com
- * @link http://phpfaq.ru/safemysql
- *
- * Safe and convenient way to handle SQL queries utilizing type-hinted placeholders.
- *
- * Key features
- * - set of helper functions to get the desired result right out of query, like in PEAR::DB
- * - conditional query building using parse() method to build queries of whatever comlexity,
- * while keeping extra safety of placeholders
- * - type-hinted placeholders
- *
- * Type-hinted placeholders are great because
- * - safe, as any other [properly implemented] placeholders
- * - no need for manual escaping or binding, makes the code extra DRY
- * - allows support for non-standard types such as identifier or array, which saves A LOT of pain in the back.
- *
- * Supported placeholders at the moment are:
- *
- * ?s ("string") - strings (also DATE, FLOAT and DECIMAL)
- * ?i ("integer") - the name says it all
- * ?n ("name") - identifiers (table and field names)
- * ?a ("array") - complex placeholder for IN() operator (substituted with string of 'a','b','c' format, without parentesis)
- * ?u ("update") - complex placeholder for SET operator (substituted with string of `field`='value',`field`='value' format)
- * and
- * ?p ("parsed") - special type placeholder, for inserting already parsed statements without any processing, to avoid double parsing.
- *
- * Connection:
- *
- * $db = new SafeMySQL(); // with default settings
- *
- * $opts = array(
- * 'user' => 'user',
- * 'pass' => 'pass',
- * 'db' => 'db',
- * 'charset' => 'latin1'
- * );
- * $db = new SafeMySQL($opts); // with some of the default settings overwritten
- *
- * Alternatively, you can just pass an existing mysqli instance that will be used to run queries
- * instead of creating a new connection.
- * Excellent choice for migration!
- *
- * $db = new SafeMySQL(['mysqli' => $mysqli]);
- *
- * Some examples:
- *
- * $name = $db->getOne('SELECT name FROM table WHERE id = ?i',$_GET['id']);
- * $data = $db->getInd('id','SELECT * FROM ?n WHERE id IN ?a','table', array(1,2));
- * $data = $db->getAll("SELECT * FROM ?n WHERE mod=?s LIMIT ?i",$table,$mod,$limit);
- *
- * $ids = $db->getCol("SELECT id FROM tags WHERE tagname = ?s",$tag);
- * $data = $db->getAll("SELECT * FROM table WHERE category IN (?a)",$ids);
- *
- * $data = array('offers_in' => $in, 'offers_out' => $out);
- * $sql = "INSERT INTO stats SET pid=?i,dt=CURDATE(),?u ON DUPLICATE KEY UPDATE ?u";
- * $db->query($sql,$pid,$data,$data);
- *
- * if ($var === NULL) {
- * $sqlpart = "field is NULL";
- * } else {
- * $sqlpart = $db->parse("field = ?s", $var);
- * }
- * $data = $db->getAll("SELECT * FROM table WHERE ?p", $bar, $sqlpart);
- *
- */
- class SafeMySQL
- {
- protected $conn;
- protected $stats;
- protected $emode;
- protected $exname;
- protected $defaults = array(
- 'host' => 'localhost',
- 'user' => 'root',
- 'pass' => '',
- 'db' => 'test',
- 'port' => NULL,
- 'socket' => NULL,
- 'pconnect' => FALSE,
- 'charset' => 'utf8',
- 'errmode' => 'exception', //or 'error'
- 'exception' => 'Exception', //Exception class name
- );
- const RESULT_ASSOC = MYSQLI_ASSOC;
- const RESULT_NUM = MYSQLI_NUM;
- function __construct($opt = array())
- {
- $opt = array_merge($this->defaults,$opt);
- $this->emode = $opt['errmode'];
- $this->exname = $opt['exception'];
- if (isset($opt['mysqli']))
- {
- if ($opt['mysqli'] instanceof mysqli)
- {
- $this->conn = $opt['mysqli'];
- return;
- } else {
- $this->error("mysqli option must be valid instance of mysqli class");
- }
- }
- if ($opt['pconnect'])
- {
- $opt['host'] = "p:".$opt['host'];
- }
- @$this->conn = mysqli_connect($opt['host'], $opt['user'], $opt['pass'], $opt['db'], $opt['port'], $opt['socket']);
- if ( !$this->conn )
- {
- $this->error(mysqli_connect_errno()." ".mysqli_connect_error());
- }
- mysqli_set_charset($this->conn, $opt['charset']) or $this->error(mysqli_error($this->conn));
- unset($opt); // I am paranoid
- }
- /**
- * Conventional function to run a query with placeholders. A mysqli_query wrapper with placeholders support
- *
- * Examples:
- * $db->query("DELETE FROM table WHERE id=?i", $id);
- *
- * @param string $query - an SQL query with placeholders
- * @param mixed $arg,... unlimited number of arguments to match placeholders in the query
- * @return resource|FALSE whatever mysqli_query returns
- */
- public function query()
- {
- return $this->rawQuery($this->prepareQuery(func_get_args()));
- }
- /**
- * Conventional function to fetch single row.
- *
- * @param resource $result - myqli result
- * @param int $mode - optional fetch mode, RESULT_ASSOC|RESULT_NUM, default RESULT_ASSOC
- * @return array|FALSE whatever mysqli_fetch_array returns
- */
- public function fetch($result,$mode=self::RESULT_ASSOC)
- {
- return mysqli_fetch_array($result, $mode);
- }
- /**
- * Conventional function to get number of affected rows.
- *
- * @return int whatever mysqli_affected_rows returns
- */
- public function affectedRows()
- {
- return mysqli_affected_rows ($this->conn);
- }
- /**
- * Conventional function to get last insert id.
- *
- * @return int whatever mysqli_insert_id returns
- */
- public function insertId()
- {
- return mysqli_insert_id($this->conn);
- }
- /**
- * Conventional function to get number of rows in the resultset.
- *
- * @param resource $result - myqli result
- * @return int whatever mysqli_num_rows returns
- */
- public function numRows($result)
- {
- return mysqli_num_rows($result);
- }
- /**
- * Conventional function to free the resultset.
- */
- public function free($result)
- {
- mysqli_free_result($result);
- }
- /**
- * Helper function to get scalar value right out of query and optional arguments
- *
- * Examples:
- * $name = $db->getOne("SELECT name FROM table WHERE id=1");
- * $name = $db->getOne("SELECT name FROM table WHERE id=?i", $id);
- *
- * @param string $query - an SQL query with placeholders
- * @param mixed $arg,... unlimited number of arguments to match placeholders in the query
- * @return string|FALSE either first column of the first row of resultset or FALSE if none found
- */
- public function getOne()
- {
- $query = $this->prepareQuery(func_get_args());
- if ($res = $this->rawQuery($query))
- {
- $row = $this->fetch($res);
- if (is_array($row)) {
- return reset($row);
- }
- $this->free($res);
- }
- return FALSE;
- }
- /**
- * Helper function to get single row right out of query and optional arguments
- *
- * Examples:
- * $data = $db->getRow("SELECT * FROM table WHERE id=1");
- * $data = $db->getRow("SELECT * FROM table WHERE id=?i", $id);
- *
- * @param string $query - an SQL query with placeholders
- * @param mixed $arg,... unlimited number of arguments to match placeholders in the query
- * @return array|FALSE either associative array contains first row of resultset or FALSE if none found
- */
- public function getRow()
- {
- $query = $this->prepareQuery(func_get_args());
- if ($res = $this->rawQuery($query)) {
- $ret = $this->fetch($res);
- $this->free($res);
- return $ret;
- }
- return FALSE;
- }
- /**
- * Helper function to get single column right out of query and optional arguments
- *
- * Examples:
- * $ids = $db->getCol("SELECT id FROM table WHERE cat=1");
- * $ids = $db->getCol("SELECT id FROM tags WHERE tagname = ?s", $tag);
- *
- * @param string $query - an SQL query with placeholders
- * @param mixed $arg,... unlimited number of arguments to match placeholders in the query
- * @return array enumerated array of first fields of all rows of resultset or empty array if none found
- */
- public function getCol()
- {
- $ret = array();
- $query = $this->prepareQuery(func_get_args());
- if ( $res = $this->rawQuery($query) )
- {
- while($row = $this->fetch($res))
- {
- $ret[] = reset($row);
- }
- $this->free($res);
- }
- return $ret;
- }
- /**
- * Helper function to get all the rows of resultset right out of query and optional arguments
- *
- * Examples:
- * $data = $db->getAll("SELECT * FROM table");
- * $data = $db->getAll("SELECT * FROM table LIMIT ?i,?i", $start, $rows);
- *
- * @param string $query - an SQL query with placeholders
- * @param mixed $arg,... unlimited number of arguments to match placeholders in the query
- * @return array enumerated 2d array contains the resultset. Empty if no rows found.
- */
- public function getAll()
- {
- $ret = array();
- $query = $this->prepareQuery(func_get_args());
- if ( $res = $this->rawQuery($query) )
- {
- while($row = $this->fetch($res))
- {
- $ret[] = $row;
- }
- $this->free($res);
- }
- return $ret;
- }
- /**
- * Helper function to get all the rows of resultset into indexed array right out of query and optional arguments
- *
- * Examples:
- * $data = $db->getInd("id", "SELECT * FROM table");
- * $data = $db->getInd("id", "SELECT * FROM table LIMIT ?i,?i", $start, $rows);
- *
- * @param string $index - name of the field which value is used to index resulting array
- * @param string $query - an SQL query with placeholders
- * @param mixed $arg,... unlimited number of arguments to match placeholders in the query
- * @return array - associative 2d array contains the resultset. Empty if no rows found.
- */
- public function getInd()
- {
- $args = func_get_args();
- $index = array_shift($args);
- $query = $this->prepareQuery($args);
- $ret = array();
- if ( $res = $this->rawQuery($query) )
- {
- while($row = $this->fetch($res))
- {
- $ret[$row[$index]] = $row;
- }
- $this->free($res);
- }
- return $ret;
- }
- /**
- * Helper function to get a dictionary-style array right out of query and optional arguments
- *
- * Examples:
- * $data = $db->getIndCol("name", "SELECT name, id FROM cities");
- *
- * @param string $index - name of the field which value is used to index resulting array
- * @param string $query - an SQL query with placeholders
- * @param mixed $arg,... unlimited number of arguments to match placeholders in the query
- * @return array - associative array contains key=value pairs out of resultset. Empty if no rows found.
- */
- public function getIndCol()
- {
- $args = func_get_args();
- $index = array_shift($args);
- $query = $this->prepareQuery($args);
- $ret = array();
- if ( $res = $this->rawQuery($query) )
- {
- while($row = $this->fetch($res))
- {
- $key = $row[$index];
- unset($row[$index]);
- $ret[$key] = reset($row);
- }
- $this->free($res);
- }
- return $ret;
- }
- /**
- * Function to parse placeholders either in the full query or a query part
- * unlike native prepared statements, allows ANY query part to be parsed
- *
- * useful for debug
- * and EXTREMELY useful for conditional query building
- * like adding various query parts using loops, conditions, etc.
- * already parsed parts have to be added via ?p placeholder
- *
- * Examples:
- * $query = $db->parse("SELECT * FROM table WHERE foo=?s AND bar=?s", $foo, $bar);
- * echo $query;
- *
- * if ($foo) {
- * $qpart = $db->parse(" AND foo=?s", $foo);
- * }
- * $data = $db->getAll("SELECT * FROM table WHERE bar=?s ?p", $bar, $qpart);
- *
- * @param string $query - whatever expression contains placeholders
- * @param mixed $arg,... unlimited number of arguments to match placeholders in the expression
- * @return string - initial expression with placeholders substituted with data.
- */
- public function parse()
- {
- return $this->prepareQuery(func_get_args());
- }
- /**
- * function to implement whitelisting feature
- * sometimes we can't allow a non-validated user-supplied data to the query even through placeholder
- * especially if it comes down to SQL OPERATORS
- *
- * Example:
- *
- * $order = $db->whiteList($_GET['order'], array('name','price'));
- * $dir = $db->whiteList($_GET['dir'], array('ASC','DESC'));
- * if (!$order || !dir) {
- * throw new http404(); //non-expected values should cause 404 or similar response
- * }
- * $sql = "SELECT * FROM table ORDER BY ?p ?p LIMIT ?i,?i"
- * $data = $db->getArr($sql, $order, $dir, $start, $per_page);
- *
- * @param string $iinput - field name to test
- * @param array $allowed - an array with allowed variants
- * @param string $default - optional variable to set if no match found. Default to false.
- * @return string|FALSE - either sanitized value or FALSE
- */
- public function whiteList($input,$allowed,$default=FALSE)
- {
- $found = array_search($input,$allowed);
- return ($found === FALSE) ? $default : $allowed[$found];
- }
- /**
- * function to filter out arrays, for the whitelisting purposes
- * useful to pass entire superglobal to the INSERT or UPDATE query
- * OUGHT to be used for this purpose,
- * as there could be fields to which user should have no access to.
- *
- * Example:
- * $allowed = array('title','url','body','rating','term','type');
- * $data = $db->filterArray($_POST,$allowed);
- * $sql = "INSERT INTO ?n SET ?u";
- * $db->query($sql,$table,$data);
- *
- * @param array $input - source array
- * @param array $allowed - an array with allowed field names
- * @return array filtered out source array
- */
- public function filterArray($input,$allowed)
- {
- foreach(array_keys($input) as $key )
- {
- if ( !in_array($key,$allowed) )
- {
- unset($input[$key]);
- }
- }
- return $input;
- }
- /**
- * Function to get last executed query.
- *
- * @return string|NULL either last executed query or NULL if were none
- */
- public function lastQuery()
- {
- $last = end($this->stats);
- return $last['query'];
- }
- /**
- * Function to get all query statistics.
- *
- * @return array contains all executed queries with timings and errors
- */
- public function getStats()
- {
- return $this->stats;
- }
- /**
- * protected function which actually runs a query against Mysql server.
- * also logs some stats like profiling info and error message
- *
- * @param string $query - a regular SQL query
- * @return mysqli result resource or FALSE on error
- */
- protected function rawQuery($query)
- {
- $start = microtime(TRUE);
- $res = mysqli_query($this->conn, $query);
- $timer = microtime(TRUE) - $start;
- $this->stats[] = array(
- 'query' => $query,
- 'start' => $start,
- 'timer' => $timer,
- );
- if (!$res)
- {
- $error = mysqli_error($this->conn);
-
- end($this->stats);
- $key = key($this->stats);
- $this->stats[$key]['error'] = $error;
- $this->cutStats();
-
- $this->error("$error. Full query: [$query]");
- }
- $this->cutStats();
- return $res;
- }
- protected function prepareQuery($args)
- {
- $query = '';
- $raw = array_shift($args);
- $array = preg_split('~(\?[nsiuap])~u',$raw,-1,PREG_SPLIT_DELIM_CAPTURE);
- $anum = count($args);
- $pnum = floor(count($array) / 2);
- if ( $pnum != $anum )
- {
- $this->error("Number of args ($anum) doesn't match number of placeholders ($pnum) in [$raw]");
- }
- foreach ($array as $i => $part)
- {
- if ( ($i % 2) == 0 )
- {
- $query .= $part;
- continue;
- }
- $value = array_shift($args);
- switch ($part)
- {
- case '?n':
- $part = $this->escapeIdent($value);
- break;
- case '?s':
- $part = $this->escapeString($value);
- break;
- case '?i':
- $part = $this->escapeInt($value);
- break;
- case '?a':
- $part = $this->createIN($value);
- break;
- case '?u':
- $part = $this->createSET($value);
- break;
- case '?p':
- $part = $value;
- break;
- }
- $query .= $part;
- }
- return $query;
- }
- protected function escapeInt($value)
- {
- if ($value === NULL)
- {
- return 'NULL';
- }
- if(!is_numeric($value))
- {
- $this->error("Integer (?i) placeholder expects numeric value, ".gettype($value)." given");
- return FALSE;
- }
- if (is_float($value))
- {
- $value = number_format($value, 0, '.', ''); // may lose precision on big numbers
- }
- return $value;
- }
- protected function escapeString($value)
- {
- if ($value === NULL)
- {
- return 'NULL';
- }
- return "'".mysqli_real_escape_string($this->conn,$value)."'";
- }
- protected function escapeIdent($value)
- {
- if ($value)
- {
- return "`".str_replace("`","``",$value)."`";
- } else {
- $this->error("Empty value for identifier (?n) placeholder");
- }
- }
- protected function createIN($data)
- {
- if (!is_array($data))
- {
- $this->error("Value for IN (?a) placeholder should be array");
- return;
- }
- if (!$data)
- {
- return 'NULL';
- }
- $query = $comma = '';
- foreach ($data as $value)
- {
- $query .= $comma.$this->escapeString($value);
- $comma = ",";
- }
- return $query;
- }
- protected function createSET($data)
- {
- if (!is_array($data))
- {
- $this->error("SET (?u) placeholder expects array, ".gettype($data)." given");
- return;
- }
- if (!$data)
- {
- $this->error("Empty array for SET (?u) placeholder");
- return;
- }
- $query = $comma = '';
- foreach ($data as $key => $value)
- {
- if ( is_int($value) )
- {
- // Bit types coerced from strings are NOT the same as int from string.
- // In any event, int is inherently safe because they are NOT strings!
- $query .= $comma.$this->escapeIdent($key).'='.$this->escapeInt($value);
- } else {
- $query .= $comma.$this->escapeIdent($key).'='.$this->escapeString($value);
- }
- $comma = ",";
- }
- return $query;
- }
- protected function error($err)
- {
- $err = __CLASS__.": ".$err;
- if ( $this->emode == 'error' )
- {
- $err .= ". Error initiated in ".$this->caller().", thrown";
- trigger_error($err,E_USER_ERROR);
- } else {
- throw new $this->exname($err);
- }
- }
- protected function caller()
- {
- $trace = debug_backtrace();
- $caller = '';
- foreach ($trace as $t)
- {
- if ( isset($t['class']) && $t['class'] == __CLASS__ )
- {
- $caller = $t['file']." on line ".$t['line'];
- } else {
- break;
- }
- }
- return $caller;
- }
- /**
- * On a long run we can eat up too much memory with mere statsistics
- * Let's keep it at reasonable size, leaving only last 100 entries.
- */
- protected function cutStats()
- {
- if ( count($this->stats) > 100 )
- {
- reset($this->stats);
- $first = key($this->stats);
- unset($this->stats[$first]);
- }
- }
- }
|