主页 / dba / 问题 / 259604
Accepted
cis
Asked: 2020-02-14 03:06:30 +0800 CST

WHERE 子句不阻止返回行但更改聚合函数的值（此处：计数）

  • 772

我最近发现了一种 PostgreSQL 行为，在我看来这是一种奇怪且有问题的行为。

考虑这个简单的查询

SELECT 
   'something confidential'
WHERE FALSE;

无意义的子句WHERE FALSE在这里类似于一个非常严格的权限检查。这按预期工作：没有返回任何内容。

现在，假设您添加了一个附加列，例如一个count函数。这给出了这个查询：

SELECT 
   'something confidential'
   ,count(CURRENT_DATE)
WHERE FALSE;

（的参数count可以是任何东西。CURRENT_DATE只是一个随机的例子。）

现在，我们得到

机密的东西| 0

我想，这是故意的，SELECT count(CURRENT_DATE);如果条件为真则返回 1，如果条件为假则返回 0。

但是，我认为在您不知道的情况下这是有问题的。所以，我的问题是

  1. 怎么来的？这种行为的背景是什么？
  2. 如何使这样的查询返回零行而不是值为 0 的一行count？
  3. 有没有办法直接在查询中通过添加聚合函数来防止意外返回您不想返回的行？（即除了外部测试等）

我使用的是 PostgreSQL 12.1，但旧版本的行为是相同的。

postgresql security

2 个回答

  1. Best Answer

    这是几乎所有数据库的标准 SQL 行为。

    返回行的方式有以下三种：

    1. 没有聚合函数，查询每行返回一行；
    2. 使用聚合函数，但没有分组，查询只返回一行；
    3. 使用聚合函数和分组，查询为每个组返回一行（即，为分组列中的每个唯一值组合）。

    换句话说，没有分组的聚合表现得好像只有一个组，即，就好像你已经写GROUP BY ()了（带有空列表的 GROUP BY 子句会导致单个组“无处”，类似于没有 FROM 的查询结果）在单行'从无处'）。

    为防止返回此单组行，您可以

    • 添加一个显式的 GROUP BY，以便您在上面的情况 3. 中结束（在 WHERE 过滤掉所有行之后，没有可以从中创建组的值）：

      SELECT 
   'something confidential',
   count(CURRENT_DATE)
WHERE FALSE
GROUP BY 1;  -- refers to the first column

    • 将聚合移动到子查询中，以便权限检查影响其输出：

      SELECT *
FROM (SELECT
          'something confidential',
          count(CURRENT_DATE)
     ) AS subquery
WHERE FALSE;

    • 将权限检查移到 HAVING 子句中（如果可能的话），以便丢弃单个组的输出：

      SELECT 
   'something confidential',
   count(CURRENT_DATE)
HAVING FALSE;
    • 3

  2. 这是预期的行为（不仅适用于 Postgres）。如果我们查看查询的评估逻辑顺序：

    1. 从
    2. 在哪里
    3. 通过...分组
    4. 选择

    我将在示例中添加一个包含一列 (x) 和一行的表 (T)

    我们有：

    SELECT COUNT(1) as CNT
FROM ( VALUES (1) ) AS t(x) 
WHERE false
GROUP BY ()   -- empty set default

    结果是一行值为 0。然后我们向该行添加一个常量“机密信息”并最终得到：

    SELECT 'something confidential', CNT
FROM 
    SELECT COUNT(1) as CNT
    FROM ( VALUES (1) ) AS t(x) 
    WHERE false
    GROUP BY ()   -- empty set default
) AS u

'something confidential', 0
    • 0

相关问题