确定sql语句是否以SELECT单词开头 [英] Determine if a sql statement starts with the word SELECT

问题描述

我正在尝试对输入的SQL字符串进行一些基本验证.我想确保查询的第一个单词是SELECT，否则我将引发错误.以下是一些示例:

I am trying to do some basic validation on an input SQL string. I want to make sure the first word of the query is SELECT or else I'll raise an error. Here are some examples:

1) yes
SELECT * FROM table

2) no
# SELECT * FROM table;
DROP TABLE table;

3) no
   /* SELECT * FROM 
   TABLE */ DROP TABLE table;

4) yes
# here is a comment
SELECT * FROM table

5) yes
/* here is a
comment */ SELECT * FROM table

还有其他各种.也许这更多是一个正则表达式解决方案或string.replace.但是，查看是否已输入SQL SELECT语句的一种好方法是什么?

And various others. Perhaps this is more of a regex solution or string.replace. But what would be a good way to see if a SQL SELECT statement has been entered?

推荐答案

我将为此工作使用适当的工具-

I would use a proper tool for the job - sqlparse SQL parser, get the first statement object and check if it's of a SELECT type:

In [1]: import sqlparse

In [2]: queries = [
   ...:     "SELECT * FROM table",
   ...:     """
   ...:     # SELECT * FROM table;
   ...:     DROP TABLE table;
   ...:     """,
   ...:     """/* SELECT * FROM 
   ...:    TABLE /* DROP table table;""",
   ...:    """
   ...:    # here is a comment
   ...:    SELECT * FROM table
   ...:    """
   ...: ]

In [3]: def is_select(query):
             first_statement = next((token for token in sqlparse.parse(query) if isinstance(token, sqlparse.sql.Statement)), None) 
             if first_statement:
                 return first_statement.get_type() == 'SELECT'

In [4]: for query in queries:
    ...:     print(query, is_select(query))

    SELECT * FROM table 
    True

    # SELECT * FROM table;
    DROP TABLE table;
    False

   /* SELECT * FROM 
   TABLE /* DROP table table; 
   False

   # here is a comment
   SELECT * FROM table
   True

这篇关于确定sql语句是否以SELECT单词开头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！