[Django] #33815: Oracle sql tracking incorrectly substitutes in parameters when 10 or more parameters are used.

[Django]

#33815: Oracle sql tracking incorrectly substitutes in parameters when 10 or more
parameters are used.
-----------------------------------------+------------------------
Reporter: Brian | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: dev
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
Since Django 3.0, the variable substitution performed in the Oracle
backend's `last_executed_query` function can incorrectly track the sql
statements issued to the backend. Here's the method copied from
https://github.com/django/django/blob/main/django/db/backends/oracle/operations.py#L304:

{{{
def last_executed_query(self, cursor, sql, params):
# https://cx-
oracle.readthedocs.io/en/latest/api_manual/cursor.html#Cursor.statement
# The DB API definition does not define this attribute.
statement = cursor.statement
# Unlike Psycopg's `query` and MySQLdb`'s `_executed`, cx_Oracle's
# `statement` doesn't contain the query parameters. Substitute
# parameters manually.
if isinstance(params, (tuple, list)):
for i, param in enumerate(params):
statement = statement.replace(
":arg%d" % i, force_str(param, errors="replace")
)
elif isinstance(params, dict):
for key, param in params.items():
statement = statement.replace(
":%s" % key, force_str(param, errors="replace")
)
return statement
}}}

The problem is that `statement.replace` will end up replacing all matches
in the statement, even those that are not a full match for the argument
identifier. This can result in values that are a mashup of the subbed in
value and the argument identifiers. For example, if you have values A-L
that need to be substituted into a query, you'd have 12 arguments that
would need to be substituted in, and the following scenario would occur:

Statement Pre-substitution:

{{{
SELECT
"EMPLOYEE"."ID",
"EMPLOYEE"."USERNAME",
"EMPLOYEE"."NAME",
"EMPLOYEE"."EMAIL"
FROM "EMPLOYEE"
WHERE "EMPLOYEE"."ID" IN (:arg0, :arg1, :arg2, :arg3, :arg4, :arg5,
:arg6, :arg7, :arg8, :arg9 :arg10, :arg11)
}}}

Statement Post-substitution:

{{{
SELECT
"EMPLOYEE"."ID",
"EMPLOYEE"."USERNAME",
"EMPLOYEE"."NAME",
"EMPLOYEE"."EMAIL"
FROM "EMPLOYEE"
WHERE "EMPLOYEE"."ID" IN (A, B, C, D, E, F, G, H, I , J, B0, B1)
}}}

Expected Output:

{{{
SELECT
"EMPLOYEE"."ID",
"EMPLOYEE"."USERNAME",
"EMPLOYEE"."NAME",
"EMPLOYEE"."EMAIL"
FROM "EMPLOYEE"
WHERE "EMPLOYEE"."ID" IN (A, B, C, D, E, F, G, H, I , J, K, L)
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33815>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.