Identify the sql statements using python

Question

The task is to identify if the SQL statements is a DML / DDL or not.

So I had to use an array and push all the DML/DDL patterns into that and search for them by iterating.

Below is a simple code snippet where

I am sending an SQL query as a parameter
Check if it has update, alter, drop, delete
Print

def check_dml_statementent (self,sql)
    actions = ['update', 'alter', 'drop', 'delete']
    for action in actions
    if ( re.search (action,sql.lower() ) :
        print "This is a dml statement "

But there are some edge cases, for which I need to code for

To consider

Update table table_name where ...
alter table table_name
create table table_name
delete * from table_name
drop table_name

Not to consider:

select * from table where action='drop'

So, the task is to identify only SQL statements which modify, drop, create, alter table etc.

One specific idea is to check if an SQL statement starts with above array values using startswith function.

This is probably one of those cases where you should elaborate more on why you want to do that. My spidey senses tell me that this is not something you should do. — Maurice
– Maurice, Commented Jan 3, 2023 at 16:05
I think you have your definition backwards. DML statements simply add/update/remove rows in a table (i.e. INSERT, UPDATE, DELETE, etc.). They do not alter the table structure. Perhaps you meant DDL, not DML? — John Gordon
– John Gordon, Commented Jan 3, 2023 at 16:07
Whatever it is you're doing, you're probably better off creating a user with read-only permissions on the database and let that execute the statement. The database will check if the user is authorized to modify data and will block these kinds of operations more effectively than you'll be able to do it ;-) - Google SQL injection and whitelisting/blacklisting keywords to learn more. — Maurice
– Maurice, Commented Jan 3, 2023 at 16:14
@Maurice Actually , the requirement is just to log the information and not block the statements , just that we should know which user ran it. — LearningCpp
– LearningCpp, Commented Jan 11, 2023 at 6:58

Alex Bochkarev · Accepted Answer · 2023-01-03 17:20:02Z

3

You can use python-sqlparse for that:

import sqlparse

query = """
select * from table where action='delete';
delete from table where name='Alex'
"""

parsed = sqlparse.parse(query)
for p in parsed:
    print(p.get_type())

This will output:

SELECT
DELETE

answered Jan 3, 2023 at 17:20

Alex Bochkarev

3,0111 gold badge21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Identify the sql statements using python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related