时间戳

首页 > 解决方案 > 带有 SQL 注入保护的简单查询比没有的要花费更长的时间

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

问题描述

我对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'))"

标签： sqloraclesqlperformanceoracle19c

解决方案

值“09/30/2020”来自网络请求

然后任何处理 Web 请求的东西几乎肯定会支持参数化查询和绑定变量。不要尝试使用字符串连接构建查询，然后使用它DBMS_ASSERT来尝试防止 SQL 注入，只需使用绑定变量即可。

匿名绑定变量通常具有?占位符（但您应该检查处理 Web 请求的任何服务的语法）：

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

或者命名绑定变量通常以 为前缀:，如下所示：

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

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

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

我们正在讨论 oracle 如何将看似两个相似的查询转换为两个完全不同的实现。

如果一个人正在执行全表扫描并尝试DBMS_ASSERT.ENQUOTE_LITERAL在每一行上使用，那么重复执行此操作将花费大量时间。解决方案可能是使用索引，但更好的解决方案是根本不使用并通过绑定变量DBMS_ASSERT.ENQUOTE_LITERAL将值作为数据类型传递给查询。DATE

您在第 31 页的评论状态中引用的如何编写 SQL 注入证明 PL/SQL文档：

规则 6：除非你不能，否则使用编译时固定的 SQL 语句文本。

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

推荐阅读