escape character for '@' -- JDBC?

0
votes

I'm making a batch insert to MySQL table:

insert into table1 (field1, field2) values("[email protected]", "f2 value"), ("[email protected]", "another f2 here");

giving error to the character '@' in the value String:

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 'insert into buyers (field1, field2) values ('aa@' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)

How can i get around this - is there some kind of escape charactr for JDBC to work this?

Note: I'm aware of JDBC-batch execution. I'm looking for a solution for the above - if any:

pStat.addBatch();
pStat.executeBatch();

TIA.

Further note: The above insert query runs fine directly on MySQL without JDBC in between. Also note: this isn't an issue when JDBC itself sets up the parameter with pStat.getString("[email protected]"); -- thus the batch execn is a solution.

5
javamysqlsqljdbc
Is the error in the insert itself? Have you tried brackets? – Daniel Marcus
@DanielMarcus pls see "Further note." thx for the comment. – xavierz
Thanks @xavierz that makes it a jdbc issue not a sql one – Daniel Marcus
Does it help to use single quotes instead of double quotes: values('[email protected]', 'f2 value'), ...? – Markus Pscheidt
@MarkusPscheidt no - same error. – xavierz

5 Answers

3
votes

Try using PreparedStatement. It resolves special characters automatically and avoids sql-injection.

String queryStr = "insert into table1 (field1, field2) values(?, ?);"
try {
    PreparedStatement preparedStatement = conn.prepareStatement(queryStr);
    preparedStatement.setString(1, "[email protected]");
    preparedStatement.setString(2, "f2 value");
    preparedStatement.executeUpdate();
} catch (SQLException e) {
    // Error
} finally {
    if (preparedStatement != null) {
        preparedStatement.close();
    }
    if (conn != null) {
        conn.close();
    }
}

More examples: https://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/

1
votes

I don't think the error message is indicating a problem with the '@' at sign character.

MySQL syntax error "right syntax to use near" usually points to the first token where the problem is encountered. In this case, it looks like MySQL is objecting to INSERT.

... near 'insert into buyers (field1, field2) values ('aa@' at line 1 at

I suspect that there is something before that insert in the SQL text, and MySQL is seeing multiple statements. That's just a guess, we're not seeing the actual code.

I recommend displaying the actual contents of the SQL text, before it's executed or prepared.

0
votes

Use single quotes:

insert into table1 (field1, field2) 
  values('[email protected]', 'f2 value'), ('[email protected]', 'another f2 here');
0
votes

Use the UTF-8 code for special characters when running from Java. UTF-8 code for @ is \u0040:

insert into table1 (field1, field2) values("aa\u0040gmail.com", "f2 value"), ("cc\u0040gmail.com", "another f2 here");
0
votes

Was doing two queries separated by ; in one. all resolved. nothing wrong with @.

Thanks for the insightful comments&answers.