I have a PlayFramework server (with Anorm) which operates against a database with several schemas, all of them with the same tables.
Most of my "access to database" functions look like:
def findById(zoneName: String, id: Long): Option[Employee] = {
DB.withConnection { implicit connection =>
SQL("""select *
from """+zoneName+"""employee
where employee._id = {id}"""
.on(
'_id -> id
).as(simpleParser.singleOpt)
}
}
But I know this is a wrong approach, because it is not SQL-Injection-safe and of course it is tedious to write in every function.
I want to use String interpolation to correct this, it works well with my id
variable, but it doesn't with zoneName
:
def findById(zoneName: String, id: Long): Option[Employee] = {
DB.withConnection { implicit connection =>
SQL"""select *
from $zoneName.employee
where employee._id = 1"""
.as(simpleParser.singleOpt)
}
}
Gives me:
info] ! @6lenhal6c - Internal server error, for (GET) [/limbo/br/employee/1] ->
[info]
[info] play.api.Application$$anon$1: Execution exception[[PSQLException: ERROR: syntax error at or near «$1»
[info] Position: 25]]
[info] at play.api.Application$class.handleError(Application.scala:296) ~[play_2.11-2.3.8.jar:2.3.8]
[info] at play.api.DefaultApplication.handleError(Application.scala:402) [play_2.11-2.3.8.jar:2.3.8]
[info] at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3$$anonfun$applyOrElse$4.apply(PlayDefaultUpstreamHandler.scala:320) [play_2.11-2.3.8.jar:2.3.8]
[info] at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3$$anonfun$applyOrElse$4.apply(PlayDefaultUpstreamHandler.scala:320) [play_2.11-2.3.8.jar:2.3.8]
[info] at scala.Option.map(Option.scala:146) [scala-library-2.11.5.jar:na]
[info] Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near «$1»
[info] Position: 25
[info] at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198) ~[postgresql-9.3-1102.jdbc4.jar:na]
[info] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927) ~[postgresql-9.3-1102.jdbc4.jar:na]
[info] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.3-1102.jdbc4.jar:na]
[info] at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561) ~[postgresql-9.3-1102.jdbc4.jar:na
Tested also with ${zoneName}
with the same result.
Any help or advice about how to write this would be appreciated, thank you in advance!