SQLException: Invalid MySQL syntax

[Waldemar Macijewski]

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...

import java.util.*;
import java.sql.*;
public class CreateDB {
Connection con;
Statement st;
ResultSet rs;
PreparedStatement prs;
public void connectAsUser(String username) throws ClassNotFoundException {
String query = "CREATE DATABASE "+"?"+"use "+"?"+"CREATE TABLE "+"? "+"(ID INTEGER, NAME VARCHAR(220));"+
"INSERT INTO "+"?"+"VALUES "+"("+"?,"+"?"+");";
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", username, "cowz");
prs = con.prepareStatement(query);
prs.setString(1, username);
prs.setString(2, username);
prs.setString(3, "Contacts");
prs.setString(4, "Contacts");
prs.setInt(5, 77);
prs.setString(6, "Entry from CreateDB");
prs.executeUpdate();
} catch (SQLException e) { System.out.println("Error: "+e.toString()); e.printStackTrace(); }
finally {
if (con != null)
try {
con.close();
} catch (SQLException e) { System.out.println("Error closing connection: "+e.toString()); }
}
}
public String CreateUser() throws ClassNotFoundException {
String value = null;
String name = null;
String username = "Vadzik";
byte[] buffer = username.getBytes();
LinkedList<String> list = new LinkedList<String>();
list.add(new String(buffer));
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "somepass");
st = con.createStatement();
String query = "CREATE USER"+"?"+"@'localhost' IDENTIFIED BY 'cowz';" ;
prs = con.prepareStatement(query);
ListIterator<String> it = list.listIterator();
while (it.hasNext()) {
name = it.next();
}
prs.setString(1, name);
String secondquery = "GRANT CREATE,SELECT,INSERT ON *.* TO "+"?"+"@'localhost';";
prs = con.prepareStatement(secondquery);
prs.setString(1, name);
prs.executeUpdate();
rs = st.executeQuery("SELECT User from mysql.user;");
while (rs.next()) {
if (rs.getString("User").equalsIgnoreCase(username)) {
value = username;
System.out.println("Found it: "+value);
}
}
} catch (SQLException e) {
System.out.println("Error: "+e.toString()); e.printStackTrace();
}
finally {
if (con != null) try {
con.close();
} catch (SQLException e) { System.out.println("Error closin db: "+e.toString()); }
}
return value;
}
public static void main(String[] args) {
try {
CreateDB obj = new CreateDB();
String realname = obj.CreateUser();
obj.connectAsUser(realname);
} catch (ClassNotFoundException e) { System.out.println(e.toString()); }
}
}

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]

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

 String query = "CREATE DATABASE "+"?"+"use "+"?"+"CREATE TABLE "+"? "+"(ID INTEGER, NAME VARCHAR(220));"+
"INSERT INTO "+"?"+"VALUES "+"("+"?,"+"?"+");"; 

Here's an equivalent version with less string concatenation:

 String query = "CREATE DATABASE ?use ?CREATE TABLE ? (ID INTEGER, NAME VARCHAR(220));"+
"INSERT INTO ?VALUES (?,?);"; 

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]

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 Vashko]

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]

 

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]

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

INSERT INTO ?VALUES (?,?)

There is a space missing between the table name and the "VALUES" constant. Your original code had several examples of those missing spaces.