regexproxysql

Regex to capture multiple element

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

Solution

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