0

I want to insert the literal '${a}' into a table using anorm 2.5.2, which means I want to execute the bare SQL query

INSERT INTO `db`.`table` (`a`) VALUES ('${a}');

without using any anorm / string interpolation. When I try to do the following

SQL("INSERT INTO `db`.`table` (`a`) VALUES ('${a}');").execute()

I get an anorm.Sql$MissingParameter: Missing parameter value exception because it tries to use anorm interpolation on ${a} but no value a is available in the scope.

How to escape the anorm / string interpolations $... and ${...}?

Escape a dollar sign in string interpolation doesn't seem to work here.

Community
  • 1
  • 1
user2860570
  • 11
  • 3
  • 1
    The string is not a String Interpolation, so you don't need to escape the `$` in the first place. The problem is not caused by String Interpolation. – Clashsoft Oct 07 '16 at 15:45
  • I should have said anorm interpolation, or whatever you want to call it, to make it more obvious what I mean. Yes, I know it does not use Scala's string interpolation internally, since then the $$ escape should work, which it doesn't. I'll change the question to reflect that. – user2860570 Oct 09 '16 at 14:22

1 Answers1

0

You can make ${a} the value of a parameter, i.e.

SQL("""INSERT INTO db.table (a) VALUES ({x})""").on("x" -> s"$${a}")

(s"$${a}" is the way to write "${a}" without getting a warning about possible missing interpolators).

The same can be written equivalently as

val lit = s"$${a}"
SQL"""INSERT INTO db.table (a) VALUES ($lit)"""

The below will probably work, but I am not sure:

SQL"INSERT INTO db.table (a) VALUES ('$${a}')"

It may also be worth asking if it's intentional behavior or a bug: when talking about parametrized SQL queries, it doesn't make sense to have a parameter inside '.

Alexey Romanov
  • 167,066
  • 35
  • 309
  • 487
  • A value in the SQL parameters should never be passed using plain string interpolation or `#$` with Anorm interpolation, otherwise it introduces SQL injection risk. – cchantep Oct 08 '16 at 11:23
  • 1
    @cchantep But it isn't a parameter according to the question. – Alexey Romanov Oct 08 '16 at 12:35
  • Using it in `VALUES` means it is – cchantep Oct 08 '16 at 12:37
  • @cchantep I don't agree (so far as it's an actual literal and not dynamically constructed), but on further thought `#$` is too large a hammer for this. I've rewritten the answer. – Alexey Romanov Oct 08 '16 at 13:13
  • As long as a value is given to be updated or inserted in a column, that's a parameter, and so not setting it appropriately keep the risk of SQL injection. – cchantep Oct 08 '16 at 13:48
  • Thanks, you actually understood my question. Unfortunately I cannot upvote you since I don't have 15 reputation. Yes, setting a string like `val a = "${a}"` and then using anorm interpolation or `.on("a" -> a)` would work, but is not quite nice, since I actually need this quite often. Escaping with `$$` unfortunately doesn't work like I wrote in the last sentence of my question. Yes, I was also wondering if it is intentional that you don't seem to be able to escape the dollar sign or not. – user2860570 Oct 09 '16 at 14:12
  • Note that in last solution I use Anorm's string interpolator: `SQL"..."`, not `SQL("...")`. It isn't clear from your last sentence whether you've tried `$$` in this case. – Alexey Romanov Oct 09 '16 at 14:39
  • (Whether it'll actually work depends on details of Anorm's implementation.) – Alexey Romanov Oct 09 '16 at 14:43
  • You are right, `SQL"..."` actually supports escaping, meaning they behave differently. You can also use `SQL"#$query"` to insert a whole bare SQL query, which also doesn't work with `SQL("#$query")`. I don't believe any of this is intentional, it appears quite arbitrary. As a matter of fact, the documentation doesn't mention anything about `SQL("...")` supporting any kind of string interpolation by itself. – user2860570 Oct 10 '16 at 08:15
  • It's quite intentional. `SQL(...)` is just a method, it doesn't do any string interpolation (of course, you can write something like `SQL(s"$cmd * FROM $table ..."`, but you _should_ be careful not to use normal string interpolation for parameters). It just happens to search for `{...}` inside this string and convert it to a prepared statement parameter. This is also why `#$` makes no sense for it. – Alexey Romanov Oct 10 '16 at 13:55