alexander Posted April 26, 2011 Report Posted April 26, 2011 So this is the first iteration of the secure query code in php. I would actually like for people to point out its defficiencies or insecurities, so feel free to play and comment. It is open, it is free, it is public, but attribution, please and if you want to use this commercially, contact me.... <?php class SecMysqli extends mysqli { // Members protected static $mysqli; protected $memcache=false, $result=false, $bind_arr=array(), $row=0, $data=null, $key=null; // Private Methods private function clean($vars) { $clean = array('GLOBALS', 'argc', 'argv', '_GET', '_POST', '_COOKIE', '_FILES', '_SERVER'); foreach($clean as $key) { if(array_key_exists($key, $vars)) { unset($vars[$key]); } } return $vars; } //Public Methods public function __construct($host = MYSQLI_HOST, $user = MYSQLI_USER, $pass = MYSQLI_PASS, $db = MYSQLI_DB, $port = MYSQLI_PORT, $mhost = MEM_SERVER, $mport = MEM_PORT, $mtime = MEM_TIMEOUT, $mctime = MEM_CONNECT_TIMEOUT, $mrtime = MEM_RETRY_TIMEOUT, $mcomp = MEM_COMPRESSION) { parent::__construct($host, $user, $pass, $db, $port); if(mysqli_connect_errno()) { error_log("Could not connect to database! Repent! The end is neigh!"); die(); } if(extension_loaded('memcached.so') && $mtime != 0){ $this->memcache = new Memcached(); if (!$this->memcache->addServer($mhost, $mport)) { error_log('Could not connect to MemCache server'); $obj = false; } else { $this->memcache->setOption(Memcached::OPT_CONNECT_TIMEOUT, $mctime); // connection timeout in milliseconds $this->memcache->setOption(Memcached::OPT_RETRY_TIMEOUT, $mrtime); // retry timeout in seconds $this->memcache->setOption(Memcached::OPT_COMPRESSION, $mcomp); // Set this to false if you ever start using memcached append } } } public static function conn() { if(!self::$mysqli) { if(!(defined('MYSQLI_USER') && defined('MYSQLI_PASS') && defined('MYSQLI_HOST') && defined('MYSQLI_DB') && defined('MYSQLI_PORT'))) { error_log("Please check config.php for correct database settings!"); die(); } self::$mysqli = new self(); } return self::$mysqli; } public function __clone() { error_log("Can't clone Mysqli!"); die(); } public function query($query, $backtrace=array()) { // I need this here to check cache so that i dont have to do that crazy thing down below every time before checking for cache $vars = $this->clean($GLOBALS); // globally defined vars $qvars = array(); // globally defined vars used in the query preg_match_all('/\^\^[a-zA-Z0-9\-_]+/m', $query, $matches); foreach($matches[0] as $var) { $var = substr($var, 2); if(array_key_exists($var, $vars)) { $qvars[$var] = $vars[$var]; } } // Check cache first if($this->memcache) { $this->key = $query; foreach($qvars as $name=>$var) { $this->key = preg_replace("/\^\^".$name."/", $var, $key_query); } $this->key = "mysql_".md5($this->key); $obj = $memcache->get($this->key); //check cache if($obj !== false) { $this->data = unserialize($obj); return true; } } // Because the point of this function is to prevent inline string concatenation, i am going to check for it. // If you remove this section, know that you are playing with fire and making the code insecure, so if anyone asks you to do it // DON'T!. I DO NOT ALLOW THE USE OF THIS CODE IF THIS SECTION IS REMOVED! This section may be modified to better fulfill its function // which is to detect and prevent concatenation of strings, specifically strings and variables in the line calling this function. // BEGIN if(!is_array($backtrace) || count($backtrace)>=0) { $backtrace = debug_backtrace(); } if(array_key_exists(0, $backtrace)) { $backtrace=$backtrace[0]; } if(!array_key_exists("file", $backtrace)) { error_log("We don't seem to have the right globals data"); return false; } $fh = file($backtrace["file"]); // All this just to deal with multi-line input and convert it to a single line (note line returns the last line in a multi-line split so this reads backwards $i=$backtrace["line"]-1; $line = ""; do { $line = preg_replace('/\s+/', ' ', $fh[$i].$line); $i--; } while(!preg_match('/[;}]\s*$/', $fh[$i]) || $i<=0); // This pulls quoted strings from a magic function call (yeah i know that wond do multi-level very well, but it will have to do for now) // then it evals it to get the value, basically this is a cheasy way to run it through php interpreter and determine if there is any // string concatenation happening there by checking the $ count before and after $pre = 0; $post = 0; if(preg_match_all('/'.$backtrace['function'].'\(([^()]*)\)/', $line, $matches)) { foreach($matches[0] as $line) { if(preg_match_all('/["\'].*["\']/', $line, $query_matches)) { foreach($query_matches[0] as $query_match) { $pre = preg_match('/\$/', $query_match); @eval('$post='.$query_match.';'); // This should be safe as it will reference local scope variables which dont exist. and if they do, its programmer's fault and this will still not work in terms of code injection if($pre != preg_match('/\$/', $post)){ error_log("There seems to be some string concatenation in the function call in {$backtrace['file']}, at line {$backtrace['line']}, around {$query_match}"); return false; } } } else { error_log("invalid file format, please call {$backtrace['function']}(\$this_link, \"query\")"); return false; } } } else { error_log("Invalid calling line format"); return false; } // END $bind_str = ""; $bind_params = array(); $query = preg_replace('/\s+/', ' ', $query); foreach($qvars as $var => $val) { if(is_null($val)) { if(preg_match('/where.*\^\^'.$var.'/', $query)) { preg_match('/(\s[=<>!\s]*(not|is|like)*)*(\^\^'.$var.')/', $query, $matches); $comp = (preg_match('/(!|not)/',$matches[0])) ? " is not " : " is "; $query = preg_replace('/(\s[=<>!\s]*(not|is|like)*)*(\^\^'.$var.')/', $comp."NULL", $query); } else { $query = preg_replace('/(\^\^'.$var.')/', "?", $query); } } else { $query = preg_replace('/(\^\^'.$var.')/', "?", $query); switch($val){ case (is_int($val)): $bind_str .= "i"; break; case (is_float($val)): $bind_str .= "d"; break; default: $bind_str .= "s"; break; } array_push($bind_params, &$qvars[$var]); } } // Prepare and execute the query if(!$this || !preg_match('/.*mysqli.*/i',get_class($this))) { error_log("Didn't get a legitimate mysqli resource"); return false; } $this->result=$this->prepare($query); if($this->errno) { error_log($this->error); return false; } // check for mysql errors array_unshift($bind_params, $bind_str); if(count($bind_params)>0) { call_user_func_array(array($this->result,'bind_param'), $bind_params); if($this->result->errno) { error_log($this->result->error); return false; } // again } $this->result->execute(); if($this->result->errno) { error_log($this->result->error); return false; } // and again $this->result->store_result(); // This will bind the result array if(!$fields = $this->result->result_metadata()) { error_log("The result contains no data." . $this->result->error); return false; } if(!$fields = $fields->fetch_fields()) { error_log("Could not fetch fields: " . $this->result->error); return false;} $bind_cmd = '$this->result->bind_result('; foreach($fields as $field) { $bind_cmd .='$this->bind_arr[\''.$field->name.'\'],'; } $bind_cmd = substr($bind_cmd, 0, -1).");"; eval($bind_cmd); return true; } private function fetch_array($type=NULL, $prefix=NULL, $postfix=NULL, $join="_", $drop=false) //drop will allow you to drop the pre/postfix { // This actually magically fetches data if($this->result->num_rows <= 0) { error_log("We need one or more results in the result set first."); return false; } if(!$this->result->fetch()) return FALSE; while (list($key, $val) = each($this->bind_arr)) { $ret[$key] = $val; } reset($this->bind_arr); if(!$type || strtoupper($type)=="MYSQLI_NUM") $ret=array_values($ret); if($prefix || $postfix) { if(!$drop) { if($prefix == "MYSQLI_ROW") { $prefix = $this->row; } if($postfix == "MYSQLI_ROW") { $postfix = $this->row; } foreach($ret as $key=>$val) { $ret[(($prefix||$prefix=="0")?$prefix.$join:"").$key.(($postfix||$postfix=="0")?$join.$postfix:"")] = $val; unset($ret[$key]); } } else { foreach($ret as $key=>$val) { $ret[preg_replace('/('.(($prefix||$prefix=="0")?$prefix.$join:"").'|'.(($postfix||$postfix=="0")?$join.$postfix:"").')', '',$key)] = $val; unset($ret[$key]); } } } $this->row++; return $ret; } public function fetch_all($type=NULL, $prefix=NULL, $postfix=NULL, $join=NULL, $drop=false) { if($this->memcache && !empty($this->data)) { $data = $this->data; $this->data = null; return $data; } $tmp = array(); $ret = array(); // It's nice to initilaize arrays while($tmp = $this->fetch_array($type,$prefix,$postfix,$join,$drop)) { $ret[] = $tmp; } $this->result->free_result(); // Free the original result set (note on change, in mysqli query free is aliased to free_result, always free the result as per the documentation if($this->memcache) { // Store data in cache if its not there yet $timeout = (defined(MEM_TIMEOUT)) ? MEM_TIMEOUT : 3600 ; if(!$memcache->replace($key, serialize($res), $timeout)) { if(!$memcache->add($key, serialize($res),$timeout)) { error_log('Failed to store data in MemCache'); } } } return $ret; } } You can use it as such, for example: config.php: <?php defined('MYSQLI_HOST') || define('MYSQLI_HOST', '127.0.0.1'); defined('MYSQLI_USER') || define('MYSQLI_USER', 'user'); defined('MYSQLI_PASS') || define('MYSQLI_PASS', 'pass'); defined('MYSQLI_DB') || define('MYSQLI_DB', 'somedb'); defined('MYSQLI_PORT') || define('MYSQLI_PORT', 3306); test.php: <?php require_once('config.php'); require_once('sec_mysqli.php'); $db = new SecMysqli(); $test_data = "%test%"; $null_test_data = NULL; if($db->query("select * from test_table where `data` like ^^test_data and `data` not = ^^null_test_data")) { echo "great success"; print_r($db->fetch_all()); } else { echo "Denied, check the log"; } Quote
alexander Posted April 26, 2011 Author Report Posted April 26, 2011 oh also i would just as greatly appreciate if someone thoroughly pwns this code, that's the only way to make it more secure... Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.