Use proper sql parser

[staabm]

Related problems

• Query error with parameterised queries #106 (comment)
• Left joined columns should be nullable #122

• phpstan-dba/src/QueryReflection/QuerySimulation.php

  Lines 87 to 92 in cac63a6

  	private static function stripTraillingLimit(string $queryString): ?string
  	{
  	// XXX someday we will use a proper SQL parser,
  	// which would also allow us to support even more complex expressions like SELECT .. LIMIT X, Y FOR UPDATE
  	return preg_replace('/\s*LIMIT\s+["\']?\d+["\']?\s*(,\s*["\']?\d*["\']?)?\s*$/i', '', $queryString);
  	}

• make test of don't report placeholder errors on non-SELECT statements for now #153 pass, without non-SELECT optout workaround
• Added nullable is not null test #172
• unexpected syntax error #175
• Support simulation of date/dateime placeholders #244
• count() Should be inferred as a 0|positive-int #248
• wip: prepared param type validation #65
• Support table aliases in PdoQueryReflector #279
• error on sql-IN() and empty-array #292
• properly simulate IS NULL #297
• min(), max(), avg() inference #309

https://github.com/phpmyadmin/sql-parser

https://github.com/greenlion/PHP-SQL-Parser

[craigfrancis]

Might be worth looking at the PDO function that parses parameters, to see how it works.

It seems to use pdo_parse_params(), which is a bit more complicated than using the BINDCHR regex by itself (used later as PDO_PARSER_BIND).

It supports escaped question marks (??), comments (/* What about X? */ or -- To fix?), and quoted string values (see ANYNOEOF?).

[staabm]

Would be great if you could provide failling testcases if you have special cases in mind

[craigfrancis]

I don't have any real examples, but how about?

• SELECT * FROM cmsdomain WHERE url LIKE "%?%";
• SELECT * FROM cmsdomain WHERE url LIKE "%:tag_name%";
• SELECT * FROM cmsdomain WHERE cmsdomainid IN (1,2) /* What about 3? */
• SELECT * FROM cmsdomain WHERE 1=2; -- :TODO?

[staabm]

I don't have any real examples, but how about?

* `SELECT * FROM cmsdomain WHERE url LIKE "%?%";`

* `SELECT * FROM cmsdomain WHERE url LIKE "%:tag_name%";`

* `SELECT * FROM cmsdomain WHERE cmsdomainid IN (1,2) /* What about 3? */`

* `SELECT * FROM cmsdomain WHERE 1=2; -- :TODO?`

these basic examples are now covered by #145

still on the long run we might better use a sql parser. lets see whether people report problems with their queries when running phpstan-dba on real projects

[staabm]

on twitter the SQLFTW/sqlftw project was suggested as a proper parser