/**
* Parse SQL where parameters
*/
protected function parse_where()
{
$key = array_search('where', $this->parse_query_lower);
if ($key == FALSE) {
$this->parse_where = 'return TRUE;';
return $this;
}
$string = $this->parse_query[$key + 1];
if (trim($string) == '') {
return $this->parse_where = 'return TRUE;';
}
if (self::$cache_patterns && self::$cache_replacements) {
$patterns = self::$cache_patterns;
$replacements = self::$cache_replacements;
} else {
/**
* SQL Functions
*/
$patterns[] = '/LOWER\\((.*)\\)/ie';
$patterns[] = '/UPPER\\((.*)\\)/ie';
$patterns[] = '/TRIM\\((.*)\\)/ie';
$replacements[] = "'strtolower(\\1)'";
$replacements[] = "'strtoupper(\\1)'";
$replacements[] = "'trim(\\1)'";
/**
* Basics SQL operators
*
* (([FUNCTION]+)(\())?([FIELDNAME]+)(\))?(SPACE)+(OPERATOR)(SPACE)+(QUOTES)(VALUE)(QUOTES)(SPACE)* CASE INSENSITIVE
* *see header for functions
*/
// Attempt at wrapping all symbolic operators in spaces at once
$patterns[] = '/(.[a-zA-z0-9\\._]+)(<|>|=|!=|>=|<=|<>)+(\\s)*/';
$patterns[] = '/(.)(<|>|=|!=|>=|<=|<>)+([a-zA-z0-9\\._]+)*/';
$patterns[] = '/(([a-zA-Z0-9\\._]+)(\\())?([a-zA-Z0-9\\._]+)(\\))?(\\s)+(=|IS)(\\s)+([[:digit:]]+)(\\s)*/ie';
$patterns[] = '/(([a-zA-Z0-9\\._]+)(\\())?([a-zA-Z0-9\\._]+)(\\))?(\\s)+(=|IS)(\\s)+(\'|\\")(.*)(\'|\\")(\\s)*/ie';
# $patterns[] = '/(([a-zA-Z0-9\._]+)(\())?([a-zA-Z0-9\._]+)(\))?((?:\s+)?(=)(?:\s+)?|(?:\s+)(IS)(?:\s+))(\'|\")(.*)(\'|\")(\s)*/ie'; // test for no spaces
$patterns[] = '/(([a-zA-Z0-9\\._]+)(\\())?([a-zA-Z0-9\\._]+)(\\))?(\\s)+(>|<)(\\s)+([[:digit:]]+)(\\s)*/ie';
$patterns[] = '/(([a-zA-Z0-9\\._]+)(\\())?([a-zA-Z0-9\\._]+)(\\))?(\\s)+(<=|>=)(\\s)+([[:digit:]]+)(\\s)*/ie';
$patterns[] = '/(([a-zA-Z0-9\\._]+)(\\())?([a-zA-Z0-9\\._]+)(\\))?(\\s)+(<>|IS NOT|!=)(\\s)+([[:digit:]]+)(\\s)*/ie';
$patterns[] = '/(([a-zA-Z0-9\\._]+)(\\())?([a-zA-Z0-9\\._]+)(\\))?(\\s)+(<>|IS NOT|!=)(\\s)+(\'|\\")(.*)(\'|\\")(\\s)*/ie';
$patterns[] = '/(([a-zA-Z0-9\\._]+)(\\())?([a-zA-Z0-9\\._]+)(\\))?(\\s)+(IS)?(NOT IN)(\\s)+\\((.*)\\)/ie';
$patterns[] = '/(([a-zA-Z0-9\\._]+)(\\())?([a-zA-Z0-9\\._]+)(\\))?(\\s)+(IS)?(IN)(\\s)+\\((.*)\\)/ie';
$replacements[] = "\\1 \\2\\3";
$replacements[] = "\\1\\2 \\3 ";
$replacements[] = "'\\1'.\$this->parse_where_key(\"\\4\").'\\5 == \\9 '";
$replacements[] = "'\\1'.\$this->parse_where_key(\"\\4\").'\\5 == \"\\10\" '";
$replacements[] = "'\\1'.\$this->parse_where_key(\"\\4\").'\\5 \\7 \\9 '";
$replacements[] = "'\\1'.\$this->parse_where_key(\"\\4\").'\\5 \\7 \\9 '";
$replacements[] = "'\\1'.\$this->parse_where_key(\"\\4\").'\\5 != \\9 '";
$replacements[] = "'\\1'.\$this->parse_where_key(\"\\4\").'\\5 != \"\\10\" '";
$replacements[] = "'\\1'.\$this->parse_where_key(\"\\4\").'\\5 != ('.\$this->parse_in(\"\\10\").') '";
$replacements[] = "'\\1'.\$this->parse_where_key(\"\\4\").'\\5 == ('.\$this->parse_in(\"\\10\").') '";
self::$cache_patterns = $patterns;
self::$cache_replacements = $replacements;
}
/**
* match SQL operators
*/
$ereg = array('%' => '(.*)', '_' => '(.)');
$patterns[] = '/([a-zA-Z0-9\\.]+)(\\s)+LIKE(\\s)*(\'|\\")(.*)(\'|\\")/ie';
$patterns[] = '/([a-zA-Z0-9\\.]+)(\\s)+ILIKE(\\s)*(\'|\\")(.*)(\'|\\")/ie';
$patterns[] = '/([a-zA-Z0-9\\.]+)(\\s)+NOT LIKE(\\s)*(\'|\\")(.*)(\'|\\")/ie';
$patterns[] = '/([a-zA-Z0-9\\.]+)(\\s)+NOT ILIKE(\\s)*(\'|\\")(.*)(\'|\\")/ie';
// TODO: use preg to replace ereg
$replacements[] = "'preg_match(\"'.strtr(\"\\5\", \$ereg).'\", '.\$this->parse_where_key(\"\\1\").')'";
$replacements[] = "'eregi(\"'.strtr(\"\\5\", \$ereg).'\", '.\$this->parse_where_key(\"\\1\").')'";
$replacements[] = "'!preg_match(\"'.strtr(\"\\5\", \$ereg).'\", '.\$this->parse_where_key(\"\\1\").')'";
$replacements[] = "'!eregi(\"'.strtr(\"\\5\", \$ereg).'\", '.\$this->parse_where_key(\"\\1\").')'";
/*
* Change:
* This was returning whatever is in parse_where even if it fails to match a operator pattern.
* this causes eval to eval anything tossed in there, and also throws errors like
* Use of undefined constant id - assumed 'id'
* So I chagned it to check that proper replacements were being sent to eval
*
* This did previsouly allow some queries to work but for the wrong unexpected reasons
* eg id > '5' quotes are not allowed and the pattern never matched
* BUT this was working by php forgiving syntax and
* parsing it as $id while throwing an undefined notice about it
* Which was confusing as hell
*
* So this now returns false as parse where if $row[] isnt found in the string,
* meaning it didnt hit an operator replacement
*
* No telling what this might break, theres is no documentation, and I do not know if this fallthrough is by design
*/
$whereStr = stripslashes(trim(preg_replace($patterns, $replacements, $string)));
// force an operator pattern match
if ($whereStr == $string || strpos($whereStr, '$row') === false) {
$this->parse_where = 'return false;';
} else {
$this->parse_where = "return " . $whereStr . ";";
}
return $this;
}
<?php
/*
* Project: Absynthe sql4array
* File: sql4array.example.php5
* Author: Absynthe <*****@*****.**>
* Webste: http://absynthe.is.free.fr/sql4array/
* Version: alpha 1
* Date: 30/04/2007
* License: LGPL
*/
header("Content-type: text");
require "./sql4array.class.php";
for ($i = 0; $i < 20; $i++) {
$array[$i][id] = rand(0, 20);
$array[$i][foo] = md5(rand(0, 10000));
}
$sql = new sql4array();
$a = $sql->query("SELECT id, foo FROM array");
$b = $sql->query("SELECT id, foo FROM array WHERE id > 10");
$c = $sql->query("SELECT id AS i, foo AS f FROM array WHERE i > 10");
$d = $sql->query("SELECT id AS i, foo AS f FROM array WHERE i > 10 AND f LIKE '%a%'");
$e = $sql->query("SELECT id AS iiiiii, foo AS fooooooooo FROM array WHERE iiiiii != 10");
var_dump($a);
var_dump($b);
var_dump($c);
var_dump($d);
var_dump($e);
