JDBC4 syntax error, but MySQL accepts the query

I’ve tried running the following query with JDBC4, which leads to a syntax error:

SELECT m.* 
FROM schema.table m 
INNER JOIN (
    SELECT :id0 as id, :from0 as date_from, :to0 as date_to 
    UNION ALL SELECT :id1 as id, :from1 as date_from, :to1 as date_to 
) tmp 
ON tmp.id = m.id AND m.date BETWEEN tmp.date_from AND tmp.date_to

JavaScript
​x
 
SELECT m.* FROM schema.table m INNER JOIN (    SELECT :id0 as id, :from0 as date_from, :to0 as date_to     UNION ALL SELECT :id1 as id, :from1 as date_from, :to1 as date_to ) tmp ON tmp.id = m.id AND m.date BETWEEN tmp.date_from AND tmp.date_to​

Strangely enough, when I run the query via mysql (on the same database server), the query is accepted and run.

The error message is even more confusing, as there is no closing ) on line 1.

You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near ')' at line 1

JavaScript
 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1​

The server is running MySQL 5.1.

Answer

JDBC accepts parameters on very specific locations only. Usually in place of scalar values. You can’t use parameters for structural items such as columns, tables, or SQL clauses.

If you are in need of this, then you should consider Dynamic SQL or proper SQL Injection.

Advertisement

Answer