播放Framework 2.0正确的方式来表示使用Anorm的查询中的集合 [英] Play Framework 2.0 correct way to represent a set in a query using Anorm

问题描述

我正在尝试使用Anorm使用查询返回结果列表，该查询返回一组ID的匹配行.例如

I am trying to return a list of results using Anorm using a query that returns matching rows for a set of ids. Eg.

select *
from example
where id in (1,2,3,4,5)

如果我尝试

SQL(
    """
      select *
      from example 
      where id in ({ids})
    """
  ).on('ids -> ids).as(int("id") ~ str("name") *)

其中ids是字符串"1,2,3,4,5"，它将仅返回第一行.注入一组ID的正确方法是什么?

where ids is the String "1,2,3,4,5" it will only return the first row. What is the correct way to inject the set of ids?

推荐答案

没有简单的方法来完成AFAIK.

There's no simple way of doing it AFAIK.

这是我解决的方法:

def findSomething(ids: String) = {
  // Split up the comma separated values
  val sids = ids split ","
  // Create a list of keys (id0, id1, id2, ...)
  val keys = for ( i <- 0 until sids.size ) yield ("id" + i)
  // Create a seq of parameterized values
  val values = sids map (toParameterValue(_))

  // Now zip together the keys and values into list of tuples
  val params = keys zip values

  DB.withConnection { implicit connection =>
    SQL(
      """
        select *
        from example 
        where id in ({%s})
      """.format(keys.mkString("},{"))
    ).on(
      params: _*
    ).as(
      int("id") ~ str("name") *
    )
  }
}

NB
这里最关键的部分是SQL语句中的字符串格式.如果您无法完全控制输入参数，则很容易受到SQL注入的攻击.

NB
The cruical part here is the string formatting in the SQL statement. It is vulnerable for SQL injection if you don't have total control of your input parameters.

这篇关于播放Framework 2.0正确的方式来表示使用Anorm的查询中的集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！