1

I try to reproduce the example of SQL interpolation with the library ScalikeJDBC (here).

val member = sql"select id, name from members where id = ${id}"
println(member.statement)

But I get a weird result : 

select id, name from members where id = ?

What I have missed ?

EDIT and ANSWER:

Mea culpa, I tried to debug why my sql query doesn't work with string interpolaiton, so I print the value of the query. I got this result but I didn't check that it was OK. The problem was that I had to use SQLSyntax.createUnsafely on the string that I want to use for interpolation.

Thomas
  • 1,164
  • 13
  • 41

1 Answers1

1

It is not clear from the question what exactly do you find surprising here. I suppose that the thing you find weird is the ? placeholder in the statement. The referenced article shows exactly the same query with a placeholder and even explicitly mentions the reason for it:

Don’t worry, this code is safely protected from SQL injection attacks. ${id} will be a place holder.

If you look at the source for SQL, which is the base for all results of the sql"" interpolations, you may see there two fields:

val statement: String,
private[scalikejdbc] val rawParameters: Seq[Any]

and there is also

  final lazy val parameters: Seq[Any] = rawParameters.map {
    case ParameterBinder(v) => v
    case x => x
  }

This is done to not re-implement tricky SQL escaping logic in the library. Instead standard java.sql.PreparedStatement is used to do that. That's why the query-string is parsed into a placeholder query and a separate parameters list.

P.S. if why some form of escaping is necessary to avoid SQL injections or what's bad about SQL injections is not clear, you probably should read more about SQL injections.

SergGr
  • 23,570
  • 2
  • 30
  • 51
  • I edit the post to explain my mystake. Thank you for your relevant answer – Thomas May 17 '18 at 12:42
  • @Thomas, your real case might or might not need `createUnsafely` but it is impossible to guess basing on the information you provided. Beware that `createUnsafely` as it name suggest is unsafe so you should take care of possible SQL injections yourself. – SergGr May 17 '18 at 12:48
  • Yes I understand it, I just want to automate table creation if doesn't exist. Something like: `create table if not exists ${tableName}`, to do that I have to do: `val tableName = SQLSyntax.createUnsafely("myTable")`, if not I got a sql syntax error – Thomas May 17 '18 at 15:05
  • @Thomas, Yeah, DDL doesn't support placeholders. And if somehow you have many tables with the same structure but different names, this could makes sense. But this is probably a rare case in practice. – SergGr May 17 '18 at 18:13