CODEWITHSUNDEEP
javamysql
ESDEE
ESDEE

Reputation: 125

Escape of Escape character not accepted (Java and MySQL)

I have a statement for MySQL 5.5.58-0+deb8u1 that looks like this:

UPDATE `general` SET `CUSTOMER_NUMBER` = 'null', `MAINTENANCE_CONTRACT_NR` = '5678', `CONTACT1_NAME` =  + 'ss\' where `general`.`uuid` = '06f8417b-c865-11e7-9a86-000c2924bf92'

I try to escape the \ (after ss) with

string statementEsc = statement.replace('\\', '\\\\');

this results in:

UPDATE `general` SET `CUSTOMER_NUMBER` = 'null', `MAINTENANCE_CONTRACT_NR` = '5678', `CONTACT1_NAME` =  + 'ss\\' where `general`.`uuid` = '06f8417b-c865-11e7-9a86-000c2924bf92'

When trying the query manually, MySQL accepts this query, but the program gives an error

MySQL JDBC Driver Registered Problem occured at executeUpdate operation : 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 'ss\', `...at line 1 SQL UPDATE operation has been failed: 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 'ss\', ... = '' at line 1.

I don't understand why.

Upvotes: 0

Views: 390

Answers (3)

user3438137
user3438137

Reputation: 19

Though using PreparedStatement is best answer, try going through this class StringEscapeUtils. It have methods for Escapes and unescapes Strings for Java, Java Script, HTML, XML, and SQL.

Upvotes: 0

Joop Eggen
Joop Eggen

Reputation: 109547

You had a + maybe from copying the code for us.

However this is an excellent opportunity to use a PreparedStatement.

String sql = "UPDATE `general` SET `CUSTOMER_NUMBER` = 'null', "
        + "`MAINTENANCE_CONTRACT_NR` = ?, `CONTACT1_NAME` = ? WHERE `general`.`uuid` = ?";
try  (PreparedStatement stmt = connection.prepareStatement(sql)) {
    stmt.setInt(1, 5678);
    stmt.setString(2, "ss\\"); // One backslash
    stmt.setString(3, "06f8417b-c865-11e7-9a86-000c2924bf92");
    stmt.executeUpdate();
}

By the way = 'null' would assign a string "null" to that field.

Upvotes: 2

Tom Mac
Tom Mac

Reputation: 9853

You've got a rogue plus sign in there.

`CONTACT1_NAME` =  + 'ss\'

Hence this SQL is not valid :

UPDATE `general` SET `CUSTOMER_NUMBER` = 'null', `MAINTENANCE_CONTRACT_NR` = '5678', `CONTACT1_NAME` =  + 'ss\\' where `general`.`uuid` = '06f8417b-c865-11e7-9a86-000c2924bf92'

Suggest you get rid of it in your original String so that you end up with this SQL instead :

UPDATE `general` SET `CUSTOMER_NUMBER` = 'null', `MAINTENANCE_CONTRACT_NR` = '5678', `CONTACT1_NAME` =  'ss\\' where `general`.`uuid` = '06f8417b-c865-11e7-9a86-000c2924bf92'

Another suggestion is to take a look at Prepared Statements. I think they will make your life easier.

Upvotes: 2

Related Questions