Scala Play Framework Anorm SQL.on disable wrapping replacements with ' '

Question

Whenever I replace placeholders in the SQL query using on it surrounds the replacement with '', is there a way to prevent this?

It means I can't do things like

SQL("SELECT * FROM {table} blah").on("table" -> tabletouse)

because it wraps the table name with '' which causes an SQL syntax error.

score 4 · Accepted Answer · answered Jul 30 '12 at 14:53

you could certainly combine both approaches, using the format function for data you don't want to be escaped

SQL(
  """
    select %s from %s
    where
      name = {name} and
      date between {start} and {end}
    order by %s
  """.format(fields, table, order)
).on(
  'name     -> name,
  'start    -> startDate,
  'end      -> endDate
)

Just take into account that the data you are sending using the format function should NOT come from user input, otherwise it should be properly sanitized

It's coming from a configuration determined by user input, this is what I need. Thanks. — UberMouse, Jul 30 '12 at 20:42

score 1 · Answer 2 · edited May 23 '17 at 12:29

You cannot do what you are trying. Anorm's replacement is based on PreparedStatements. Meaning all data will automatically be escaped, meaning you cannot use replacement for :

table names,
column names,
whatever operand, SQL keyword, etc.

The best you can do here is a String concatenation (and what is really a bad way in my opinion) :

SQL("SELECT * FROM " + tabletouse + " blah").as(whatever *)

PS : Checkout this question about table names in PreparedStatements.

Scala Play Framework Anorm SQL.on disable wrapping replacements with ' '

2 Answers2