Is it possible to use IN clause in plain sql Slick for integers?

Question

There is a similar question here but it doesn't actually answer the question.

Is it possible to use IN clause in plain sql Slick?

Note that this is actually part of a larger and more complex query, so I do need to use plain sql instead of slick's lifted embedding. Something like the following will be good:

val ids = List(2,4,9)
sql"SELECT * FROM coffee WHERE id IN ($ids)"

Answer 1

The sql prefix unlocks a StringContext where you can set SQL parameters. There is no SQL parameter for a list, so you can easily end up opening yourself up to SQL injection here if you're not careful. There are some good (and some dangerous) suggestions about dealing with this problem with SQLServer on this question. You have a few options:

Your best bet is probably to use the #$ operator together with mkString to interpolate dynamic SQL:

val sql = sql"""SELECT * FROM coffee WHERE id IN (#${ids.mkString(",")})"""

This doesn't properly use parameters and therefore might be open to SQL-injection and other problems.

Another option is to use regular string interpolation and mkString to build the statement:

val query = s"""SELECT * FROM coffee WHERE id IN (${ids.mkString(",")})"""
StaticQuery.queryNA[Coffee](query)

This is essentially the same approach as using #$, but might be more flexible in the general case.

If SQL-injection vulnerability is a major concern (e.g. if the elements of ids are user provided), you can build a query with a parameter for each element of ids. Then you'll need to provide a custom SetParameter instance so that slick can turn the List into parameters:

implicit val setStringListParameter = new SetParameter[List[String]]{
    def apply(v1: List[String], v2: PositionedParameters): Unit = {
        v1.foreach(v2.setString)
    }
}

val idsInClause = List.fill(ids.length)("?").mkString("(", ",", ")")
val query = s"""SELECT * FROM coffee WHERE id IN ($idsInClause)"""
Q.query[List[String], String](query).apply(ids).list(s)

Since your ids are Ints, this is probably less of a concern, but if you prefer this method, you would just need to change the setStringListParameter to use Int instead of String: