Wednesday, April 17, 2013

"In" Clause with Anorm

At Lucidchart, two coding styles have been adopted because Anorm doesn't support the 'in' clause.

  1. Code that allows for, or is conducive to, SQL injection.

    Example:
    SQL(""" SELECT * FROM users WHERE id IN (%s) """.format(userIds.mkString(",")))

  2. Code that is hard to follow and duplicate.

    Example
    val params = List(1, 2, 3) 
    val paramsList = for ( i <- 0 until params.size ) yield ("userId" + 
    i) // results in List("userId0", "userId1", "userId2") 
    SQL(""" SELECT * FROM users WHERE id IN ({%s}) """.format(paramsList.mkString("},{")) // 
    produces "id in ({userId0},{userId1},{userId2})" 
    .on(paramsList.zip(params)) // produces Map("userId0" -> 1, 
    "userId1" -> 2, ...)
Ugly much? The madness ends here with code samples.

Now, the code.

We're going to create a class that can be converted to an Anorm SqlQuery object. Super simple.

In models/AnormImplicits.scala

package models

import anorm._

object AnormImplicits {
class 
RichSQL val query: String, val parameterValues: (Any, ParameterValue[Any])*) {
/**
* Convert this object into an anorm.SqlQuery
*/
def toSQL = SQL(query).on(parameterValues: _*)
/**
* Similar to anorm.SimpleSql.on, but takes lists instead of single values.
* Each list is converted into a set of values, and then passed to anorm's
* on function when toSQL is called.
*/
def onList[A](args: (String, Iterable[A])*)(implicit toParameterValue: (A) => ParameterValue[A]) = {
val condensed = args.map { case (name, values) =>
val search = "{" + name + "}"
val valueNames = values.zipWithIndex.map { case (value, index) => name + "_" + index }
val placeholders = valueNames.map { name => "{" + name + "}" }
val replace = placeholders.mkString(",")
val converted = values.map { value => toParameterValue(value).asInstanceOf[ParameterValue[Any]] }
val parameters = valueNames.zip(converted)
(search, replace, parameters)
}
val newQuery = condensed.foldLeft(query) { case (newQuery, (search, replace, _)) =>
newQuery.replace(search, replace)
}
val newValues = parameterValues ++ condensed.map { case (_, _, parameters) => parameters }.flatten
new 
RichSQL(newQuery, newValues: _*)
}
}
object RichSQL {
def apply[A](query: String) = new 
RichSQL(query)
}
}


That's it! Now you can use it in a model.

RichSQL(""" SELECT * FROM users WHERE id IN ({userIds}) """).onList("userIds" -> userIds).toSQL.as(userParser *)(connection)

Benefits

  1. Easy to read and use
  2. Works flawlessly with Anorm
  3. Prevents SQL Injection
  4. Works with all types of values - strings, longs, ints, uuids, binary, etc.

2 comments:

  1. Hi. Since https://github.com/playframework/playframework/pull/2285 was merge, Anorm provides encoding for a Seq parameter.

    ReplyDelete