last_executed_query() incorrectly substitutes parameters on Oracle.

[Brian]

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)

[Mariusz Felisiak]

Thanks for the report. I attached a small regression test:

tests/backends/tests.py

@@ -101 +101 @@
                 pk=1,
                 reporter__pk=9,
             ).exclude(reporter__pk__in=[2, 1]),
+            Article.objects.filter(pk__in=list(range(20, 31))),
         ):
             sql, params = qs.query.sql_with_params()
             with qs.query.get_compiler(DEFAULT_DB_ALIAS).execute_sql(CURSOR) as cursor:

[Mariusz Felisiak]

Would you like to prepare a patch?

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In 249ecc43:

Fixed #33815 -- Fixed last_executed_query() on Oracle when parameter names overlap.