File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQLException: Invalid MySQL syntax Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQLException: Invalid MySQL syntax" Watch "SQLException: Invalid MySQL syntax" New topic
Author

SQLException: Invalid MySQL syntax

Waldemar Macijewski
Ranch Hand

Joined: Jun 22, 2012
Posts: 32
Hi all, I'm having a bit of trouble with this little program. I have two methods, first which connects to database as a root user and creates another user with the name supplied in the "username" variable. This works, as when I login to mysql shell and look into "mysql.user" table all the results are displayed correctly. Second method attempts to connect database as newly created user, and create some database, table and provide some bogus data. The error says I have bad SQL syntax, but I checked it and it seems fine. At first I thought I have misplaced placeholders (?), but if add them with or without "+" operator - doesn't make any difference. Since I'm using MySQL, and almost every statement requires semicolon at the end, I though adding ";" would help, but nope. Also when using INSERT statement we usually use "''" characters when giving values, that is:
INSERT into SomeTable VALUES (1, 'SomeValue'); I don't have "''" characters in my SQL statement, but if I add them or not, it sill doesn't have any effect.

Man I'm really getting frustrated here...



StackTrace:

Error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''Vadzik'use 'Vadzik'CREATE TABLE 'Contacts' (ID INTEGER, NAME VARCHAR(220));INSE' at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''Vadzik'use 'Vadzik'CREATE TABLE 'Contacts' (ID INTEGER, NAME VARCHAR(220));INSE' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
at CreateDB.connectAsUser(CreateDB.java:31)
at CreateDB.main(CreateDB.java:116)


Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

The way you defined your SQL statement is kind of confusing and hard to read:



Here's an equivalent version with less string concatenation:



Do you see your error now? (It's just what the error message said, actually.) I would advise using the simpler form which I posted -- I don't see any reason for breaking the statement up into tiny strings and concatenating them back together again.


Waldemar Macijewski
Ranch Hand

Joined: Jun 22, 2012
Posts: 32
Yes I understand it's kinda of difficult to read, I tried making it more simple SQL statement (without breaking it into more strings, and I actually tried this before), but still no success. I got the same error message..
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

You cannot use a bind variable (the ? character) to represent a table or a column name. They can be only used to replace a literal, and table names (as well as other identifiers and keywords) are not literals.

If it was possible to use bind variables to specify table names or keywords or other identifiers, using PreparedStatement would not prevent SQL injection. So I'd even say that no serious database allows it.
Waldemar Macijewski
Ranch Hand

Joined: Jun 22, 2012
Posts: 32
Martin Vajsar wrote:You cannot use a bind variable (the ? character) to represent a table or a column name. They can be only used to replace a literal, and table names (as well as other identifiers and keywords) are not literals.

If it was possible to use bind variables to specify table names or keywords or other identifiers, using PreparedStatement would not prevent SQL injection. So I'd even say that no serious database allows it.


Live and learn. I have read so many tutorials regarding JDBC, and yet none of them mentioned a thing that you cannot use "?" placeholder for table and column names (or maybe it's something I missed). Anyway, if it wouldn't be for you, I would never know. Thank you, this solved my problem.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

Also, just for future reference: if using bind variables for table names did work, you would still have problems because you were leaving out necessary whitespace all over the place. For example



There is a space missing between the table name and the "VALUES" constant. Your original code had several examples of those missing spaces.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQLException: Invalid MySQL syntax