SSIMySQLi

/*
Explanation:
Before inline interpolation (example from Mike Samuel):

$stmt = $db->prepare(   'I met a ? in the ? who had a ? full of ?.  He said'
        .' "Hello, ?, How are you today\?"  "Fine," I replied'
        .' "but I can't seem to find my ?."  "Hmm," he said.'
        .' "I Can\'t help you with that."  "How about ? ? to'
        .' make you feel ?\?"');
$stmt->bind_param("sssssiss", $profession, $landmark, $container, $species_of_monkey, $proper_name, $common_household_item, $some_number, $beverage, $state_of_mind);

It is secure, but its as much of a pain to read as it is to write...

But what if we could just say
$db->query( 'I met a ^^profession in the ^^landmark who had a ^^container full of ^^species_of_monkey.  He said'
        .' "Hello, ^^proper_name, How are you today\?"  "Fine," I replied'
        .' "but I can't seem to find my ^^common_household_item."  "Hmm," he said.'
        .' "I Can\'t help you with that."  "How about ^^number ^^beverage to'
        .' make you feel ^^state_of_mind\?"');
and still be secure without adding anything that we don't already have (like b64d function in mysql)? well, we can :)
In addition to determining and passing data and query via separate channels, if someone tries to inline inject strings into the code or something in the query, it will fail.

You are welcome to use or sony(meaning pwn) this code, if you improve the code or manage to inject or break something, please let me know.
*/

// An attempt at forcing developers to use secure string interpolation in mysql queries with some integrated caching and direct csv output.
class SSIMysqli extends mysqli
{
    // Members
    protected static $mysqli;
    protected $memcache=false, $result=false, $bind_arr=array(), $row=0, $data=null, $key=null, $vars=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;
    }

    private function output_csv(&$vals, $key, $stream) { fputcsv($stream, $vals, ',', '"'); }

    //Public Methods
    public function __construct($db = MYSQLI_DB, $host = MYSQLI_HOST, $user = MYSQLI_USER, $pass = MYSQLI_PASS, $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 function __clone() { error_log("Can't clone Mysqli!"); die(); }

    public function query($query, $backtrace=array(), $vars=null) {
        // 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
        $this->vars = (is_null($this->vars)) ? $this->clean($GLOBALS) : $this->vars; // 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, $this->vars)) {
                $qvars[$var] = $this->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
        if(count($bind_params)>0) {
            array_unshift($bind_params, $bind_str);
            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( is_null($type) || strtoupper($type)=="MYSQLI_NUM" || $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;
            unset($this->data);
            return $data;
        }

        $tmp = array(); $ret = array(); // It's nice to initilaize arrays
        if($type==MYSQLI_DOC){
            $tmp = $this->fetch_array(MYSQLI_ASSOC,$prefix,$postfix,$join,$drop);
            array_push($ret, array_keys($tmp), array_values($tmp));
            $type=MYSQLI_NUM;
        }
        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
        $this->bind_arr = array();

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

    public function get_csv($type=NULL, $prefix=NULL, $postfix=NULL, $join=NULL, $drop=false)
    {
        $stream = fopen("php://output", 'w');
        if($this->memcache && !empty($this->data)) {
            $this->arr_csv($this->data, $stream);
        } else {
            $tmp=array();
            while($tmp = $this->fetch_array($type,$prefix,$postfix,$join,$drop)) { $this->arr_csv($tmp, $stream); }
        }
        fclose($stream);
    }

    public function arr_csv($data, $stream)
    {
        if(!is_array($data) || gettype($stream) != "resource") { return false; }
        if(is_array($data[0])) { array_walk($data, get_class($this).'::outputCSV', $stream); }
            else { $this->output_csv($data, null, $stream); }
    }

    public function num_rows()
    {
        return $this->result->num_rows;
    }
}