Generate sql query by anorm, with all nulls except one

Question

I developing web application with play framework 2.3.8 and scala, with complex architecture on backend and front-end side. As backend we use MS SQL, with many stored procedures, and called it by anorm. And here one of the problems.

I need to update some fields in database. The front end calls play framework, and recive name of the field, and value. Then I parse, field name, and then I need to generate SQL Query for update field. I need assign null, for all parameters, except recived parameter. I try to do it like that:

def updateCensusPaperXX(name: String, value: String, user: User) = {
  DB.withConnection { implicit c =>
    try {
        var sqlstring = "Execute [ScXX].[updateCensusPaperXX] {login}, {domain}"
        val params = List(
          "fieldName1",
          "fieldName2",
          ...,
          "fieldNameXX"
        )
        for (p <- params){
          sqlstring += ", "
          if (name.endsWith(p))
            sqlstring += value
          else
            sqlstring += "null"

        }
        SQL(sqlstring)
          .on(
            "login" -> user.login,
            "domain" -> user.domain,
          ).execute()
    } catch {
      case e: Throwable => Logger.error("update CensusPaper04 error", e)
    }
  }
}

But actually that doesn't work in all cases. For example, when I try to save string, it give's me an error like:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'some phrase'

What is the best way to generate sql query using anorm with all nulls except one?

You just use Option.empty[ParamType] to pass a JDBC NULL. — cchantep

DemetriKots DemetriKots · Accepted Answer · 2015-07-09T02:50:01

The reason this is happening is because when you write the string value directly into the SQL statement, it needs to be quoted. One way to solve this would be to determine which of the fields are strings and add conditional logic to determine whether to quote the value. This is probably not the best way to go about it. As a general rule, you should be using named parameters rather than building a string to with the parameter values. This has a few of benefits:

It will probably be easier for you to diagnose issues because you will get more sensible error messages back at runtime.
It protects against the possibility of SQL injection.
You get the usual performance benefit of reusing the prepared statement although this might not amount to much in the case of stored procedure invocation.

What this means is that you should treat your list of fields as named parameters as you do with user and domain. This can be accomplished with some minor changes to your code above. First, you can build your SQL statement as follows:

    val params = List(
      "fieldName1",
      "fieldName2",
      ...,
      "fieldNameXX"
    )

    val sqlString = "Execute [ScXX].[updateCensusPaperXX] {login}, {domain}," +
      params.map("{" + _ + "}").mkString{","}

What is happening above is that you don't need to insert the values directly, so you can just build the string by adding the list of parameters to the end of your query string.

Then you can go ahead and start building your parameter list. Note, the parameters to the on method of SQL is a vararg list of NamedParameter. Basically, we need to create Seq of NamedParameters that covers "login", "domain" and the list of fields you are populating. Something like the following should work:

    val userDomainParams: Seq[NamedParameter] = (("login",user.login),("domain",user.domain))
    val additionalParams = params.map(p => 
      if (name.endsWith(p)) 
        NamedParameter(p, value)
      else 
        NamedParameter(p, None)
    ).toSeq
    val fullParams = userDomainParams ++ additionalParams
    // At this point you can execute as follows
    SQL(sqlString).on(fullParams:_*).execute()

What is happening here is that you building the list of parameters and then using the splat operator :_* to expand the sequence into the varargs needed as arguments for the on method. Note that the None used in the NamedParameter above is converted into a jdbc NULL by Anorm.

This takes care of the issue related to strings because you are no longer writing the string directly into the query and it has the added benefit eliminating other issues related with writing the SQL string rather than using parameters.

Generate sql query by anorm, with all nulls except one

1 Answers