Basic SQL Interpreter

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

sebble_dot
Forum Newbie
Posts: 5
Joined: Sun May 11, 2008 7:05 am

Basic SQL Interpreter

Post 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>
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Basic SQL Interpreter

Post 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)
sebble_dot
Forum Newbie
Posts: 5
Joined: Sun May 11, 2008 7:05 am

Re: Basic SQL Interpreter

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Basic SQL Interpreter

Post 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.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Basic SQL Interpreter

Post 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".
sebble_dot
Forum Newbie
Posts: 5
Joined: Sun May 11, 2008 7:05 am

Re: Basic SQL Interpreter

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Basic SQL Interpreter

Post by Eran »

There is database-less database solution for PHP, it's called SQLite http://www.sqlite.org/.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Basic SQL Interpreter

Post 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)
sebble_dot
Forum Newbie
Posts: 5
Joined: Sun May 11, 2008 7:05 am

Re: Basic SQL Interpreter

Post by sebble_dot »

@pytrin: Thanks, I'll have a look at SQLite in a bit more detail. Seb
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Basic SQL Interpreter

Post 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.
sebble_dot
Forum Newbie
Posts: 5
Joined: Sun May 11, 2008 7:05 am

Re: Basic SQL Interpreter

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Basic SQL Interpreter

Post 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.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Basic SQL Interpreter

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Basic SQL Interpreter

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Basic SQL Interpreter

Post by pickle »

Sounds like you've written something like Linq (Google search for PHP Linq)
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply