How can I escape backslashes in MySQL with JDBC?

Question

I want to insert something like \mytext into a MySQL database using JDBC. I tried to use the Java String replace method to find all backslashes and turn them into double-backslashes. In Java itself, I have to escape each backslash by a double-backslash, so in my understanding this should be it:

String sqlProcessed = sqlString.replace("\\", "\\\\")

The resulting string I send to the database using an insert statement, surrounded by 'single quotes' like this:

String query = "INSERT INTO table VALUES ('" + sqlProcessed + "')";

However, instead of showing the expected result, I get a double-backslash in the database, i.e., \\mytext. On the other hand, without the processing, the backslash is just omitted, i.e., I get mytext in the database.

What am I doing wrong?

@marekful You mean like this: replace("\\", "\\\")? Java does not allow this, the third backslash is interpreted to escape the quotes which leads me out of the string. — lukas.coenig, Aug 06 '15 at 18:30

score 3 · Accepted Answer · answered Aug 06 '15 at 20:33

3

Don't concatenate values into the query string (the only valid exception is if you need to dynamically determine table and columnnames).

Instead you should use parameterized (or prepared) statements:

For example

try (PreparedStatement preparedStatement = connection.prepareStatement(
        "insert into myTable (column1, column2, column3) values (?, ?, ?)")) {
    preparedStatement.setString(1, "value1");
    preparedStatement.setInt(2, 2);
    preparedStatement.setString(3, "value3");
    preparedStatement.executeUpdate();
}

Using prepared statements will remove the need to escape for the database (either the driver takes care of this, or the parameter values are sent to the server in a way that doesn't need escaping), you only need to take care of the normal Java String escaping.

As an added bonus you can get a reusable statement that you can execute multiple times with different values. And on most database systems (but IIRC not on MySQL with the default settings of the Connector/J driver), using prepared statements can (and usually will) have a performance benefit when reused.

answered Aug 06 '15 at 20:33

Mark Rotteveel

100,966
191
140
197

Ok, I get it. Thanks! I'm very new to JDBC... So I did it all with regular statements. Now I'll have to reimplement the core of my database communication. Is there any reason not to use PreparedStatements or can I just completely switch to those? – lukas.coenig Aug 07 '15 at 06:07
1

For DDL, prepared statement isn't very useful, but other than that: use a `PreparedStatement` (or `CallableStatement` for stored procedures) unless there is a very good reason not to. – Mark Rotteveel Aug 07 '15 at 06:26
1

Thank you so much. I knew PreparedStatements before, but I did not get why they are so useful. It was quite some effort, but my code is MUCH cleaner now! – lukas.coenig Aug 07 '15 at 17:10
@MarkRotteveel in my situation I need to create query dynamically, so I cannot create prepared statement. Is there any way around this? – Dhruv Pal Mar 18 '19 at 16:59
@DhruvPal Having to create a query dynamically is too vague, and doesn't preclude use of prepared statements. Consider asking a question and be explicit and detailed about the problem you want to solve. – Mark Rotteveel Mar 19 '19 at 10:44
@MarkRotteveel here is one question I created for this https://stackoverflow.com/questions/55227084/backslash-and-quote-in-search-insert-query-jdbc – Dhruv Pal Mar 19 '19 at 11:11
@DhruvPal That question doesn't really seem to have to do with having to build a query dynamically (and if it does, the question doesn't make it clear at all), but specifically how the SQL dialect of PostgreSQL handles backslashes (maybe combined with how Java handles them). – Mark Rotteveel Mar 19 '19 at 11:14

score -1 · Answer 2 · edited Dec 12 '19 at 12:55

-1

I suggest you to use query parameters instead of making a query with parameters texted.

The driver will help you to feed the \.

edited Dec 12 '19 at 12:55

Peter Mortensen

30,738
21
105
131

answered Aug 06 '15 at 17:23

Mark I

1

2

How would I do that? – lukas.coenig Aug 06 '15 at 17:37

How can I escape backslashes in MySQL with JDBC?

2 Answers2