Regex to capture multiple element

Question

I was writting a regex to capture the mysql query execute in proxysql.

my expectation of the regex will capture the following commend where email, password, or both of them appear together:

SELECT email FROM user_tbl
SELECT password FROM user_tbl
SELECT col1, email FROM user_tbl
SELECT email, password FROM user_tbl

but with my regex it only capture 1 time my regex will be like this

^SELECT\s(.*)(email|password)(.*)FROM\suser_tbl

Is it possible archive what i want ?

A regex which could capture various element

Answer 1

You can use a lookahead assertion (?=<expr>) to make sure email and password are in the query.

Using this regular expression covers your examples given:

SELECT\s+((?=.*?(email|password)).*?)\s+user_tbl

I took the freedom to change \s to \s+ since it is allowed to use newlines and indentation as well.

See demo below where I also added another query which doesn't contain email or password to prove it doesn't find those (demo is for JavaScript and just for demonstration purposes):

var sql = 'SELECT email FROM user_tbl\
SELECT password FROM user_tbl\
SELECT col1, email FROM user_tbl\
SELECT email, password FROM user_tbl\
SELECT col1, col2 FROM user_tbl';

var res = sql.matchAll(/SELECT\s+((?=.*?(email|password)).*?)\s+user_tbl/gm);

console.log(...res);

Answer 2

SELECT\b(?:.*?\b(?:email|password)\b)+.*\bFROM\suser_tbl\b