正则表达式提取SQL查询 [英] Regular expression to extract SQL query

问题描述

是否有正则表达式从字符串中提取SQL查询？我 NOT 对验证任何SQL语法感兴趣，而只是提取一些SQL命令。这将以灵活的方式解析给定的SQL文件/字符串。

Is there a regex which extracts SQL queries from a string? I'm NOT interested to validate any SQL syntax, rather and only extracting a selection of SQL commands. This to parse a given SQL file/string in a flexible manner.

给出以下SQL文件/字符串示例：

Given is the following SQL file/string example:

SELECT
    *
FROM
    test_table
WHERE
    test_row = 'Testing ; semicolon';

SELECT * FROM another_test_table;

INSERT INTO 
    table_name 
VALUES 
    (value1,'value which contains semicolon ;;;;',value3,...);

一些伪代码示例如下： ^（UPDATE | SELECT | INSERT INTO） （*）（;）$ 。在将来，我希望用所有（可能的）命令扩展它。

Some pseudocode example would be: ^(UPDATE|SELECT|INSERT INTO)(.*)(;)$. In the future i'm looking to extend this with all (possible) commands.

• 寻找与以下任何一个的起始匹配:(更新| SELECT | INSERT | INTO）


• 零或多个任何字符（包括空格和换行符）


• 停在; ，它界定了SQL查询。

• Look for a starting match with either: (UPDATE|SELECT|INSERT|INTO)
• Zero or more any character (including whitespaces and newlines)
• Stop at ;, which delimits the SQL query.

每当这个可以通过正则表达式，以下java代码能够提取所有SQL命令：

Whenever this would be possible via a regex the following java code is able to extract all SQL commands:

final String regex = "LOOKING_FOR_THIS_ONE";
final Pattern p = Pattern.compile(regex, Pattern.MULTILINE);
final Matcher matcher = p.matcher(content);

while (matcher.find()) {
  // matcher.group() now contains the full SQL command
}

提前致谢！

推荐答案

我会首先说这不是一个很好的方法，并强烈建议你找到另一种方法，最好在做出陈述的地方正确地标记它，这样你就不会在这种情况下结束。

I'll start off by saying that this is not a good way of doing it, and strongly urge you to find another method of doing it, preferrably tagging it properly where the statements are made, so you don't end up in this situation.

话虽如此，SQL要求它从下列之一开始; DELETE ， SELECT ， WITH ， UPDATE 或 INSERT INTO 。它还要求输入以; 结尾。

That being said, SQL requires it to start with one of the following; DELETE, SELECT, WITH, UPDATE or INSERT INTO. It also requires that the input ends with ;.

我们可以使用它来获取与SQL匹配的所有序列以下：

We can use this to grab all sequences matching SQL with the following:

final String regex = "^(INSERT INTO|UPDATE|SELECT|WITH|DELETE)(?:[^;']|(?:'[^']+'))+;\\s*$";
final Pattern p = Pattern.compile(regex, Pattern.MULTILINE | Pattern.DOTALL);

如果您希望在<$ c $上过滤有效的SQL，则第1组现在保留操作字c> UPDATE 或 SELECT 。

查看正在运行的正则表达式，以及洞穴在这里：

See the regex in action, as well as a cave-at here:

https：// regex101.com/r/dt9XTK/2

这篇关于正则表达式提取SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！