Jump to content
Science Forums

Project Secure Query

alexander
 Share

Recommended Posts

alexander Newbie

alexander

Posted
Posted

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";
}

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
 Share
Go to topic listing
×
×
  • Create New...