I'm trying to write a regex to find all the dynamic parameters and their corresponding columns in an SQL query.

dynamic parameter: every not reserved word in PostgreSQL that starts with a $ sign inside an sql query.

i.e

'SELECT * from Students where grade > $g1 and grade < $g2  and grade < $g3' --> should return dynamic values as keys, and column names as array of values ---> {$g1:grade,$g2:grade,$g3:grade}

'SELECT * from Students where age > $g1 and grade < $g1' ----> {$g1:[grade,age]} we can assume that age and grade must have the column type.

dynamic parameters can occur either from left side of operator or right side of it. but they will always come with some SQL Comparison Operators i.e ( > , < , >= ,<>) OR inside IN clause.

i.e

1. column_name SQL Comparison Operators $dynamic_Param
2. $dynamic_Param SQL Comparison Operators column_name
3. column_name IN ($dynamic_Param, $dynamic_Param)

function findParamColumn(query, param) {
  const regex = new RegExp(`\\b${param}\\b\\s*(?:[ \\-*/<=>!] |IN)\\s*(?:\\$\\w |\\d )`, 'gi');
  const matches = query.match(regex) || [];

  const columnRegex = /\b[a-z_] \b/gi;
  return matches.map(match => match.match(columnRegex)[0]);
}

function findParamsColumns(query) {
  const paramRegex = /\$\w /g;
  const params = query.match(paramRegex) || [];

  const paramsColumns = {};
  for (const param of params) {
    const column = findParamColumn(query, param);
    paramsColumns[column] = param;
  }
  return paramsColumns;
}

Here is what I've done so far the but findParamColumn doesn't return the expected result.

any suggestions and modfications to the regex?

CodePudding user response：

To get some results from your function, you'd need to

• flip param and column in the paramsColumns object construction
• flip the order of operands in the regex you are building - in your examples the column name comes first and the parameter last
• escape the $ sign of the param in your regex construction

With these minimal changes, you'd get the desired result:

for (const test of [
  'SELECT * from Students where grade > $g1 and grade < $g2  and grade < $g3', // {$g1:grade,$g2:grade,$g3:grade}
  'SELECT * from Students where age > $g1 and grade < $g1', // {$g1:[grade,age]}
]) {
  console.log(test);
  console.log(findParamsColumns(test));
}

function findParamColumn(query, param) {
  const regex = new RegExp(`(?:\\w |\\d )\\s*(?:[ \\-*/<=>!] |IN)\\s*\\${param}`, 'gi');
  const matches = query.match(regex) || [];

  const columnRegex = /\b[a-z_] \b/gi;
  return matches.map(match => match.match(columnRegex)[0]);
}

function findParamsColumns(query) {
  const paramRegex = /\$\w /g;
  const params = query.match(paramRegex) || [];

  const paramsColumns = {};
  for (const param of params) {
    const column = findParamColumn(query, param);
    paramsColumns[param] = column;
  }
  return paramsColumns;
}

However, this is not a good approach. There's no reason to use three different regular expressions, or even to construct them dynamically. And it handles only one of the patterns that you want to match. Instead, I would recommend

for (const test of [
  'SELECT * from Students where grade > $g1 and grade < $g2  and grade < $g3', // {$g1:grade,$g2:grade,$g3:grade}
  'SELECT * from Students where age > $g1 and grade < $g1', // {$g1:[grade,age]}
  'SELECT * from Students where $g1 < age and $g2 <= grade', // {$g1:[age], $g2:[grade]}
  'SELECT * from Students where name IN ($a, $b, $c)', // 
]) {
  console.log(test);
  console.log(findParamsColumns(test));
}

function findParamsColumns(query) {
  const paramsColumns = {};
  for (const match of query.matchAll(/(\w )\s*(?:[*\/<=>! -] )\s*(\$\w )/gi)) {
    const column = match[1], param = match[2];
    (paramsColumns[param] ??= []).push(column);
  }
  for (const match of query.matchAll(/(\$\w )\s*(?:[*\/<=>! -] )\s*(\w )/gi)) {
    const param = match[1], column = match[2];
    (paramsColumns[param] ??= []).push(column);
  }
  for (const match of query.matchAll(/(\w )\s*IN\s*\(([^)] )\)/gi)) {
    const column = match[1], params = match[2].match(/\$\w /g);
    for (const param of params) {
      (paramsColumns[param] ??= []).push(column);
    }
  }
  return paramsColumns;
}