在分号上拆分 php 中的 sql 语句(但不在引号内) [英] split sql statements in php on semicolons (but not inside quotes)
问题描述
当用户在自由格式字段中使用分号时，我有一个系统会导致错误.我已经将其追溯到一个简单的爆炸语句:
I have a system that is causing errors when users use a semicolon in a free format field. I have traced it down to a simple explode statement:
$array = explode( ";", $sql );
因为这一行在一个从整个系统调用的子程序中，我想用可以正确拆分事物的东西替换这一行，而不会破坏系统的其余部分.我以为我是 str_getcsv 的赢家，但这也不够复杂.看下面的例子
Because this line is in a subroutine that is called from all over the system I would like to replace this line with something that will split things properly, without breaking the rest of the system. I thought I was onto a winner with str_getcsv, but that isn't sophisticated enough either. Look at the following example
$sql = "BEGIN;INSERT INTO TABLE_A (a, b, c) VALUES('42', '12', '\'ab\'c; DEF');INSERT INTO TABLE_B (d, e, f) VALUES('42', '43', 'XY\'s Z ;uvw') ON DUPLICATE KEY UPDATE f='XY\'s Z ;uvw';COMMIT;";
$array = str_getcsv($sql, ";", "'");
foreach( $array as $value ) {
echo $value . "<br><br>";
}
当我运行时，输出如下:
When I run this is outputs the following:
开始
INSERT INTO TABLE_A (a, b, c) VALUES('42', '12', '\'ab\'c
INSERT INTO TABLE_A (a, b, c) VALUES('42', '12', '\'ab\'c
DEF')
INSERT INTO TABLE_B (d, e, f) VALUES('42', '43', 'XY\'s Z
INSERT INTO TABLE_B (d, e, f) VALUES('42', '43', 'XY\'s Z
uvw') 重复密钥更新 f='XY\'s Z
uvw') ON DUPLICATE KEY UPDATE f='XY\'s Z
紫外线'
提交
所以它没有注意到分号在引号内.(据我所知，来自系统中不同地方的引用字符串总是用单引号引起来的，但有时它们可能是双引号，我不确定.)
So it doesn't notice the semicolons are inside quotes. (As far as I can see the quoted strings from different places in the system are always in single quotes, but it is possible that at times they are double quotes, I am not sure about that.)
谁能告诉我怎么做?我怀疑我可以用一个非常复杂的正则表达式来做到这一点，但这超出了我的能力.
Can anyone tell me how to do this? I suspect I can do this with a very complicated regex, but this is over my head.
推荐答案
(*SKIP)(*FAIL) Magic
这个现场PHP演示向您展示了以下两个选项的输出(带或不带分号).
This live PHP demo shows you the output of the two options below (with or without the semi-colon).
这就是你需要的:
$splits = preg_split('~\([^)]*\)(*SKIP)(*F)|;~', $sql);
查看演示，了解我们在正确的分号上拆分.
See demo to see that we are splitting on the right semi-colons.
输出:
[0] => BEGIN
[1] => INSERT INTO TABLE_A (a, b, c) VALUES('42', '12', '\'ab\'c; DEF')
[2] => INSERT INTO TABLE_B (d, e, f) VALUES('42', '43', 'XY\'s Z ;uvw')
[3] => COMMIT
[4] =>
空项目#4 是最后一个; 另一侧的匹配项.另一种选择是保留分号(见下文).
The empty item #4 is the match on the other side of the final ;. The other option is to keep the semi-colons (see below).
选项 2:保留分号
如果您想保留分号，请使用:
If you want to keep the semi-colons, go with this:
$splits = preg_split('~\([^)]*\)(*SKIP)(*F)|(?<=;)(?![ ]*$)~', $sql);
输出:
[0] => BEGIN;
[1] => INSERT INTO TABLE_A (a, b, c) VALUES('42', '12', '\'ab\'c; DEF');
[2] => INSERT INTO TABLE_B (d, e, f) VALUES('42', '43', 'XY\'s Z ;uvw');
[3] => COMMIT;
说明
这个问题是这个问题中解释的技术的经典案例正则表达式匹配模式，不包括..."
This problem is a classic case of the technique explained in this question to "regex-match a pattern, excluding..."
在交替|的左边，正则表达式\([^)]*\)匹配完整的(括号)然后故意失败，之后引擎跳到字符串中的下一个位置.右侧匹配您想要的 ; 单词，我们知道它们是正确的，因为它们与左侧的表达式不匹配.现在可以安全地拆分它了.
In left side of the alternation |, the regex \([^)]*\) matches complete (parentheses) then deliberately fails, after which the engine skips to the next position in the string. The right side matches the ; word you want, and we know they are the right ones because they were not matched by the expression on the left. It is now safe to split on it.
在我们保留分号的选项 2 中，我们右侧的匹配匹配一个位置，但不匹配字符.该位置由后视 (?<=;) 断言，它断言 ; 紧跟在该位置之前，而否定前瞻 (?![]*$)，它断言后面的不是可选的空格然后是字符串的结尾(所以我们避免最后一个空匹配).
In Option 2, where we keep the semi-colons, our match on the right matches a position, but no characters. That position is asserted by the lookbehind (?<=;), which asserts that a ; immediately precedes the position, and the negative lookahead (?![ ]*$), which asserts that what follows is not optional spaces then the end of the string (so we avoid a last empty match).
示例代码
请查看现场 PHP 演示.
参考
这篇关于在分号上拆分 php 中的 sql 语句(但不在引号内)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
