CODEWITHSUNDEEP
phpregex
Christian
Christian

Reputation: 21

Regex explode sql query

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 ','...

Upvotes: 2

Views: 853

Answers (2)

dynamic
dynamic

Reputation: 48111

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();

Upvotes: 1

Leri
Leri

Reputation: 12535

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);

Upvotes: 4

Related Questions