Using `on` to replace placeholders in Scala SQL query

Question

I'm trying to create a findBy method that takes in a key and value to search a table. For some reason the following does not find anything:

def findBy(key: String, value: String): Option[Authentication] = DB.withConnection { implicit connection =>
  SQL("select * from authentications where {key}='{value}' limit 1")
    .on("key" -> key, "value" -> value).as(authentication.singleOpt)
}

But when I just use the plus sign, it does. I wouldn't mind leaving it this way but there are benefits to being able to use on

def findBy(key: String, value: String): Option[Authentication] = DB.withConnection { implicit connection =>
  SQL("select * from authentications where " + key + "='" + value + "' limit 1")
    .as(authentication.singleOpt)
}

Example Query: Authentication.findBy("email", "[email protected]")

Answer 1

Play escapes the query parameters in prepared SQL statements. So you don't actually need to wrap {value} in single quotes. Unfortunately, this means the column names will be escaped with quotes as well by Play, so it'll be interpreted by the database as a string. I don't see a way around this without using string concatenation or string interpolation.

Scala string interpolation would look the code look a little better:

def findBy(key: String, value: String): Option[Authentication] = DB.withConnection { implicit connection =>
    SQL(s"select * from authentications where `$key` = {value} limit 1")
       .as(authentication.singleOpt)
       .on("value" -> value).as(authentication.singleOpt)
}

Notice the "s" before the beginning of the string. The scala compiler will replace the token $key with the string key that's in scope. This should work, but key won't be escaped. So you'll have to sanitize the input for key yourself, unless you're using it internally only. Also, it's important to note that an input for key that isn't a column in the table will raise an SQLException.