SQL queries with 'IN' in Java or Groovy

Question

I have written a SQL query:

select sum(amount) as totalAmount 
from employee_account 
where id = 29967 
and repayment_status in ("MARKEDPAID", "PAID", "PENDING", "OVERDUE");

This query is working fine in MySQL console. I have to execute this query in Java using

String query = ""
int id = 123
Connection connection = // connection code 
Statement statement = connection.createStatement()
ResultSet resultSet = statement.executeQuery(query)

I write the

query = "select sum(amount) as totalAmount from employee_account where id = ${id} and repayment_status in ()"

How do I pass "MARKEDPAID", "PAID", "PENDING", "OVERDUE" in above String query?

in ('MARKEDPAID', 'PAID', 'PENDING', 'OVERDUE') is this is correct? @AnkitSharma — chaitanya gupta, Dec 21 '21 at 13:34
Yeah MySQL is fine with single or double any quotes as long as you are consistent — Ankit Sharma, Dec 21 '21 at 13:38
@Prog_G I tried multiples way like for ex- List strings = ["PAID","PENDING"] and then in query i pass this list but nothing works — chaitanya gupta, Dec 21 '21 at 14:07
why there are 2 language tags? do you need `java` or `groovy`? — daggett, Dec 21 '21 at 14:20
https://stackoverflow.com/questions/1327074/how-to-execute-in-sql-queries-with-springs-jdbctemplate-effectively — Lizhi Liu, Dec 21 '21 at 14:21
Thanks Guys I figured out how to implement this Thanks for your time @LizhiLiu — chaitanya gupta, Dec 21 '21 at 14:34
Your code is vulnerable to SQL injection. Do not use string interpolation for adding values into a query string, use JDBC parameters. — Mark Rotteveel, Dec 24 '21 at 15:42

score 0 · Accepted Answer · answered Dec 21 '21 at 14:33

0

String status = "\'OVERDUE\',\'PAID\',\'PENDING\',\'MARKEDPAID\'"
String query = "select sum(amount) as totalAmount from employee_account where id = ${id} and repayment_status in (${status})"

answered Dec 21 '21 at 14:33

chaitanya gupta

302
5
25

Your code is vulnerable to SQL injection. Do not use string interpolation for adding values into a query string, use JDBC parameters (the use of `${status}` is safe in **this** case, but `${id}` is not!). – Mark Rotteveel Dec 24 '21 at 15:42

SQL queries with 'IN' in Java or Groovy

1 Answers1