我有以下针对postgres数据库的find_by_sql查询:
pick_ids = picks.pluck(:id).join(',')
Pick.find_by_sql("WITH cte1 AS (SELECT DISTINCT user_id, state, pick FROM picks
WHERE id IN (#{pick_ids})), cte2 AS (SELECT user_id, coalesce(sum(amount_won), 0)
as picks_total_won FROM picks WHERE id IN (#{pick_ids}) GROUP BY user_id), cte3 AS
(SELECT user_id, COUNT(CASE WHEN state = 'won' then 1 ELSE null END) AS picks_won,
FROM cte1 GROUP BY user_id) SELECT cte2.picks_total_won, cte3.picks_won,
FROM cte2 INNER JOIN cte3 ON cte2.user_id = cte3.user_id")
当我试图参数化它时(即?, ?…pick_id,pick_id),但是,我得到以下错误:
ArgumentError: wrong number of arguments (3 for 1..2)
(1)可以这样参数化find_by_sql查询吗?如果是,怎么做?
(2)如果查询永远不会收到用户输入的参数,您甚至需要担心SQL注入吗?
最佳答案
首先,您可能希望在SQL中使用%Q{...}
字符串和一些格式,以避免出现无法读取的混乱:
Pick.find_by_sql(%Q{
WITH
cte1 AS (
SELECT DISTINCT user_id, state, pick
FROM picks
WHERE id IN (#{pick_ids})
),
cte2 AS (
SELECT user_id, coalesce(sum(amount_won), 0) as picks_total_won
FROM picks
WHERE id IN (#{pick_ids})
GROUP BY user_id
),
cte3 AS (
SELECT user_id, COUNT(CASE WHEN state = 'won' then 1 ELSE null END) AS picks_won,
FROM cte1
GROUP BY user_id
)
SELECT cte2.picks_total_won, cte3.picks_won
FROM cte2
JOIN cte3 ON cte2.user_id = cte3.user_id
})
根据
picks
的来源,您可以完全避免插值,并嵌入SQL来生成pick_ids
(可能使用另一个CTE)。如果需要从外部输入
pick_ids
,则可以使用find_by_sql
的占位符,但界面有点奇怪:必须将数组传递给find_by_sql
:Pick.find_by_sql([%Q{
WITH
cte1 AS (
SELECT DISTINCT user_id, state, pick
FROM picks
WHERE id IN (:pick_ids)
),
cte2 AS (
SELECT user_id, coalesce(sum(amount_won), 0) as picks_total_won
FROM picks
WHERE id IN (:pick_ids)
GROUP BY user_id
),
cte3 AS (
SELECT user_id, COUNT(CASE WHEN state = 'won' then 1 ELSE null END) AS picks_won,
FROM cte1
GROUP BY user_id
)
SELECT cte2.picks_total_won, cte3.picks_won
FROM cte2
JOIN cte3 ON cte2.user_id = cte3.user_id
}, :pick_ids => some_array_of_ids])
注意参数列表中
[
和]
的位置。关于sql - Rails-如何防止PostgreSQL注入(inject)find_by_sql查询的SQL注入(inject)?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23447397/