Performing regex search on a sql query string python 3.x

Question

I would like to find out if a select some_col_name, * , some_col_name from table_name occurs in a query string like example below:

select some_col_name, *, some_col_names
from table_name

or

select some_col_name, 
*, some_col_name
from 
table_name

So far, I am only able to match select some_col_names, * using

re.search("select(\s*.*?)*\*", query_string)

How do I match the desired expression? I would like to stop after matching the first occurrence.

This smacks of an XY problem. Why do you want to do this? It's not that it's impossible, but it's probably not the *right* way to do whatever it is you're trying to do. — Adam Smith, Feb 22 '19 at 21:30
Do you want to find all queries that select `*` as a column? — Bohemian, Feb 22 '19 at 21:30
Given a query, I want to know if a user tried to select all columns in a specific table_name (using select * as opposed to mentioning the column name). But note that the query string could have multiple select statements because of any use of WITH. — cidi30bg, Feb 22 '19 at 22:26

Bohemian · Accepted Answer · 2019-02-22T22:40:59.927

1

Try this regex:

select([^*](?!from))*\*([^*](?!from))* from[\s\r\n]*table_name

This will match over multiple lines, but due to the negative look aheads won’t skip from the select of one query to the from of a later query.

edited Feb 22 '19 at 22:40

answered Feb 22 '19 at 21:37

Bohemian

Thanks @Bohemian, but I want to also add a specific table name to the match. You example matches only up to from. In addition, I want to only stop at the first occurrence. – cidi30bg Feb 22 '19 at 22:28
@obiigbe91 I’ve added the table name to the regex. If you want to match only first, see [this answer](https://stackoverflow.com/a/38579883/256196) for how. – Bohemian Feb 22 '19 at 22:45

1 Answers1