Page 1 of 2

Basic SQL Interpreter

Posted: Sun May 11, 2008 7:12 am
by sebble_dot
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:

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>

Re: Basic SQL Interpreter

Posted: Fri May 16, 2008 12:55 pm
by Mordred
I'm sorry, but I fail to see the purpose. Can you describe some usage scenarios? (No comments on the security, I haven't even scanned the code yet)

Re: Basic SQL Interpreter

Posted: Sat May 17, 2008 5:11 am
by sebble_dot
The idea, to allow SQL manipulation of php arrays.
If you believe this has no true purpose, are there better alternatives, or should array manipulation just be done with several functions specifically each time.

Re: Basic SQL Interpreter

Posted: Sat May 17, 2008 5:32 am
by onion2k
I can imagine it'd be useful if you store large quantities of data in a PHP array ... but to be honest I never do that.

Re: Basic SQL Interpreter

Posted: Sat May 17, 2008 9:18 am
by Mordred
You are reinventing, if not the wheel, then at least the database. There are already enough database solutions, the world doesn't need a PHP-based one. And don't explain your rationale for developing an SQL-over-array database with "to allow SQL manipulation of php arrays" - okay, I will believe you what the code does. What I asked was "why".

Re: Basic SQL Interpreter

Posted: Sat May 17, 2008 10:12 am
by sebble_dot
I appologise for not answering your question.

Why, would be to extend the use of SQL queries to PHP arrays, this is of use to myself in that I use several means of data storage, for systems without access to proper databases, I was not intending to reinvent a data storage medium, just possibly enhance the manipulation of it. The contained PHP arrays are for example only, and could be the result of any number of data storage systems.

Currently I am making great use of CSV based storage, and occasionally allowing a user to build their data storage by adding a text based file (or even 'drag and drop'-ing pictures into album folders with a simple metadata plaintext file) to a simple directory structure has been useful.

My hope was that this interpretation of manipulation strings could simplify the process further.

The reason for posting this code was to know what people thought of the idea (not necessarily new) or the functionality, and I appreciate your feedback. Would you suggest all users without true database systems available should transfer to hosts providing such to enable CMS systems, blogs, wikis, albums? (!! I am aware that this functionality is already available, the interpreter would merely be to simplify manipulation).

Thanks, Seb.

Re: Basic SQL Interpreter

Posted: Sat May 17, 2008 12:18 pm
by Eran
There is database-less database solution for PHP, it's called SQLite http://www.sqlite.org/.

Re: Basic SQL Interpreter

Posted: Sat May 17, 2008 1:59 pm
by Mordred
Databases solve, often invisibly to the unexperienced user (which is not to say that you specifically are unexperienced) many problems which I doubt you have had the chance to address (but I still haven't checked your code, so you might have). File-based solutions work well on the development machine, and fail in unpredictable ways in the production environment. SQL queries in modern databases are well optimized before executing, so it's harder for the user to shoot himself in the foot. PHP itself is not well suited for handling huge datasets, while a natively compiled code will work much better on natively represented data.

Also, there are no systems without a database (any such you can point out is just statistical error ;) )

So, in short, I think it's a great intellectual exercise, but it has little practical value even on paper (and it will get no better when implemented in code).

@pytrin: SQLite is a regular database, just has fewer features and tighter storage. So is sebble_dot's project. A database is just organised storage (with an optional SQL interface, if you so insist)

Re: Basic SQL Interpreter

Posted: Sat May 17, 2008 2:35 pm
by sebble_dot
@pytrin: Thanks, I'll have a look at SQLite in a bit more detail. Seb

Re: Basic SQL Interpreter

Posted: Sat May 17, 2008 3:08 pm
by Eran
@Mordred:
SQLite is not like a regular database, in the sense that it doesn't have a separate server process - meaning it does not require an SQL database engine to be running as service on the server for it to work. I think this is what sebble was looking for.

Re: Basic SQL Interpreter

Posted: Sat May 17, 2008 3:55 pm
by sebble_dot
@pytrin: I've been having a look at SQLite and it seems to be just what I was looking for. It even suggests that
it is often easier and quicker to load the data into an in-memory SQLite database and use queries with joins and ORDER BY clauses to extract the data in the form and order needed rather than to try to code the same operations manually
, as I was trying to achieve with my SQL interpreter.

This solution would work very well for me, better than my PHP code, unfortuantely it is not installed on my shared server nor do I have the means to install/build it.

Thanks anyway, Seb

Re: Basic SQL Interpreter

Posted: Sat May 17, 2008 4:33 pm
by Eran
Can't you send a request to your server's NOC asking them to activate the php_sqlite module? We ask those kind of things all the time (hosted at servInt) and they usually follow through.

Re: Basic SQL Interpreter

Posted: Sat May 17, 2008 10:19 pm
by Mordred
Ah, so you both meant a database server.
I still can't think of any particular benefit for web development to use a built-in database versus a database server. Local sockets are fast, there is support for in-memory tables... No really, can you give one example where SQLite or sebble_dot's project will be better?

Re: Basic SQL Interpreter

Posted: Sun May 18, 2008 7:21 pm
by Eran
Don't get me wrong, I believe a database server such as MySQL is a superior solution and would always choose that given the choice. He asked about a specific database implementation (re: SQL parser) so I thought SQLite might be a good alternative.

Re: Basic SQL Interpreter

Posted: Thu Mar 11, 2010 10:02 am
by pickle
Sounds like you've written something like Linq (Google search for PHP Linq)