Regex explode sql query

Question

i have spent searching and finding a solution for my problem the entire day and couldn't find anything for my Problem. I have a query which needs to be "exploded" via regular expression. I want to get the selected fields, the table and the fields after the where condition (without the check itself).

SELECT `a`, `b`, `c` FROM `d` WHERE `e` > 1 OR `d` > 1

My attempt looks like the following:

/SELECT (?<selectedFields>(,*?)\`(.*?)\`) FROM (?<tableName>\`(.*?)\`) WHERE (?<checkFields>\`(.*?)\`)/

The Problem I have is that the regular expression stops after the first field at the where condition. The Output i got looks like the following.

Array
(
[0] => Array
    (
        [0] => SELECT `a`, `b`, `c` FROM `d` WHERE `e`
    )

[selectedFields] => Array
    (
        [0] => `a`, `b`, `c`
    )

[1] => Array
    (
        [0] => `a`, `b`, `c`
    )

[2] => Array
    (
        [0] => 
    )

[3] => Array
    (
        [0] => a`, `b`, `c
    )

[tableName] => Array
    (
        [0] => `d`
    )

[4] => Array
    (
        [0] => `d`
    )

[5] => Array
    (
        [0] => d
    )

[checkFields] => Array
    (
        [0] => `e`
    )

[6] => Array
    (
        [0] => `e`
    )

[7] => Array
    (
        [0] => e
    )

)

I need the "checkFields" in the same kind of array i got the selected fields. What am i doing wrong? Second thing is that the selectFields from the regular expression are not as expected an array with each fieldname, it is separated by ','...

You can use this project: http://code.google.com/p/php-sql-parser/ — m4t1t0, Dec 27 '12 at 14:32

score 4 · Answer 1 · edited May 23 '17 at 10:24

4

You should not parse sql query with regex, because parsing sql query with regex is very similar of parsing html with regex. And this answer clearly says why you should not do it.

You'd better use some parser (e.g. this one).

Little sample:

$sql = 'SELECT `a`, `b`, `c` FROM `d` WHERE `e` > 1 OR `d` > 1';
$sqlParser = new PHPSQLParser($sql);
echo '<pre>';
print_r($sqlParser->parsed);

edited May 23 '17 at 10:24

Community

1
1

answered Dec 27 '12 at 14:34

Leri

12,367
7
43
60

Humm... I do not want to use a sql parser because all of my queries i have to explode are made without any join, alias or whatever. I have to repair some broken data fields and because i do not want to copy mysql_query, mysql_fetch_assoc a hundred times i just thought that it might be possible get around with a regular expression. – Christian Dec 28 '12 at 09:18
@Christian, It's possible to use regular exception for given query but exactly the same query maybe little bit different as a string and regular exception will fail. – Leri Dec 28 '12 at 09:30
the problem is that the regular expression won't gather all elements. It stops after the first element of the where part. Instead of returing e and d at "checkFields", the result is only e... $sRegPattern = '/SELECT(?(.*?))FROM(?(.*?))WHERE(?(.*))/s'; $sSpecificResult = '/\`(.*?)\`/'; For getting all elements i have to run 2 regular expression (above). Is it possible to combine them? – Christian Dec 28 '12 at 12:41

dynamic · Answer 2 · 2012-12-27T14:37:37.313

With REGEX you won't go too far. Think about all possible SQL queries:

SELECT * FROM tbl1 JOIN tbl2 
SELECT field as field2 FROM table1 as alias
etc

You need a parser for this. Try: http://code.google.com/p/php-sql-parser/

Also I would even recommend you to don't parse SQL at all, SQL parser builtin in a database are very very complex and mimic their behaviors isn't easy.

If you need such behaviour you can create a simple Class that does the inverse (builds up your SQL), something like:

$s = new SQL();
$s->setFrom('table');
$s->addCondition('condition1','value1');
$s->select('*');  //> outputs SELECT * FROM table WHERE condition1 = 'value1'

So you can have a method to retrieve all parts:

$s->getConditions();
$s->getSelect();

Regex explode sql query

2 Answers2