I am trying to return a list of results using Anorm using a query that returns matching rows for a set of ids. Eg.
select *
from example
where id in (1,2,3,4,5)
If I try
SQL(
"""
select *
from example
where id in ({ids})
"""
).on('ids -> ids).as(int("id") ~ str("name") *)
where ids is the String "1,2,3,4,5" it will only return the first row. What is the correct way to inject the set of ids?
There's no simple way of doing it AFAIK.
This is how I solved it:
def findSomething(ids: String) = {
// Split up the comma separated values
val sids = ids split ","
// Create a list of keys (id0, id1, id2, ...)
val keys = for ( i <- 0 until sids.size ) yield ("id" + i)
// Create a seq of parameterized values
val values = sids map (toParameterValue(_))
// Now zip together the keys and values into list of tuples
val params = keys zip values
DB.withConnection { implicit connection =>
SQL(
"""
select *
from example
where id in ({%s})
""".format(keys.mkString("},{"))
).on(
params: _*
).as(
int("id") ~ str("name") *
)
}
}
NB
The cruical part here is the string formatting in the SQL statement. It is vulnerable for SQL injection if you don't have total control of your input parameters.
