CODEWITHSUNDEEP
javamysqljdbc
debracey
debracey

Reputation: 6597

Escaping Strings for MySQL in Java

I need to do a large SQL query against a MySQL DB using a List of strings in Java, ex:

SELECT * FROM db_table WHERE id IN (....);

Unfortunately the input API is sort of limiting here and I am only given a list of strings as the input.

Normally I would just use a parameterized query, but that won't allow me to pass a comma delimited list so something like the following won't work:

SELECT * FROM db_Table WHERE id IN (?);
preparedStaement.Set(1, "A,B,C,D,E");

I took a look at this thread: PreparedStatement IN clause alternatives?

And unfortunately none of the more creative options will work since I don't have write access to the database, so I can't create a temporary table.

So - any ideas? I only care about MySQL as the underlying source, so if the solution is specific to MySQL that's fine.

Thanks in advance

Edit: Escaping each string individually would be fine -- but what method does that in Java? I haven't had very much luck finding one, I only see a few things from apache, which I can't use.

-- Dan

Upvotes: 1

Views: 2636

Answers (1)

Dan Grossman
Dan Grossman

Reputation: 52372

You have two obvious options:

  1. Build the query dynamically as well as its inputs. That is, for each string you need to include, add a ? to the query before you pass it to the PreparedStatement.

    SELECT * FROM table WHERE id = ? OR id = ? OR id = ? OR id = ? ...

  2. Don't use binding here. Escape the strings individually and put them directly in the query.

...and probably some less obvious options I haven't thought of.

Upvotes: 2

Related Questions