Geek Answers Handbook

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

There is a similar question here, but in reality it does not answer the question.

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

Note that this is actually part of a more complex and more complex query, so I need to use plain sql instead of slick lifted embedding. Something like the following would be fine:

val ids = List(2,4,9) sql"SELECT * FROM coffee WHERE id IN ($ids)"
+5
source share
3 answers

The sql prefix unlocks the StringContext , where you can set SQL parameters. There is no SQL parameter for the list, so you can easily insert it into SQL injection if you are not careful. There are some good (and some dangerous) suggestions for resolving this issue with SQLServer on this question . You have several options:

It is best to use the #$ operator along with mkString to interpolate dynamic SQL:

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

This uses parameters incorrectly and therefore may be open to SQL injection and other problems.

Another option is to use standard 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 can be more flexible in the general case.

If the SQL injection vulnerability is the main problem (for example, if the ids elements are provided by the user), you can build a query with a parameter for each ids element. Then you need to provide a custom instance of SetParameter so that slick can include the List in the 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 is Ints , this is probably less of a concern, but if you prefer this method, you just need to change setStringListParameter to use Int instead of String :

+11
source 
  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 example code, a test for slick 3.1.0.

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

+3
source

Although this is not a universal answer and may not be what the author wanted, I still want to point out this, no matter who considers this issue.

Some database databases support array types, and there are extensions for Slick that allow you to set these array types in interpolation.

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

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

This gives a cleaner syntax that is more cache friendly for the statement compiler, and also safe from SQL injections and the general danger of creating corrupted SQL with interpolation syntax #$var .

+1
source

More articles:


All Articles