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

It looks to me you can match anything between two strings with `^SELECT\s+(.*?)\s+FROM`. — Wiktor Stribiżew, Aug 16 '23 at 10:53

score 0 · Accepted Answer · answered Aug 16 '23 at 11:16

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);

score 0 · Answer 2 · answered Aug 16 '23 at 12:05

0

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

answered Aug 16 '23 at 12:05

William Harris

36
8

Regex to capture multiple element

2 Answers2