PHP code inside SQL about sql-formatter HOT 5 CLOSED

The problem is that you're trying to match the string between double quotes:

" . date('y-m-d') . "

with a regular expression

['"]\s?\..*\.\s?['"]

This would work, but the problem is that this string is also inside singe quotes:

'" . date('y-m-d') . "'

That regex would only match from the second character of this string, so it won't succeed. Next sql-formatter will try to match a single-quoted string token, which will succeed, matching the part within single quotes:

'" . date('

It will then continue detecting the following tokens: y, -, m, -, d, ') . "'.

I guess you'll need to cover this case with another more specialized regex. Though fundamentally you can't parse PHP with a regular expression. Hopefully you know the limitations of this approach.

from sql-formatter.

Well it's your call if you want to shoot yourself in the foot, but already in this example SQL, this regex will match the following:

'" . date('Y-m-d') . "', 'YYYY-MM-DD'

That is, it will greedily match everything between first and last quote. It will also happily match from " to ' if it sees the opportunity.

from sql-formatter.

The problem is that you're trying to match the string between double quotes:

" . date('y-m-d') . "

with a regular expression

['"]\s?\..*\.\s?['"]

This would work, but the problem is that this string is also inside singe quotes:

'" . date('y-m-d') . "'

That regex would only match from the second character of this string, so it won't succeed. Next sql-formatter will try to match a single-quoted string token, which will succeed, matching the part within single quotes:

'" . date('

It will then continue detecting the following tokens: y, -, m, -, d, ') . "'.

I guess you'll need to cover this case with another more specialized regex. Though fundamentally you can't parse PHP with a regular expression. Hopefully you know the limitations of this approach.

Thank you! You are absolutely right. I change my config to

{
  language: 'postgresql',
  tabWidth: 4,
  keywordCase: 'upper',
  functionCase: 'upper',
  identifierCase: 'lower',
  linesBetweenQueries: 1,
  paramTypes: {
    custom: [
      { regex: String.raw`\{\$?[\w\d\->\[\]]+\}` },
      { regex: String.raw`\$[\w\d\->\[\]]+` },
      { regex: String.raw`['"].*['"]` },
    ],
    positional: true,
    named: [':', '@'],
  },
}

and everything works

from sql-formatter.

This ['"].*['"] regex is likely not what you want. It will override the builtin double- and single-quoted strings/identifiers matching in SQL Formatter with a broken implementation. You really should use something more specific than that.

from sql-formatter.

This ['"].*['"] regex is likely not what you want. It will override the builtin double- and single-quoted strings/identifiers matching in SQL Formatter with a broken implementation. You really should use something more specific than that.

I've tested some queries, everything seems to be fine

from sql-formatter.