Simple Query with SQL injection protection takes magnitudes longer than without

user_1857083 0
Viewed 150

I'm very inexperienced on Oracle. What's going on here?

Query A:

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

Query B:

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

Query A takes ~22 minutes. Query B takes ~28 seconds. And, seemingly, both of the TO_DATE calls with or without the ENQUOTE_LITERAL return the same thing.

Why is Query A taking so long?

Query A Plan:

| 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'))

Query B Plan:

----------------------------------------------------------------------------------------------------------------------
| 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'))"
oracle19c sqlperformance oracle sql
1 Answers

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

Then whatever is handling the web request will almost certainly support parametrized queries and bind variables. Don't try to build the query using string concatenation and then use DBMS_ASSERT to try to prevent SQL injection, just use a bind variable.

An anonymous bind variable typically has the ? placeholder (but you should check the syntax for whatever service is handling the web requests):

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

Or named bind variables are usually prefixed with :, like this:

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

Even better, if you can convert the string to a date in whatever is handling the web request then you can pass the date value to the bind variable and do not need to use 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.

If one is doing a full table scan and trying to use DBMS_ASSERT.ENQUOTE_LITERAL on every row then it will take a lot of time to repeatedly do that. The solution could be to use an index but the better solution is to not use DBMS_ASSERT.ENQUOTE_LITERAL at all and to pass the value into the query as a DATE data type via a bind variable.

The How to write SQL injection proof PL/SQL document you reference in comments states on page 31:

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

Bind variables let you use compile-time-fixed SQL statements; regardless of what the variable's value is the statement does not need to change and you can be sure that the query is not vulnerable to SQL injection.

user_15092640
Related