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

Question

For example, I want to create the following query:

SELECT c.* FROM Coffees c WHERE c.name IN ('robusta', 'arabica')

My attempt failed:

val cnames = List("robusta", "arabica")
sql""" SELECT c.* FROM Coffees c WHERE c.name IN ${cnames} """
  could not find implicit value for parameter pconv: 
  scala.slick.jdbc.SetParameter[List[String]]

Is it possible to somehow use in clause in Slick plain sql queries?

What puzzles me most, is that in Slick's lifted embedding, this is almost an effortless task. — Rogach, Jul 01 '13 at 15:53
I use `slick-pg` and `select * from Coffees where array_position(${cnames}, name) is not null`. — Marcello Nuccio, Jul 02 '19 at 15:30
For newer versions of Slick, was also answered here: https://stackoverflow.com/questions/31156613/is-it-possible-to-use-in-clause-in-plain-sql-slick-for-integers — cjn, Oct 25 '21 at 19:03

score 32 · Accepted Answer · answered Jul 02 '13 at 10:11

32

The type safe "lifted embedding" API supports this as well:

val ids = List(1,2,3)
val q = for {
  f <- Foo if f.id inSet ids // ids is not bound
}

slick.typesafe.com/doc/1.0.1/api/index.html#scala.slick.lifted.ColumnExtensionMethods

answered Jul 02 '13 at 10:11

cvogt

11,260
30
46

3

Thanks! I was able to use this in Slick 3.0 as well: query.filter(_.id inSet ids) – panther Aug 28 '15 at 22:11
17

How does this apply to plain SQL Slick (as is asked by the question)? I'm looking for that answer & this doesn't apply. – jm0 Feb 25 '16 at 20:05
6

It doesn't apply. If you use `ids` you get `Could not find implicit value for parameter e: slick.jdbc.SetParameter[List[String]]`. Downvoting because although it works in other cases, it doesn't answer this question. Related issue: https://github.com/slick/slick/issues/1335 – juanignaciosl Sep 13 '16 at 06:15

score 4 · Answer 2 · edited Feb 13 '20 at 17:31

4

Although it's not safe for SQL injection, you can use #$ interpolator:

val ids = idList.mkString("'", "','", "'")
val q = sql"""select name from mytable where id in (#$ids)"""

edited Feb 13 '20 at 17:31

mkUltra

2,828
1
22
47

answered Sep 13 '16 at 06:21

juanignaciosl

3,435
2
28
28

score 2 · Answer 3 · edited Feb 13 '20 at 17:31

2

I don't see anything out of the box to handle this. You're best bet is probably something like this:

val cnames = List("robusta", "arabica").mkString("'", "','", "'")
val query = sql""" SELECT c.* FROM Coffees c WHERE c.name IN (${cnames}) """

edited Feb 13 '20 at 17:31

mkUltra

2,828
1
22
47

answered Jul 01 '13 at 16:30

cmbaxter

35,283
4
86
95

1

@Rogarch, as things have changed in slick and my answer is no longer correct, could you please change the accepted answer to the one below so I can delete this answer? – cmbaxter Jul 14 '14 at 16:03
2

This will work if the interpolation uses #$ instead of $, but it's still very unsafe. :) http://slick.typesafe.com/doc/2.1.0/sql.html `scala> sqlu""" update tbl_foo set x = 1 where y in #$z """ res30: scala.slick.jdbc.StaticQuery[Unit,Int] = scala> res30.getStatement res31: String = " update tbl_foo set x = 1 where y in (1,2) " ` (sorry I have no idea how to format this nicely when I can't insert returns) – qu1j0t3 Apr 07 '15 at 22:15
4

Not too familiar with slick, but doesn't this introduce a SQLi vulnerability? Or is it handled by the `sql` quasiquote? – Lukas Eder Feb 01 '17 at 12:22

score 1 · Answer 4 · answered Apr 02 '22 at 14:07

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 cnames = List("robusta", "arabica")
sqli""" SELECT c.* FROM Coffees c WHERE c.name IN *${cnames} """

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 this answer to a similar question.

score 0 · Answer 5 · answered Mar 19 '19 at 18:57

There is a library that (among other things) introduces a binder for list properties to Slick's SQL interpolator: https://index.scala-lang.org/tarao/slick-jdbc-extension-scala/slick-jdbc-extension

Example code from the page:

  import util.NonEmpty

  def findAll(entryIds: Option[NonEmpty[Long]]): Seq[Entry] = entryIds match {
    case Some(ids) => run { sql"""
    | SELECT * FROM ${table}
    | WHERE entry_id IN $ids
    """.as[Entry] }
    case None => Seq.empty
  }

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

5 Answers5

Linked