Basic SQL Interpreter
Posted: Sun May 11, 2008 7:12 am
I have been coding a basic SQL interpreter, to allow php arrays to be manipulated by SQL type queries.
I know there are many flaws in this code such as security holes from eval, could you please pass your opinion on this code. I am interested to know your thoughts, and whether improving it would be a worth while project or are there better ways of achieving such manipulation.
The idea behind this is to allow SQL queries to be sent to data of all type, using other functions to convert data types (ini/csv/dir&file) to arrays.
Thanks, Seb
----------------------------------------
Function Code:
----------------------------------------
Example Page Code:
I know there are many flaws in this code such as security holes from eval, could you please pass your opinion on this code. I am interested to know your thoughts, and whether improving it would be a worth while project or are there better ways of achieving such manipulation.
The idea behind this is to allow SQL queries to be sent to data of all type, using other functions to convert data types (ini/csv/dir&file) to arrays.
Thanks, Seb
----------------------------------------
Function Code:
Code: Select all
<?
## ##
## Example Data ##
## ##
$_DATA['table1'][] = array("name" => "Sebastian", "age" => 18, "male" => true);
$_DATA['table1'][] = array("name" => "Lawrence", "age" => 16, "male" => true);
$_DATA['table1'][] = array("name" => "Olivia", "age" => 10, "male" => false);
$_DATA['table1'][] = array("name" => "Dad", "age" => 50, "male" => true);
$_DATA['table1'][] = array("name" => "Mum", "age" => 40, "male" => false);
$_DATA['table1'][] = array("name" => "Sebastian", "age" => 56, "male" => true);
$_DATA['table1'][] = array("name" => "Lawrence", "age" => 19, "male" => true);
$_DATA['table1'][] = array("name" => "Olivia", "age" => 24, "male" => false);
$_DATA['table1'][] = array("name" => "Dad", "age" => 10, "male" => true);
$_DATA['table1'][] = array("name" => "Mum", "age" => 70, "male" => false);
//$result=php_multisort($_DATA['table1'],array(array('key'=>'name'),array('key'=>'age','sort'=>'desc')));
//var_dump($result);
## ##
## Main Function ##
## ##
function sql($qry){
global $_DATA;
$sql=sql_parse($qry);
var_dump($sql);
$data=$_DATA[$sql['table']['name']]; // Fetch Data
if($sql['condit']){ $data=sql_conditions($data,$sql['crules']); } // Check
if($sql['orderb']){ $data=sql_multisort($data,$sql['orders']); } // Sort Data
if(!$sql['colall']){$data=sql_select($data,$sql['column']); } // Return Req'd Columns
var_dump($data);
}
## ##
## Query Parser ##
## ##
function sql_parse($qry){
$qry=ereg_replace(', ',',',$qry);
$qry=ereg_replace(',',' ',$qry);
$bits=explode(' ',$qry);
// Walk Query
$pos=0;$do=true;
if($bits[$pos]=="SELECT") {$parse['select']=true;$pos++;} // SELECT Method
if($bits[$pos]=="DISTINCT") {$parse['unique']=true;$pos++;} // Distinct Results
if($bits[$pos]=="*") {$parse['colall']=true;$pos++;} // All Columns (*)
while(!$parse['colall']){
$parse['column'][$pos]['name']=$bits[$pos]; // Column Name
if($bits[$pos+1]=="AS"){
$parse['column'][$pos]['alias']=$bits[$pos+2]; // Column Alias
$pos++;$pos++;
}
$pos++;
if($bits[$pos]=="FROM"){ $pos++;break; } // Break and Continue
}
if($bits[$pos]=="FROM"){ $pos++; }
$parse['table']['name']=$bits[$pos];$pos++; // Table Name
if($bits[$pos]=="AS"){
$parse['table']['alias']=$bits[$pos+1]; // Table Alias
$pos++;$pos++;
}
if($bits[$pos]=="WHERE") {$parse['condit']=true;$pos++;}
while($parse['condit']){
$parse['crules'].=" ".$bits[$pos];$pos++;
if(!$bits[$pos]||$bits[$pos]=="ORDER"){break;}
}
if($bits[$pos]=="ORDER") {$pos++;}
if($bits[$pos]=="BY") {$parse['orderb']=true;$pos++;} // Order By
while($parse['orderb']){
$parse['orders'][$pos]['column']=$bits[$pos]; // Column Name
$pos++;
if($bits[$pos]=="ASC"||$bits[$pos]=="DESC"){
$parse['orders'][$pos-1]['sort']=$bits[$pos]; // Sort Order
$pos++;
}
if(!$bits[$pos]){ break; } // END
}
return $parse;
}
## ##
## Example Data ##
## ##
function sql_select($data,$cols){
// pass names to alias if none
foreach ($cols as $k=>$col){
if(!$col['alias']){
$cols[$k]['alias']=$col['name'];
}
foreach($data as $key=>$row){
$result[$key][$col['alias']]=$data[$key][$col['name']];
}
}
return $result;
}
## ##
## SQLConditions ##
## ##
function sql_conditions($data,$cond){
$cond=trim($cond);
// WHERE column<5 AND (points>7 OR age<3) AND (name>"hello ther")
// replace AND => &&, OR => || !!but not inside "hello or not"...
$conds=explode('\'',$cond);
$repl=array(' AND ',' OR ',' (','( ',' )',') ','=');
$with=array('&&', '||', '(' ,'(', ')', ')' ,'==');
$fix =array('>==','<==','!==');
$it =array('>=', '<=', '!=');
$i=0;
foreach($conds as $k=>$sect){
# select 0,2,4,6...
if($k/2==$i){
$conds[$k]=str_replace($repl,$with,$sect);
$conds[$k]=str_replace($fix ,$it ,$conds[$k]);
$conds[$k]=ereg_replace('([^\(\)=<>!&|0123456789][^\(\)=<>!&|]+)','$dd\\1',$conds[$k]);
$i++;
}
}
$cond=implode('\'',$conds);
foreach($data as $key=>$row){
foreach($row as $col=>$val){
${'dd'.$col}=$val;
}
eval("\$cond2 = \"$cond\";");
eval("\$pass = ".$cond2.";");
if($pass){$out[$key]=$data[$key];}
}
return $out;
}
function sql_cond_test(){
$rel=array('<>','>=','<=','!=','=','<','>');
return $bool;
}
## ##
## SQLMultiSort ##
## ##
function sql_multisort($data,$sort){
foreach($sort as $k=>$by){
$keys[$k]['key']=$by['column'];
if($by['sort']){$keys[$k]['sort']=$by['sort'];}
}
$data=php_multisort($data,$keys);
return $data;
}
## ##
## PHPMultiSort ##
## ##
// Takes:
// $data, multidim array
// $keys, array(array(key=>col1,sort=>desc),array(key=>col2,type=>numeric))
function php_multisort($data,$keys){
// List As Columns
foreach ($data as $key => $row) {
foreach ($keys as $k){
$cols[$k['key']][$key] = $row[$k['key']];
}
}
// List original keys
$idkeys=array_keys($data);
// Sort Expression
$i=0;
foreach ($keys as $k){
if($i>0){$sort.=',';}
$sort.='$cols[\''.$k['key'].'\']';
if($k['sort']){$sort.=',SORT_'.strtoupper($k['sort']);}
if($k['type']){$sort.=',SORT_'.strtoupper($k['type']);}
$i++;
}
$sort.=',$idkeys';
// Sort Funct
$sort='array_multisort('.$sort.');';
echo $sort;
eval($sort);
// Rebuild Full Array
foreach($idkeys as $idkey){
$result[$idkey]=$data[$idkey];
}
return $result;
}
?>Example Page Code:
Code: Select all
<h1>SQL Tests</h1>
<pre>
<? sql('SELECT name AS theirname,age FROM table1'); ?>
</pre>
<pre>
<? sql('SELECT DISTINCT name FROM table1 AS tali'); ?>
</pre>
<pre>
<? sql('SELECT * FROM table1'); ?>
</pre>
<pre>
<? sql('SELECT * FROM table1 WHERE age>21 AND name=\'Dad\''); ?>
</pre>
<pre>
<? sql('SELECT * FROM table1 ORDER BY name'); ?>
</pre>
<pre>
<? sql('SELECT name,age FROM table1 WHERE condition AND condition ORDER BY age,name DESC'); ?>
</pre>