求正则分析sql

[testoktest]

比如

$sql ="
select t.a1
from t
where t.a2 like '%什么什么%'
order by t.a3 asc
GROUP BY t.a4
HAVING t.a5 = '什么什么'
limit 10,5
";

$rule="/(select)(.+)(from)(.+)(where)(.+)(order\s+by)(.+)(group\s+by)(.+)(having)(.+)(limit)(.+)/is";

preg_match_all($rule,$sql,$return);
print_r($return);

没什么问题，但缺少子句的话就不行，比如少了 where/group by 等，
所以，先查找是否有相应子句的关键字，再组合成匹配规则来做？还是可以一条规则就搞定的，或者就单独一个个子句匹配查找？
另外，如果子句内容中出现了关键字咋办？比如 '%什么什么%' 变成了 '%什么where order by limit什么%'，或者子查询的
如果这样，是只匹配第一个，还是用排除单引号 ' 这样，具体咋做哩?
顺便问问，正则想排除几个字符串的这种匹配难不难？

[testoktest]

强大，太强大了，破解中，不过头又变大了……我顶先

[xuzuning]

不可能用简单的正则实现，因为sql语句包含的成分较多且允许嵌套
给你个片断
<?php
include 'lib/TObject.php';

define("PREG_SPLIT_ALL", PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_OFFSET_CAPTURE);

class TTextSQL extends TObject {
var $command = array();
var $comparison = false; //是否为条件表达式状态
var $mode = array('sql');
var $dict = array(
'sql' => array('select', 'insert', 'update', 'alter', 'create', 'delete', 'drop', 'rename'),
'select' => array('all', 'distinct', 'from', 'where', 'group', 'order', 'by', 'limit',),
'insert' => array('into', 'values',),
'update' => array('set', 'where', 'limit',),
'alter' => array('table', 'add', 'limit', 'column', 'drop', 'rename'),
'create' => array('database', 'temp', 'temporary', 'table', 'primary', 'key'),
'delete' => array('from', 'where'),
'drop' => array('database', 'table'),
'rename' => array('table', ),
);

/**
* 主控
**/
function parse($s) {
$this->sqlstr = trim($s);
$this->command = array();
$this->comparison = false;
$this->mode = array('sql');
$this->stack = preg_split("/(\w+|\W)/", trim($s), -1, PREG_SPLIT_ALL);
$k = '';
while($word = $this->get_word()) {
if($t = $this->word_key($word)) {
if($k && !isset($ar[$k])) {
$k .= '_'.$t;
}else {
$k = $t;
}
}else {
$ar[$k][] = $word;
}
}
$method = '';
$old_method = '';

foreach($ar as $k=>$data) {
$method = "_$k";
$old_method or $old_method .= $method;
if(method_exists($this, $old_method.$method)) {
$method1 = $old_method.$method;
$this->$method1($data);
}else {
if(method_exists($this, $method)) {
$this->$method($data);
}else {
trigger_error("找不到 $method 方法", E_USER_ERROR);
}
}
}
$this->command['method'] = join('_', array_reverse($this->mode));
return $this->command;
}

/**
* 读取一个语法成分
**/
function get_word() {
while(list($word, $this->point) = $this->pop()) {
$p = trim($word);
if($word == "'") {
while(list($t,) = $this->pop()) {
$word .= $t;
if($t == "'" && substr($word, -2, 1) != "\\") {
break;
}
}
break;
}
if(!empty($p)) break;
}
return $word;
}

/**
* 检查是否保留字
**/
function word_key($word) {
$word = strtolower($word);
if($this->mode[0] != 'sql' && $word == 'select') {
array_unshift($this->mode, $word);
return $word;
}
if($this->mode[0] == 'sql') {
if(in_array($word, $this->dict[$this->mode[0]])) {
array_unshift($this->mode, $word);
return $word;
}
}elseif(in_array($word, $this->dict[$this->mode[0]])) {
return $word;
}
return false;
}

/**
* 处理表达式
**/
function _expr($ar, $comparison=false) {
}

/**
* 查询时的字段列表
**/
function _result_column($ar) {
}

function _delete_from($ar) {
$this->_from($ar);
}
function _from($ar) {
if(count($ar) == 1) {
$this->command['from'][$ar[0]] = $ar[0];
return;
}
for($i=0; $i<count($ar); $i++) {
if($ar[$i+1] != ',') {
$this->command['from'][$ar[$i+1]] = $ar[$i];
$i += 2;
}else {
$this->command['from'][$ar[$i]] = $ar[$i];
$i++;
}
}
}
function _where($ar) {
$this->command['where'] = $this->_expr($ar, true);
}

function _values($ar) {
$ar = $this->_expr_split(array_slice($ar, 1, -1));
foreach($ar as $v) {
$v = eval("return ".join('', $v).";");
$v = str_replace('\\"', '"', $v);
$this->command['column_value'][] = $v;
}
if(isset($this->command['column_name'])) {
$this->command['field'] = array_combine(
$this->command['column_name'],
$this->command['column_value']
) or trigger_error("insert 参数无效", E_USER_ERROR);
unset($this->command['column_name']);
unset($this->command['column_value']);
}
}
function _column_list($ar) {
if(empty($ar)) return;
foreach($ar as $v) {
if(! in_array($v, array('(', ',', ')'))) {
$this->command['column_name'][] = $v;
}
}
}
function _expr_split($ar) {
$k = 0;
$n = 0;
$ret = array();
foreach($ar as $i=>$v) {
if($v == ',' && !$k) {
$ret[] = array_slice($ar, $n, $i-$n);
$n = $i+1;
continue;
}
if($v == '(') $k++;
if($v == ')') $k--;
}
if($n <= $i) $ret[] = array_slice($ar, $n);
return $ret;
}

function _assignment($ar) {
$tmp = array();
foreach($this->_expr_split($ar) as $v) {
$tmp[] = $this->_expr($v);
}
return $tmp;
}

function _group_by($ar) {
foreach($this->_expr_split($ar) as $v) {
$this->command['group'][] = $v[0];
}
}
function _order_by($ar) {
foreach($this->_expr_split($ar) as $v) {
$mode = 'asc';
$t = array_pop($v);
if(! in_array(strtolower($t), array('asc', 'desc'))) {
array_push($v, $t);
}else {
$mode = strtolower($t);
}
$this->comparison = true;
$expr = $this->_expr($v);
$this->comparison = false;

$this->command['order'][$expr] = $mode;
}
}
function _limit($ar) {
$t = split(',', join('', $ar));
if(count($t) == 1) array_unshift($t, 0);
if($t[0] > 0) $t[0]--;
$this->command['limit'] = $t;
}
function _insert_into($ar) {
$this->command['table'] = array_shift($ar);
foreach($ar as $v) {
if(! in_array($v, array('(', ',', ')'))) {
$this->command['column_name'][] = $v;
}
}
}
function _select($ar) {
$field = array();
foreach($this->_expr_split($ar) as $tmp) {
list($name, $value) = $this->_result_column($tmp);
if($name) $field[$name] = $value;
}
$this->command['field'] = $field;
}
function _update($ar) {
$this->command['table'] = array_shift($ar);
}
function _set($ar) {
$this->command['field'] = $this->_assignment($ar);
}
function _column_def($ar) {
foreach($this->_expr_split($ar) as $ar) {
$field = array();
$field['name'] = array_shift($ar);
$field['type'] = array_shift($ar);
if(($t=array_shift($ar)) == '(') {
$field['len'] = array_shift($ar);
if(($t = array_shift($ar)) == ',') {
$field['dec'] = array_shift($ar);
$t = array_shift($ar);
}
}
while($t) {
if(strtolower($t) == 'auto_increment') {
$field['auto_increment'] = 1;
}
if(strtolower($t) == 'default') {
$field['default'] = eval('return '.array_shift($ar).';');
}
$t = array_shift($ar);
}
$this->command['field'][] = $field;
}
}

[fxs_2008]

学习

[penglewen]

唠叨老大,好久不见。

[bookmoth]

这是传说中的唠叨老大？
拜一拜