0

I have a list as follows -

List<String> Friends = ImmutableList.of("Ross","Joey","Chandler");

And the following query-

Select * from EmployeeTable where name IN ('%1$s');

I am trying to figure out a way to insert this list into the query using String.format() in JAVA.

I want the query to eventually look like -

Select * from EmployeeTable where name IN ('Ross','Joey','Chandler');

Please Help me out

Dawson Smith
  • 473
  • 1
  • 6
  • 15
  • 1
    What are you using ? Spring Data ? JPA ? JDBC Template ? Please provide a complete reproducible example – Yassin Hajaj Apr 06 '21 at 19:03
  • Actually creating this query for Amazon Athena – Dawson Smith Apr 06 '21 at 19:05
  • Working on Spring – Dawson Smith Apr 06 '21 at 19:05
  • Something like `String.format("Select * from EmployeeTable where name IN ('%s');", friends.stream().collect(Collectors.joining( ", ")));` ? Works with Java versions >= 8. Not a MWE with SQL but just an idea. – Ludovic Kuty Apr 06 '21 at 19:07
  • @LudovicKuty I tried this. This produces something like - Select * from EmployeeTable where name IN ('Ross,Joey,Chandler'); – Dawson Smith Apr 06 '21 at 19:09
  • Closed as duplicate since you said Spring. – Andreas Apr 06 '21 at 19:14
  • Added second duplicate link, in case you're using plain JDBC. – Andreas Apr 06 '21 at 19:15
  • @Andreas I have to do it using String.format only – Dawson Smith Apr 06 '21 at 19:17
  • @Andreas These links don't answer my question – Dawson Smith Apr 06 '21 at 19:19
  • You should **never** build a SQL statement using string concatenation of string values that are not guaranteed to be SQL-safe. Since a persons name is entered by a user, it is by nature not SQL-safe. – Andreas Apr 06 '21 at 19:22
  • Ludovic was on the right track, but got it wrong: `friends.stream().map(n -> "'" + n + "'").collect(Collectors.joining(","))` --- Be aware that this still leaves you vulnerable to SQL syntax errors and SQL injection attacks, where hackers can steal your data and destroy your database. – Andreas Apr 06 '21 at 19:23
  • @Andreas This produces something like - ```Select * from EmployeeTable where name IN (' 'Ross','Joey','Chandler' '); There is still an extra single quote at the ends``` – Dawson Smith Apr 06 '21 at 19:28
  • Come on man, at least make an *attempt* at thinking about this for yourself. ***Look at the code!!!*** If the result has extra `'` after `(` and extra `'` before `)`, and you're formatting using `format("Select ... IN ('%s')`, then ... ... there are extra `'` in there ... ... perhaps ... ... removing them might do something ... – Andreas Apr 06 '21 at 22:57
  • Oh my, I forgot the `map` operation! Answered too quickly without proper thinking. This little example is a nice way to think about functional programming thanks to that `map`. The remark by @Andreas about string concatenation and SQL is also worth some thinking. I encourage you to spend time on both of those. – Ludovic Kuty Apr 07 '21 at 03:42

0 Answers0