解析MySQLi查询语句以获取PDO迁移的参数

Question

This question is about parsing in PHP, not how to migrate MySQLi to PDO.

I want to migrate some old code from MySQLi to PDO. The site has over 400 string query statements. I want to revise the code to use prepared statements.

I.e., convert

$query = 'SELECT * from table where x='.$x.' and y > '.$y.' or (z<= 35 and auth like "%ed")'
$result = $conn->query($query);

to

$query = 'SELECT * from table where x = ? and y > ? or (z <= ? and auth like ?)';
$stmt = $conn->prepare($query);
$stmt->bind_param($x, $y, $35, '%ed');
$stmt->execute();

I started out by parsing the $query string to find 'where', then looking for 'and' to separate parameter/value pairs, etc.

Edit: This is basic code to read lines in a file (in this case, a .php file):

$fn = fopen("myfile.php","r+");
while(!feof($fn))  {
    $line = fgets($fn);
    if (strpos($line, 'select'){
       // add parse code here
    };
}

when finding a line with a query statement, the line will be a literal string something like this:

($line = ) $query = 'select * from menu where authlvl>0 and authlvl<=' . $authlvl . ' order by title';

Then, the code to parse may look something like this:

$where = substr($line, strpos($line, 'where') + 5);
$pair = array();
$j = 0;
$st = 0;
if (strpos(substr($where, $st), 'and', $st)) > 0){
    while (($and = strpos(substr($where, $st), 'and', $st)) > 0){
        $pair[$j] = trim(substr($where, $st, $and - 1));
        $pair[$j+1] = trim(substr($where, $and + 4));
        $st = $and;
        $j++;
    }
}
$ops = array(' ', '<', '>', '=');
(find operators to identify parameters and values for each pair...)

This intuitively is not efficient. Is there a better way to parse the $query string to find the parameters than this? Note that there may be 'order by', etc. in the string that should just append after finding the parameters.

Answer 1

I ended up solving this parsing problem using regex. I am sure that there are much more efficient regex patterns that can be used for matching but this served its purpose.

Reading a line in a PHP file using fgets() yields a string (including the variable name and all syntax), for example:

$query = 'select * from menu where authlvl>0 and authlvl<=' . $authlvl . ' order by title';

The first step is to confirm that the line of code includes a SELECT statement with a WHERE clause and is not a commented statement. The WHERE clause is taken out and split by and/or to get conditional pairs of parameter/value.

Then the parameter/value pairs can be split to allow rewriting the statement according to PDO.

if (preg_match('/(=\s*\'select|=\s*"select)/', $line) && preg_match('/(where)/i', $qry) && !preg_match('/^(\s*\/\/)/', $line)) {
    $z = preg_match('/(?<=where).+/i', $qry, $matches);
    $where = trim($matches[0]);
    $pairs = preg_split('/( and | or )/', $where);
    $andor = preg_match_all('/(\s*and\s*|\s*or\s*)+/', $where, $ao);

The conditional pairs are split by finding comparison operators in each pair.

preg_match('/.+?(?:[\=<>]+)/', $pair, $par)
preg_match('/[^(' . preg_quote($par[0]) . ')].*/', $pair, $val)

The values can then be replaced by question marks and the query statement rewritten. The values can also be placed in an array for PDO.

$query = 'select * from menu where authlvl > ? and authlvl <= ? order by title';
$values = array('0', $authlvl);
$stmt = $this->pdosc->prepare($query);
$stmt->execute($values);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

I was able to use regex to capture nearly any variation of SELECT statements that I have and quickly update (migrate) my files from MySQLi to PDO. Of course, I have a wrapper function that takes either a simple string for the query statement (MySQLi) or a 2 part array with the query and values (PDO).