sql - 带有 SQL 注入(inject)保护的简单查询比没有的要花费更长的时间

Question

我对Oracle非常缺乏经验。这里发生了什么？
查询一:

SELECT COUNT(*) 
  FROM MUHSCHEMA.MUH_TABLE
 WHERE MUH_DATE = TO_DATE(
                          TRIM(
                            '''' FROM SYS.DBMS_ASSERT.ENQUOTE_LITERAL('09/30/2020')), 
                            'mm/dd/yyyy'
                          );

查询 B:

SELECT COUNT(*) 
  FROM MUHSCHEMA.MUH_TABLE
 WHERE MUH_DATE = TO_DATE('09/30/2020', 'mm/dd/yyyy');

查询 A 大约需要 22 分钟。查询 B 大约需要 28 秒。而且，看起来， TO_DATE带或不带 ENQUOTE_LITERAL 的调用返回相同的东西。
为什么查询 A 需要这么长时间？
查询计划:

| Id  | Operation                 | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                          |     1 |     9 |   411K  (2)| 00:00:17 |       |       |
|   1 |  SORT AGGREGATE           |                          |     1 |     9 |            |          |       |       |
|   2 |   VIEW                    | A_TABLE                  |    71M|   610M|   411K  (2)| 00:00:17 |       |       |
|   3 |    UNION-ALL              |                          |       |       |            |          |       |       |
|   4 |     PARTITION RANGE ALL   |                          |    28M|   214M| 42669  (15)| 00:00:02 |     1 |1048575|
|   5 |      PARTITION LIST ALL   |                          |    28M|   214M| 42669  (15)| 00:00:02 |     1 |    25 |
|*  6 |       INDEX FAST FULL SCAN| A_TABLE.                 |    28M|   214M| 42669  (15)| 00:00:02 |     1 |1048575|
|   7 |     PARTITION RANGE ALL   |                          |    42M|   327M|   368K  (1)| 00:00:15 |     1 |1048575|
|   8 |      PARTITION LIST ALL   |                          |    42M|   327M|   368K  (1)| 00:00:15 |     1 |    25 |
|*  9 |       INDEX RANGE SCAN    | A_TABLE.                 |    42M|   327M|   368K  (1)| 00:00:15 |     1 |1048575|
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

"   6 - filter(""MUH_DATE""=TO_DATE(TRIM('''' FROM ""DBMS_ASSERT"".""ENQUOTE_LITERAL""('09/30/2020')),'mm/dd/yy"
              yy'))
"   9 - access(""MUH_DATE""=TO_DATE(TRIM('''' FROM ""DBMS_ASSERT"".""ENQUOTE_LITERAL""('09/30/2020')),'mm/dd/yy"
              yy'))

查询 B 计划:

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                          |     1 |     9 | 36612   (1)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE           |                          |     1 |     9 |            |          |       |       |
|   2 |   VIEW                    | A_TABLE.                 |    28M|   241M| 36612   (1)| 00:00:02 |       |       |
|   3 |    UNION-ALL              |                          |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE|                          |    28M|   214M| 36608   (1)| 00:00:02 |   250 |   250 |
|   5 |      PARTITION LIST ALL   |                          |    28M|   214M| 36608   (1)| 00:00:02 |     1 |    25 |
|*  6 |       INDEX FAST FULL SCAN| A_TABLE                  |    28M|   214M| 36608   (1)| 00:00:02 |  6226 |  6250 |
|   7 |     PARTITION RANGE SINGLE|                          |     1 |     8 |     4   (0)| 00:00:01 |    93 |    93 |
|   8 |      PARTITION LIST ALL   |                          |     1 |     8 |     4   (0)| 00:00:01 |     1 |    25 |
|*  9 |       INDEX RANGE SCAN    | A_TABLE.                 |     1 |     8 |     4   (0)| 00:00:01 |  2301 |  2325 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

"   6 - filter(""MUH_DATE""=TO_DATE(' 2020-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))"
"   9 - access(""MUH_DATE""=TO_DATE(' 2020-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))"

Answer 1

the value '09/30/2020' comes from a web request

然后任何处理 Web 请求的东西几乎肯定会支持参数化查询和绑定(bind)变量。不要尝试使用字符串连接构建查询，然后使用 DBMS_ASSERT要尝试防止 SQL 注入(inject)，只需使用绑定(bind)变量。
匿名绑定(bind)变量通常具有 ?占位符(但您应该检查处理 Web 请求的任何服务的语法):

SELECT COUNT(*) 
FROM   MUHSCHEMA.MUH_TABLE
WHERE  MUH_DATE = TO_DATE( ?, 'mm/dd/yyyy');

或者命名绑定(bind)变量通常以 : 为前缀， 像这样:

SELECT COUNT(*) 
FROM   MUHSCHEMA.MUH_TABLE
WHERE  MUH_DATE = TO_DATE( :variable_name, 'mm/dd/yyyy');

更好的是，如果您可以在处理 Web 请求的任何内容中将字符串转换为日期，那么您可以将日期值传递给绑定(bind)变量，而无需使用 TO_DATE :

SELECT COUNT(*) 
FROM   MUHSCHEMA.MUH_TABLE
WHERE  MUH_DATE = :date_variable_name;

we're talking about how oracle translates what seems to be two similar queries into two radically different implementations.

如果有人正在执行全表扫描并尝试使用 DBMS_ASSERT.ENQUOTE_LITERAL在每一行上，重复这样做需要很多时间。解决方案可能是使用索引，但更好的解决方案是不使用 DBMS_ASSERT.ENQUOTE_LITERAL并将值作为 DATE 传递到查询中通过绑定(bind)变量的数据类型。
How to write SQL injection proof PL/SQL您在第 31 页的评论状态中引用的文件:

Rule 6: Use compile-time-fixed SQL statement text unless you cannot.

绑定(bind)变量让您可以使用编译时固定的 SQL 语句；无论变量的值是什么，语句都不需要更改，您可以确定查询不会受到 SQL 注入(inject)的影响。