Calling a string interpolator on a variable in Scala

Question

I am writing some database query helper methods for my classes in Scala.

This select method is intended to pluck certain columns from the Product table, specified as a comma-seperated list (String) of the desired columns.

I want to be able to call it like this: Product.select("id, title")

The following code does not work:

  def select(columns: String) = { DB.readOnly{ implicit session =>
    sql"select ${columns} from $sqlTable limit 1000".map(row => Product(row)).list.apply()
  }}

But this code (for illustration purposes only) does

  def select(columns: String) = { DB.readOnly{ implicit session =>
    var x = sqls"id, title, description, available_online"
    sql"select ${x} from $sqlTable limit 1000".map(row => Product(row)).list.apply()
  }}

Now, obviously I don't want to hard code columns into the method, but i want to do this with the columns parameter string (I hope you catch my drift). How can I apply the sqls interpolator on the columns string?

It would be something like var x = sqls(columns)

Here you can find more info on the sqls and sql interpolators

Please comment if you require more information and I'd appreciate your feedback.

Constructing SQL as strings like this is not the right way to go - it's how SQL injection happens. The string interpolators are a convenient shortcut for turning static strings into SQL, but once you want to start constructing queries dynamically your "columns" argument should already be the structured representation created by `sqls` and you should manipulate it in a structured way, not as a `String`. — lmm, Nov 12 '14 at 15:39
@Imm The whole point of the ScalikeJBDC sql library is to filter out sql injection automatically so you can have the control of using raw sql. And the input for this function is from controllers only anyway, it's not user input — Marco Prins, Nov 13 '14 at 07:08

score 0 · Answer 1 · answered Nov 12 '14 at 18:24

Check class SQLSyntax. It could be used for your purposes. "sqls" is syntax sugar for SQLSyntax

def select(columns: String) = { DB.readOnly{ implicit session =>
var x = SQLSyntax("id, title, description, available_online", Seq.empty)
sql"select ${x} from $sqlTable limit 1000".map(row => Product(row)).list.apply()}}

If you interest deeply, see at class SQLInterpolationString

score 0 · Answer 2 · answered Nov 17 '14 at 09:10

You can use SQLSyntax#createUnsafely instead.

https://github.com/scalikejdbc/scalikejdbc/blob/2.2.0/scalikejdbc-core/src/main/scala/scalikejdbc/interpolation/SQLSyntax.scala#L220

This is a dangerous API. I know you're aware of SQL injection vulnerability so much.

Calling a string interpolator on a variable in Scala

2 Answers2

Linked