<?php
class LwgClass{
function catcherr($msg){
$this->err=$msg;
if ($this->debug==true)echo $msg;
return false;
}
function error(){
echo $this->err;
}
}
?>

<?
class sqlparse extends LwgClass{
var $sql;
var $keyVal=array();//初步分析后关键词对应的字符
var $instead_quo=array();//将sql中有单引号的部分提取出来
var $quoStr;//将sql中有单引号的部分提取出来之后用来替换的字串
var $instead_bra=array();//将sql中有括号的部分提取出来
var $braStr;//将sql中有括号的部分提取出来之后用来替换的字串
var $result=array();//最终分析后关键词对应的字符

//构造函数
function sqlparse(){
$this->quoStr=uniqid(rand());//替换单引号的字符
$this->braStr=uniqid(rand());//替换括号的字符
}

function parse($sql=''){
if (!empty($sql))$this->sql=$sql;
if (empty($this->sql))return $this->catcherr("错误:SQL语句为空!");

$sql=$this->quotesStr($sql);//将有单引号引用的字串提出来放在数组$this->instead_quo中
$sql=strtolower($sql);

//取得第一、二个单词，判断sql的类型
if (!preg_match("/^(.+) +(.+) +/U",$sql,$regs))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 无效的SQL语句。");
if ($regs[1]=="select" || $regs[1]=="insert" || $regs[1]=="update" || $regs[1]=="delete" || $regs[1]=="alter")$this->result['dowhat']=$regs[1];
else if ($regs[1]=="create"){
if ($regs[2]=="database")$this->result['dowhat']="createdb";
else if ($regs[2]=="table")$this->result['dowhat']="createtbl";
else return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在'".$regs[2]."'处。");
}
else if ($regs[1]=="drop"){
if ($regs[2]=="database")$this->result['dowhat']="dropdb";
else if ($regs[2]=="table")$this->result['dowhat']="droptbl";
else return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在'".$regs[2]."'处。");
}
else return $this->catcherr("SQL语句错误:\"".$this->sql."\", '{$regs[1]}'是无效的的操作方式。");
$rep_src[]="/,/";
$rep_dst[]=" , ";

$rep_src[]="/\(/";
$rep_dst[]=" (";

$rep_src[]="/\)/";
$rep_dst[]=") ";

$rep_src[]="/`|;|into +|by +|column +|database +|table +|".$regs[1]." /i";
$rep_dst[]="";

$rep_src[]="/ *(\\n|\\t|\\r) */";
$rep_dst[]=" ";

$rep_src[]="/ {2,}/";
$rep_dst[]=" ";

$sql=trim(preg_replace($rep_src,$rep_dst,$sql));//除去不需要的字串，并格式化一些字串

$sql=$this->bracketStr($sql);//替换有括号的字串

//下面最为重点, 以关键字为键名，将后面的字串作为它的值
$keywords=array('from','where','order','limit','set','add','rename','values','drop','change');
$tmp_key=$this->result['dowhat'];
$this->keyVal[$tmp_key]="";
$sql=explode(' ',$sql);
foreach ($sql as $sql_1){
//if (empty($sql_1))continue;
if (in_array($sql_1,$keywords)){
if ($sql_1=="drop" && $tmp_key=="drop")continue;
else if ($sql_1=="add" && $tmp_key=="add")continue;
else $tmp_key=$sql_1;
$this->keyVal[$tmp_key]="";
}
else {
if (ereg("<|".$this->braStr,$sql_1)){
if ($tmp_key=="createtbl" || $tmp_key=="insert" && $this->keyVal[$tmp_key]!="") $tmp_key="fieldname";
}
$this->keyVal[$tmp_key].=($this->keyVal[$tmp_key]!="")?" ".$sql_1sql_1;
}
}

switch ($this->result['dowhat']){
case "select":
if (false == $this->sql_select())return false;
break;
case "insert":
if (false == $this->sql_insert())return false;
break;
case "update":
if (false == $this->sql_update())return false;
break;
case "delete":
if (false == $this->sql_delete())return false;
break;
case "createdb":
if (empty($this->keyVal['createdb']))return $this->catcherr("SQL语句错误:\"".$this->sql."\",没有指定数据库名。");
if (!preg_match("/^\w+$/",$this->keyVal['createdb']))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$this->keyVal['createdb']."\"处,不合法的数据库名。");
$this->result['dbname']=$this->keyVal['createdb'];
break;
case "dropdb":
if (empty($this->keyVal['dropdb']))return $this->catcherr("SQL语句错误:\"".$this->sql."\",没有指定数据库名。");
if (!preg_match("/^\w+$/",$this->keyVal['dropdb']))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$this->keyVal['dropdb']."\"处,不合法的数据库名。");
$this->result['dbname']=$this->keyVal['dropdb'];
break;
case "createtbl":
if (false == $this->tbl_checkerr($this->keyVal['createtbl']))return false;
if (false==$this->sql_createtbl($this->keyVal['fieldname']))return false;
break;
case "droptbl":
if (false==$this->tbl_checkerr($this->keyVal['droptbl']))return false;
break;
case "alter":
if (false==$this->sql_alter())return false;
break;
}
return $this->result;
}

function quotesStr($sql=''){
if(empty($sql))return;
if (!is_int(strpos($sql,"'")))return $sql;
if (is_int(strpos($sql,"\'"))){
$has=true;
$sql=str_replace("\'",$this->quoStr,$sql);
}

$returnstr='';
$sql=explode("'",$sql);
$i=0;
$n=0;
foreach ($sql as $str){
$i++;
if ($i%2==1)$returnstr.=$str;
else{
$returnstr.="<|".$this->quoStr.$n."|>";
$this->instead_quo["<|".$this->quoStr.$n."|>"]=(isset($has))?str_replace($this->quoStr,"\'",$str)str;
$n++;
}
}
return $returnstr;
}

function bracketStr($str=''){
if(empty($str))return;
$n=strlen($str);
$q=0;//括号的层次
$val=array();//记录每组括号的键与值
$valstr="";//记录当前括号及内括号内的字串
$returnstr=""; //把括号用替换字串替换后的新字串
$num=0;//记录括号的组数
for ($i=0;$i<$n;$i++){
$tmpstr=$str[$i];
if ($tmpstr=="("){
if ($q==0){
$tmp="<|".$this->braStr.$num."|>";
$returnstr.=$tmp;
$num++;
$valstr="";
}
$q++;
}
else if ($q==0)$returnstr.=$tmpstr;
$valstr.=$tmpstr;
if ($tmpstr==")"){
$q--;
if ($q==0)$val[$tmp]=$valstr;
}
}
$this->instead_bra=$val;
return $returnstr;
}

function sql_select(){
if (empty($this->keyVal['select']))return $this->catcherr("SQL语句错误:\"".$this->sql."\",缺少select子句。");
if (preg_match("/$this->quoStr/",$this->keyVal['select']))return $this->catcherr("SQL语句错误:\"".$this->sql."\",select子句不支持函数。");
if ($this->keyVal['select']=="*")$this->result['select']="*";
else {
if (!preg_match("/^(distinct *)?[a-z_]+\w{1,15}( *, *(distinct *)?[a-z_]+\w{1,15})*$/i",$this->keyVal['select']))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$this->keyVal['select']."\"处,select语句无效。");
else {
$sels=explode(' , ',$this->keyVal['select']);
foreach ($sels as $sel){
if (ereg('^distinct',$sel)){
if (isset($this->result['select']['distinct']))return $this->catcherr("SQL语句错误:\"".$this->sql."\",只能有一个distinct字段名。");
$this->result['select']['distinct']=str_replace("distinct ","",$sel);
}
else $this->result['select'][]=$sel;
}
}
}

if (false==$this->tbl_checkerr($this->keyVal['from']))return false;

if (isset($this->keyVal['where'])){
if (false==$this->sql_where($this->keyVal['where']))return false;
}

if (isset($this->keyVal['order'])){
if (empty($this->keyVal['order']))return $this->catcherr("SQL语句错误:\"".$this->sql."\",order子句为空。");
if (!preg_match("/^[a-z_]+\w{1,15} *(asc|desc)? *(, *[a-z_]+\w{1,15} *(asc|desc)?)?$/",$this->keyVal['order']))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$this->keyVal['order']."\"处,order by 语句无效。");
$ords=explode(' , ',$this->keyVal['order']);
foreach ($ords as $ord){
$ord=explode(' ',$ord);
$this->result['orderby'][$ord[0]]=(empty($ord[1]))?"asc"ord[1];
}
}

if (isset($this->keyVal['limit'])){
if (empty($this->keyVal['limit']))return $this->catcherr("SQL语句错误:\"".$this->sql."\",limit子句为空。");
if (!preg_match("/^[0-9]+( *, *[0-9]+)?$/",$this->keyVal['limit']))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$this->keyVal['limit']."\"处,无效的limit参数。");
$lims=explode(' , ',$this->keyVal['limit']);
if (empty($lims[1])){
$this->result['limit'][0]=0;
$this->result['limit'][1]=$lims[0];
}
else {
$this->result['limit'][0]=$lims[0];
$this->result['limit'][1]=$lims[1];
}
}
return true;
}

function sql_insert(){
if (false==$this->tbl_checkerr($this->keyVal['insert']))return false;
if (isset($this->keyVal['fieldname'])){
$value=trim(substr($this->instead_bra[$this->keyVal['fieldname']],1,-1));
if (!preg_match("/^[a-z_]+\w{1,15}( *(auto_increment))?( *, *[a-z_]+\w{1,15}( *(auto_increment))?)*$/",$value))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$value."\"处,无效字段名与属性。");
$value_1=explode(',',$value);
$i=0;
foreach ($value_1 as $val){
$this->result['fieldname'][$i]=trim($val);
$i++;
}
}

if (empty($this->keyVal['values']))return $this->catcherr("SQL语句错误:\"".$this->sql."\",没有要插入的值。");
$value=trim(substr($this->instead_bra[$this->keyVal['values']],1,-1));
if (!preg_match("/^[^,]*( *, *[^,]*)*$/",$value))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$value."\"处,无效值。");
$value_1=explode(',',$value);
$i=0;
foreach ($value_1 as $val){
$val=trim($val);
if (isset($this->instead_quo[$val])) $val=$this->instead_quo[$val];
$this->result['values'][$i]=stripslashes($val);
$i++;
}

if ($this->result['fieldname']!="" && count($this->result['fieldname'])!=count($this->result['values']))
return $this->catcherr("SQL语句错误:\"".$this->sql."\", 字段名与值没有一一对应。");
return true;
}

function sql_update(){
print_r($result);
if (false==$this->tbl_checkerr($this->keyVal['update']))return false;
if (empty($this->keyVal['set']))return $this->catcherr("SQL语句错误:\"".$this->sql."\",缺少set子句。");
if (false == $this->sql_set($this->keyVal['set']))return false;
if (isset($this->keyVal['where'])){
if (false == $this->sql_where($this->keyVal['where']))return false;
}
return true;
}

function sql_delete(){
if (false==$this->tbl_checkerr($this->keyVal['from']))return false;
if (isset($this->keyVal['where'])){
if (false == $this->sql_where($this->keyVal['where']))return false;
}
return true;
}

function sql_alter(){
if (false==$this->tbl_checkerr($this->keyVal['alter']))return false;

if (isset($this->keyVal['rename'])){
if (empty($this->keyVal['rename']))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 没有指定更新后的表名。");
if (!preg_match("/^\w+$/",$this->keyVal['rename']))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$this->keyVal['rename']."\"处,不是合法的数据表名。");
$this->result['alter']['kind']='rename';
$this->result['alter']['value']=$this->keyVal['rename'];
}

if (isset($this->keyVal['change'])){
if (empty($this->keyVal['change']))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 没有指定字段名。");
if (preg_match("/auto_increment/",$this->keyVal['change']))return $this->catcherr("错误:已存在auto_increment字段。");
foreach ($this->instead_bra as $key => $val)$this->keyVal['change']=str_replace($key,$val,$this->keyVal['change']);
if (!preg_match("/^([a-z_]+\w{1,15}) +([a-z_]+\w{1,15})( +(int|varchar|text) *(\( *([1-9]+[0-9]*) *\))?)?$/",$this->keyVal['change'],$change_regs))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$this->keyVal['change']."\"处,change子句无效,可能是缺少字段名或不合法的字段名与属性。");
if ($change_regs[4]=="text"){
if (!empty($change_regs[6]))return $this->catcherr("错误:text类型的字段不能指定长度。");
}
else if (empty($change_regs[6]))return $this->catcherr("错误:没有指定字段的长度。");
$this->result['alter']['kind']='change';
$this->result['alter']['value']['oldfield']=$change_regs[1];
$this->result['alter']['value']['newfield'][0]=$change_regs[2];
$this->result['alter']['value']['newfield'][1]=$change_regs[4];
$this->result['alter']['value']['newfield'][2]=$change_regs[6];
}

if (isset($this->keyVal['drop']))if (false==$this->dropcol($this->keyVal['drop']))return false;

if (isset($this->keyVal['add'])){
if (count($this->instead_bra)>0){
foreach ($this->instead_bra as $key => $val)$this->keyVal['add']=str_replace($key,$val,$this->keyVal['add']);
}
if (false==$this->parsefield($this->keyVal['add']))return false;
if (!empty($this->result['autofield']))return $this->catcherr("错误:已存在auto_increment字段。");
$this->result['alter']['kind']='add';
$this->result['alter']['value']=$this->result['fieldname'];
unset($this->result['fieldname']);
}

if ($this->result['dowhat']=="alter" && $this->result['alter']['kind']!='rename' && $this->result['alter']['kind']!='add' && $this->result['alter']['kind']!='dropcol' && $this->result['alter']['kind']!='change')return $this->catcherr("SQL语句错误:\"".$this->sql."\", 没有指明方式。");
return true;
}

function sql_createtbl($fieldname){
$fieldname=trim(substr($this->instead_bra[$fieldname],1,-1));
if (false==$this->parsefield($fieldname))return false;
return true;
}

function parsefield($fieldname){
if (!preg_match("/^[a-z_]+\w{1,15} +((int|varchar) *\( *[1-9]+[0-9]* *\)( *auto_increment)?|text)( *, *[a-z_]+\w{1,15} +((int|varchar) *\( *[1-9]+[0-9]* *\)( *auto_increment)?|text))*$/",$fieldname))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$fieldname."\"处,无效字段名与属性。");
$tmp=array();
$autofield='';
$fieldname=explode(',',$fieldname);
$i=0;
foreach ($fieldname as $field){
$field=trim($field);
if (empty($field))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$fieldname."\"处,无效字段名与属性。");
unset($regs);
if (!preg_match("/^(.+) +(.+) *(\((.+) *\) *(auto_increment)*)?$/iU",$field,$regs))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$field."\"处,无效字段名与属性2。");;
$tmp[$i][0]=$regs[1];
$tmp[$i][1]=$regs[2];
if (!empty($regs[4]))$tmp[$i][2]=$regs[4];
if (!empty($regs[5])){
if (empty($autofield))$autofield=$regs[1];
else return $this->catcherr("SQL语句错误:\"".$this->sql."\",只能指定一个auto_increment字段。");
}
$i++;
}
$this->result['fieldname']=$tmp;
if (!empty($autofield))$this->result['autofield']=$autofield;
return true;
}

function tbl_checkerr($tblname=''){
if ($tblname=="")return $this->catcherr("SQL语句错误:\"".$this->sql."\", 未指明数据表。");
if (!preg_match("/^\w+$/",$tblname))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$tblname."\"处,不是合法的数据表名。");
$this->result['tblname']=$tblname;
return true;
}

function dropcol($value){
if (empty($value))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 没有指定字段名。");
if (!preg_match("/^[a-z_]+\w{1,15}( *, *[a-z_]+\w{1,15})*$/",$value))return $this->catcherr("SQL语句错误:\"".$this->sql."\", 在\"".$value."\"处,无效字段名。");
$this->result['alter']['kind']='dropcol';
$vals=explode(",",$value);
$i=0;
foreach ($vals as $val){
$this->result['alter']['value'][$i]=trim($val);
$i++;
}
return true;
}

function sql_set($value){
if (preg_match("/".$this->braStr."/",$value))return $this->catcherr("SQL语句错误:\"".$this->sql."\",set子句不支持函数或表达式。");
if (!preg_match("/^[a-z_]+\w{1,15} *= *([a-z_]+\w{1,15} *(\+|\-|\*|\/|\%) *)?[^, ]+( *, *[a-z_]+\w{1,15} *= *([a-z_]+\w{1,15} *(\+|\-|\*|\/|\%) *)?[^, ]+)*$/",$value))return $this->catcherr("SQL语句错误:\"".$this->sql."\", set子句无效。");
$tmp_value=explode(",",str_replace(" ","",$value));
foreach ($tmp_value as $val){
$val=explode("=",$val);
$this->result['set'][$val[0]]=stripslashes((isset($this->instead_quo[$val[1]]))?$this->instead_quo[$val[1]]val[1]);
}
return true;
}

function sql_where($where){
if (preg_match("/".$this->braStr."/",$where))return $this->catcherr("SQL语句错误:\"".$this->sql."\",where子句不支持函数或表达式。");
if (!preg_match("/^[a-z_]+\w{1,15} *(<>|>=|<=|=|>|<| +like +) *([^ ]+)( +and +[a-z_]+\w{1,15} *(<>|>=|<=|=|>|<| +like +) *([^ ]+))*$/sU",$where))return $this->catcherr("SQL语句错误:\"".$this->sql."\", where子句无效。");

$sql=explode(" and ",trim($where));
$i=0;
foreach ($sql as $sql_1){
preg_match("/^(.*) *(<>|>=|<=|=|>|<|like) *(.*)$/U",$sql_1,$regs);
$this->result['where'][$i][0]=trim($regs[1]);
$this->result['where'][$i][1]=trim($regs[2]);
$regs[3]=trim($regs[3]);
$this->result['where'][$i][2]=(isset($this->instead_quo[$regs[3]]))?$this->instead_quo[$regs[3]]regs[3];
$i++;
}
return true;
}
}
?>