Escaping Strings for MySQL in Java

Question

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

So also my answer in the linked topic didn't help? Another dupe by the way: stackoverflow.com/questions/2861230/… — BalusC
– BalusC, Commented Mar 3, 2011 at 3:19
I did see that -- I was hoping I wouldn't have to do it that way -- but I guess since there is no better answer -- I'll go with that. — debracey
– debracey, Commented Mar 3, 2011 at 14:21

Dan Grossman · Accepted Answer · 2011-03-03 02:50:03Z

2

You have two obvious options:

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

answered Mar 3, 2011 at 2:50

Dan Grossman

52.5k10 gold badges115 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

debracey Over a year ago

Escaping the strings individually would be fine -- but is there some method to do that in Java? I could write my own -- but I have a feelign there's a lot of corner cases

Collectives™ on Stack Overflow

Escaping Strings for MySQL in Java

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related