Search code examples
mysqlvalidationinsert

Why does an SQL validator show a syntax error?

I am using various websites to check my SQL syntax.
https://www.eversql.com/sql-syntax-check-validator/
https://www.piliapp.com/mysql-syntax-check/

INSERT INTO Table1 (Column1, Column2)
VALUES ('bbbbbbb', 'aaa');

INSERT INTO Table2 (Column1, Column2, Column3)
VALUES ('bbbbb', 'hhhh', 'eeee');

While the SQL works on my end, it is showing an error on the validator sites. What is the best explanation for this?

You have an error in your SQL syntax; it seems the error is around: 'INSERT `INTO Table2 (Column1, Column2, Column3) VALUES ('bbbbb', 'hhhh', 'eee' at line 5

Solution

  • If I paste each INSERT statement into the eversql syntax checker one at a time, there is no error reported.

    The error message you showed indicates that the parser got confused as soon as you started the second INSERT.

    These two facts are a strong indicator that the syntax checker does not support checking multiple SQL statements in one go.

    I assume the checker works by doing prepare() on the statement, which would check the syntax without executing the statement. Prepare does not support multi-query. This is documented: https://dev.mysql.com/doc/refman/8.0/en/c-api-multiple-queries.html

    The multiple statement and result capabilities can be used only with mysql_query() or mysql_real_query(). They cannot be used with the prepared statement interface. Prepared statement handlers are defined to work only with strings that contain a single statement

    Also see confirmations like this one: https://bugs.mysql.com/bug.php?id=9121