Anorm String Interpolation不替换变量

问题描述

我们正在使用Scala Play，我正在尝试确保所有SQL查询都在使用Anorm的String Interpolation.它适用于某些查询，但是许多查询执行之前并没有真正替换变量.

We are using Scala Play, and I am trying to ensure that all SQL queries are using Anorm's String Interpolation. It works with some queries, but many are not actually replacing the variables before the query is executing.

import anorm.SQL
import anorm.SqlStringInterpolation

object SecureFile 
{
  val table = "secure_file"
  val pk = "secure_file_idx"
  ...

// This method works exactly as I would hope
def insert(secureFile: SecureFile): Option[Long] = {
  DBExec { implicit connection =>
    SQL"""
      INSERT INTO secure_file (
        subscriber_idx,
        mime_type,
        file_size_bytes,
        portal_msg_idx
      ) VALUES (
        ${secureFile.subscriberIdx},
        ${secureFile.mimeType},
        ${secureFile.fileSizeBytes},
        ${secureFile.portalMsgIdx}
        )
      """ executeInsert()
    }
  }

def delete(secureFileIdx: Long): Int = {
  DBExec { implicit connection =>
    // Prints correct values
    println(s"table: ${table} pk: ${pk} secureFileIdx: ${secureFileIdx} ")

    // Does not work
    SQL""" 
      DELETE FROM $table WHERE ${pk} = ${secureFileIdx} 
    """.executeUpdate()

    // Works, but unsafe
    val query = s"DELETE FROM ${table} WHERE ${pk} = ${secureFileIdx}" 
    SQL(query).executeUpdate() 
  }
}
....
}

在PostgreSQL日志中，很明显delete语句未获取正确的值:

Over in the PostgreSQL logs, it's clear that the delete statement has not acquired the correct values:

2015-01-09 17:23:03 MST ERROR:  syntax error at or near "$1" at character 23
2015-01-09 17:23:03 MST STATEMENT: DELETE FROM $1 WHERE $2 = $3
2015-01-09 17:23:03 MST LOG:  execute S_1: ROLLBACK

我尝试了execute，executeUpdate和executeQuery的许多变体，结果相似.目前，我们正在使用基本的字符串替换，但这当然是不好的，因为它没有使用PreparedStatements.

I've tried many variations of execute, executeUpdate, and executeQuery with similar results. For the moment, we are using basic string replacement but of course this is bad because it's not using PreparedStatements.

推荐答案

引入范式字符串插值以传递参数(例如SQL"Select * From Test Where id = $x)，并根据适当的类型在基础PreparedStament上设置插值参数(例如$x)转换(请参见 https://www.playframework.com/documentation/2.3.x上的用例/ScalaAnorm ).

Anorm String interpolation was introduced to pass parameter (e.g. SQL"Select * From Test Where id = $x), with interpolation arguments (e.g. $x) set on underlying PreparedStament according proper type conversion (see use cases on https://www.playframework.com/documentation/2.3.x/ScalaAnorm ).

下一个Anorm版本还将具有#$foo语法，以将参数的插值与标准字符串插值混合在一起.这将允许编写DELETE FROM #$table WHERE #${pk} = ${secureFileIdx}并将其作为DELETE FROM foo WHERE bar = ?执行(如果文字table是"foo"且pk是"bar")，并且将文字secureFileIdx作为参数传递.参见相关的拉动请求.

Next Anorm release will also have the #$foo syntax to mix interpolation for parameter with standard string interpolation. This will allow to write DELETE FROM #$table WHERE #${pk} = ${secureFileIdx} and having it executed as DELETE FROM foo WHERE bar = ? (if literal table is "foo" and pk is "bar"), with literal secureFileIdx passed as parameter. See related pull request.

在下一个版本发布之前，您可以从其主要资源构建Anorm，以便从此更改中受益.

Until next revision is release, you can build Anorm from its master sources ti benefit from this change.

这篇关于Anorm String Interpolation不替换变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！