9

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)"
Community
  • 1
  • 1
Roy Lin
  • 730
  • 8
  • 17

5 Answers5

18

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:

Community
  • 1
  • 1
Ben Reich
  • 16,222
  • 2
  • 38
  • 59
  • 4
    If `ids` has type `List[Int]` I fail to see how sql injection is possible even if they're user provided. – Daenyth Jul 01 '15 at 18:07
  • 2
    @Daenyth It's definitely less of a concern (although sometimes integer SQL injection can be a problem, by cause a divide by zero or other exceptions, and then exploiting the failed state – Google "sql injection integers"). But I'd say it's best practice to use parameters to avoid problems down the road (e.g. what if another developer changed the type to `String` down to accommodate the new types of IDs that include some characters). I was really just covering the case when it's a `String` here. – Ben Reich Jul 01 '15 at 18:15
  • Thanks for the answer Ben! Very informative of the solutions with possible vulnerabilities. I however agree with @Daenyth that you cannot sql inject with an explicit integer type. – Roy Lin Jul 02 '15 at 19:23
  • 1
    The answer can also be applied to the List[String] case, where it may be a concern. – Dan Gallagher Mar 21 '16 at 16:15
  • 1
    You use `Q.query` here, is it possible to use the `sql` interpolation from slick 3.1.1 and defining a SetParameter? – EdgeCaseBerg Mar 22 '17 at 13:42
9
  val ids = List(610113193610210035L, 220702198208189710L)

  implicit object SetListLong extends SetParameter[List[Long]] {
    def apply(vList: List[Long], pp: PositionedParameters) {
      vList.foreach(pp.setLong)
    }
  }

  val select = sql"""
        select idnum from idnum_0
        where idnum in ($ids#${",?" * (ids.size - 1)})
    """.as[Long]

@Ben Reich is right. this is another sample code, test on slick 3.1.0.

($ids#${",?" * (ids.size - 1)})

trydofor
  • 326
  • 3
  • 7
4

Although this is not universal answer and may not be what the author wanted, I still want to point this out to whoever views this question.

Some DB backends support array types, and there are extensions to Slick that allow setting these array types in the interpolations.

For example, Postgres has the syntax where column = any(array), and with slick-pg you can use this syntax like so:

def query(ids: Seq[Long]) = db.run(sql"select * from table where ids = any($ids)".as[Long])

This brings a much cleaner syntax, which is friendlier to the statement compiler cache and also safe from SQL injections and overall danger of creating a malformed SQL with the #$var interpolation syntax.

Gman
  • 1,781
  • 1
  • 23
  • 38
1

I have written a small extension to Slick that addresses exactly this problem: https://github.com/rtkaczyk/inslick

For the given example the solution would be:

import accode.inslick.syntax._

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

Additionally InSlick works with iterables of tuples or case classes. It's available for all Slick 3.x versions and Scala versions 2.11 - 2.13. We've been using it in production for several months at the company I work for.

The interpolation is safe from SQL injection. It utilises a macro which rewrites the query in a way similar to trydofor's answer

Radek Tkaczyk
  • 29
  • 2
0

Ran into essentially this same issue in Slick 3.3.3 when trying to use a Seq[Long] in an IN query for MySQL. Kept getting a compilation error from Slick of:

could not find implicit value for parameter e: slick.jdbc.SetParameter[Seq[Long]]

The original question would have been getting something like:

could not find implicit value for parameter e: slick.jdbc.SetParameter[List[Int]]

Slick 3.3.X+ can handle binding the parameters for the IN query, as long as we provide the implicit definition of how Slick should do so for the types we're using. This means adding the implicit val definition somewhere at the class level. So, like:

class MyClass {
  // THIS IS THIS KEY LINE TO ENABLE SLICK TO BIND THE PARAMS
  implicit val setListInt = SetParameter[List[Int]]((inputList, params) => inputList.foreach(params.setInt))

  def queryByHardcodedIds() = {
    val ids: List[Int] = List(2,4,9)

    sql"SELECT * FROM coffee WHERE id IN ($ids)" // SLICK CAN AUTO-HANDLE BINDING NOW
  }
}

Similar for the case of Seq[Long] & others. Just make sure your types/binding aligns to what you need Slick to handle:

implicit val setSeqLong = SetParameter[Seq[Long]]((inputList, params) => inputList.foreach(params.setLong))
// ^^Note the `SetParameter[Seq[Long]]` & `.setLong` for type alignment
cjn
  • 1,331
  • 1
  • 16
  • 22