PSQL中正常的SQL查询在Slick(PostgreSQL)/Scala应用中执行失败

基于PostgreSQL使用Slick框架，采用原生SQL而非Case Class+TableQuery方式实现CRUD操作，执行查询时触发语法错误。已在psql中验证相同查询语句可正常运行。

报错堆栈信息 #

sql - select id FROM users where id = '1';
SQL Exception occurred: ERROR: syntax error at or near "$1"
  Position: 1
[info] - should describe users *** FAILED ***
[info]   org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
[info]   Position: 1
[info]   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
[info]   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
[info]   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
[info]   at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
[info]   at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
[info]   at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
[info]   at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
[info]   at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
[info]   at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
[info]   at slick.jdbc.LoggingPreparedStatement.$anonfun$execute$5(LoggingStatement.scala:185)
[info]   ...

Scala查询逻辑代码 #

def select(tableName: String, condition: String) = {
  val selectCommand = s"select id FROM $tableName $condition;"
  println(s"sql - $selectCommand")
  val query = sql"$selectCommand".as[String]
  try {
    val result = db.run(query)
    val users = Await.result(result, 10.seconds)
    println(users)
    Future(users)
  } catch {
    case ex: SQLException =>
      println(s"SQL Exception occurred: ${ex.getMessage}")
      throw ex
    case ex: Exception =>
      println(s"An unexpected error occurred: ${ex.getMessage}")
      throw ex
  }
}

PSQL中正常执行的查询 #

banktest=# select id FROM users where id = '1';
 id
----
 1
(1 row)

原因分析 #

Slick的sql""插值器并非简单字符串拼接，它会将传入的完整selectCommand当作单个参数值处理，而非完整SQL语句。这导致PostgreSQL接收到参数化查询，把整个SQL字符串作为$1参数，直接触发语法错误。

修复方案 #

不要提前拼接完整SQL，直接使用Slick插值器拆分变量：

def select(tableName: String, conditionColumn: String, conditionValue: String) = {
  // #$用于表名/列名等标识符的直接拼接，$用于参数值的安全参数化
  val query = sql"""select id FROM #$tableName where #$conditionColumn = $conditionValue""".as[String]
  try {
    val result = db.run(query)
    val users = Await.result(result, 10.seconds)
    println(users)
    Future(users)
  } catch {
    case ex: SQLException =>
      println(s"SQL Exception occurred: ${ex.getMessage}")
      throw ex
    case ex: Exception =>
      println(s"An unexpected error occurred: ${ex.getMessage}")
      throw ex
  }
}

若必须传入完整条件片段（不推荐，存在SQL注入风险），可改用静态SQL：

val query = StaticQuery.queryNA[String](selectCommand).as[String]

关键注意点 #

• sql""插值器区分两种语法：
  • $var：处理参数值，自动参数化，防SQL注入
  • #$var：处理表名/列名等标识符，直接拼接字符串，需确保输入安全
• 禁止提前拼接完整SQL后传入sql""，否则会被识别为单个参数

内容的提问来源于stack exchange，提问作者Janani