Php pcre正则表达式用分隔符/注释解析SQL [英] Php pcre regex to parse SQL with delimiters/comments
问题描述
我正在尝试使用正则表达式从包含多个sql语句和备用分隔符/注释的文件中提取单个sql语句。
i我试图匹配以下内容用于隔离sql语句的模式,然后在隔离单个语句之后,剥离它的注释:
delimiter(del)(非空白序列)(不是(del)或用(del)注释)(del)
(不是;);
第一个模式应允许使用任何字符集作为分隔符
我尝试了什么:
i尝试以下方式来匹配第一种模式:
/ \ * * delimiter \s +(?< d> [^ \ s] +)\ s *;?\ * *(?< qstr>(( (?! - | \ g {d})。)* | - [^ \ R] * \ R)+)\ g {d} \ * *;?/ s
如果第一种模式失败,则匹配第二种模式:
/ \ * *(?< qstr>((? ! - |;)。)+ | - [^ \ R] * \ R)*); / s
匹配第二个案例
然后如果任何一个成功,用空字符串替换以下内容:
/ - [^ \ n [\\]] *(?:\ n | \ r)+ /
我的问题是当我尝试搜索时,apache在preg_match上崩溃了以下字符串中的前2个正则表达式之一:
分隔符$$
创建表格MovieDetail
(
imdbid varchar(32)主键不为空,
title varchar(512),
年int,
等级varchar(16) ,
发布int,
runtime int,
director varchar(128),
writer varchar(12) ,$ / $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ,
类型varchar(64)
); $$
将此文本作为单独的声明检测
i尝试用// s和// g替换转义序列它仍然崩溃只是相同的
我正在使用XAMPP与Apache 2.4.17和PHP 5.6.23(VC11 X86 32位线程安全)+ PEAR
i尝试在debugex.com上测试它们,两种表达方式都有效。
主要更新:好像问题似乎只有当我在多行字符串上运行表达式时才会显示自己,所以我将尝试比较2个字符串的二进制数据,其中我用\ n或\\\\ n取代换行符
更新:问题似乎只发生在多个空格字符上。
i am trying to use regular expressions to extract singular sql statements from a file containing several sql statements and alternate delimiters/comments.
i am trying to match the following patterns to isolate sql statements, then after isolating an individual statement, stripping it of comments:
"delimiter (del) (nonwhitespace sequence) (not (del) or comment with (del)) (del)"
"(not ; ) ;"
the first pattern should allow the use of any set of characters for a delimiter
What I have tried:
i tried the following to match the first pattern:
"/\s*delimiter\s+(?<d>[^\s]+)\s*;?\s*(?<qstr>(((?!--|\g{d}).)*|--[^\R]*\R)+)\g{d}\s*;?/s"
and if the first pattern fails, to match the second pattern:
"/\s*(?<qstr>(((?!--|;).)+|--[^\R]*\R)*);/s"
to match the second case
then if either succeeds, replace the following with empty string:
"/--[^\n\r]*(?:\n|\r)+/"
my problem is that apache crashes on preg_match when i try to search for either of the first 2 regular expressions on the following string:
"delimiter $$
create table MovieDetail
(
imdbid varchar(32) primary key not null,
title varchar(512),
year int,
rated varchar(16),
released int,
runtime int,
director varchar(128),
writer varchar(12),
plot varchar(2048),
imageurl varchar(512),
rating float,
ratingcount int,
type varchar(64)
); $$
detect this text as a separate statement"
i tried replacing escape sequences with // like //s and //g and it still crashes just the same
i'm using XAMPP with Apache 2.4.17 and PHP 5.6.23 (VC11 X86 32bit thread safe) + PEAR
i tried testing them on debugex.com and both expressions are valid.
major update: it seems as if the problem only manifests itself when i use run the expressions on multi-line strings, so i'm going to try comparing the binary data of 2 strings where i replace the line break with \n or \r\n
update: the problem seems to occur only with multiple whitespace characters.
推荐答案
create table MovieDetail
(
imdbid varchar(32)主键不为空,
title varchar(512),
年int,
等级varchar(16),
发布int,
runtime int,
director varchar(128),
writer varchar(12),
plot varchar(2048),
imageurl varchar(512),
rating float,
ratingcount int,
type varchar(64)
);
create table MovieDetail
(
imdbid varchar(32) primary key not null,
title varchar(512),
year int,
rated varchar(16),
released int,
runtime int,
director varchar(128),
writer varchar(12),
plot varchar(2048),
imageurl varchar(512),
rating float,
ratingcount int,
type varchar(64)
);
将此文本作为单独的声明检测
i尝试用// s和// g替换转义序列并且它仍然崩溃只是一样
我正在使用XAMPP与Apache 2.4.17和PHP 5.6.23(VC11 X86 32位线程安全)+ PEAR
i尝试在debugex.com上测试它们,两个表达式都有效。< br $>
主要更新:似乎问题只在我使用多行字符串上运行表达式时才会显现,所以我将尝试比较二进制数据2个字符串,其中我用\ n或\\\\ n取代换行符
更新:问题似乎只有多个空白字符才会出现。
detect this text as a separate statement"
i tried replacing escape sequences with // like //s and //g and it still crashes just the same
i'm using XAMPP with Apache 2.4.17 and PHP 5.6.23 (VC11 X86 32bit thread safe) + PEAR
i tried testing them on debugex.com and both expressions are valid.
major update: it seems as if the problem only manifests itself when i use run the expressions on multi-line strings, so i'm going to try comparing the binary data of 2 strings where i replace the line break with \n or \r\n
update: the problem seems to occur only with multiple whitespace characters.
这里有工具链接,帮助构建RegEx并调试它们:
.NET Regex Tester - Regex Storm [ ^ ]
Expresso正则表达式工具 [ ^ ]
这个显示RegEx是一个很好的图表,它真的有助于理解什么是RegEx:
Debuggex:在线可视正则表达式测试器。 JavaScript,Python和PCRE。 [ ^ ]
看起来像(?(不允许。
/ \ * * delimiter \s +(?[^ \]] + )\s * ;? \s *(((( - ?|?!\g {d}))* | - [^ \R] * \R)+)\g {d} \ * *;?/ s
和
/\s*(?(((??!--|;)。)+ | - [^ \ R] * \ R)*); / s
错了。
Here is links to tools to help build RegEx and debug them:
.NET Regex Tester - Regex Storm[^]
Expresso Regular Expression Tool[^]
This one show you the RegEx as a nice graph which is really helpful to understand what is doing a RegEx:
Debuggex: Online visual regex tester. JavaScript, Python, and PCRE.[^]
Looks like "(?(" is not allowed.
"/\s*delimiter\s+(?[^\s]+)\s*;?\s*(?(((?!--|\g{d}).)*|--[^\R]*\R)+)\g{d}\s*;?/s"
and
"/\s*(?(((?!--|;).)+|--[^\R]*\R)*);/s"
are wrong.
这篇关于Php pcre正则表达式用分隔符/注释解析SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!