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

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.

dty9731
dty9731 susbstr/strpos完全不足以解决这类问题。如果有的话,使用一些正则表达式检测和提取。还提出了任何包装函数,而不是在准备/绑定/执行代码块中涂抹参数绑定。
11 个月之前 回复
donglanying3855
donglanying3855 我添加了更多细节以帮助理解具体问题。
11 个月之前 回复
ds355020
ds355020 听起来像一个具有挑战性的项目但让我们现实地看一下。能够编写解析器以在更少的时间内正确地解析每个语句,以便手动编辑它们。400可能听起来很多。IT会比人们想象的要快。以这种方式看待它。如果这是一个系统,您需要长期工作。您将密切了解系统并培训您的大脑以快速解析SQL。
11 个月之前 回复
donglie7268
donglie7268 你是模糊的。你最后一段代码中$query中究竟出现了什么?是PHP源代码吗?它是什么样子的?
11 个月之前 回复
dongshipang8094
dongshipang8094 当然。我正在编写解析代码来查找所有查询语句并转换它们。编写修改文件的代码没有问题。我正在寻找一种通过查找关键组件(如运算符)来解析代码的基本方法。
11 个月之前 回复

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐