Opened 8 months ago

Closed 8 months ago

Last modified 8 months ago

#31271 closed Bug (fixed)

Logged queries may interpolate parameters in the wrong order on Oracle.

Reported by: Hans Aarne Liblik Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 3.0
Severity: Release blocker Keywords: oracle sql log debug logging
Cc: Marti Raudsepp Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When making a query

MyObject.objects.filter(field1='value1').exclude(field2__in=['badValue1', 'badValue2'])
django/db/backends/oracle/base.py  'def _fix_for_params()'

gets params as

tuple('value1', 'badValue1', 'badValue2')

and then makes them into a set

enumerate(set(params))

. This changes the order of params, and assigns them key (i.e arg0, arg1, ..). This also changes the SQL query and replaces '%s' with param key's (':arg0', ..). The order or param keys in SQL might not be in order anymore.

After SQL is executed this statement is logged. But for logging in

django/db/backends/oracle/operations.py  'def last_executed_query()'

The code is replacing param keys in SQL (':arg0', ..) with params, but they do not match anymore, since they are not in order in SQL statement anymore

REproducable all the time with more than 1 param for SQL

Change History (7)

comment:1 Changed 8 months ago by Mariusz Felisiak

Component: UncategorizedDatabase layer (models, ORM)
Severity: NormalRelease blocker
Summary: Logged ORM query differs from executed oneLogged queries may interpolate parameters in the wrong order on Oracle.
Triage Stage: UnreviewedAccepted

Thanks for this report. last_execute_query() didn't interpolate parameters before 79065b55a70cd220820a260a1c54851b7be0615a, so we can treat this as a regression. It seems that the only reasonable solution is to use mechanism that preserves ordering in _fix_for_params(), but it can be tricky to add a regression test. I was not able to reproduce an incorrect behavior but I agree that this can happen.

Would you like to prepare a patch?

comment:2 in reply to:  1 Changed 8 months ago by Hans Aarne Liblik

Sadly, I don't have the knowhow.

comment:3 Changed 8 months ago by Marti Raudsepp

Cc: Marti Raudsepp added

comment:4 Changed 8 months ago by Mariusz Felisiak

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:5 Changed 8 months ago by Mariusz Felisiak

Has patch: set

comment:6 Changed 8 months ago by GitHub <noreply@…>

Resolution: fixed
Status: assignedclosed

In 2a038521:

Fixed #31271 -- Preserved ordering when unifying query parameters on Oracle.

This caused misplacing parameters in logged SQL queries.

Regression in 79065b55a70cd220820a260a1c54851b7be0615a.

Thanks Hans Aarne Liblik for the report.

comment:7 Changed 8 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In 2448b31:

[3.0.x] Fixed #31271 -- Preserved ordering when unifying query parameters on Oracle.

This caused misplacing parameters in logged SQL queries.

Regression in 79065b55a70cd220820a260a1c54851b7be0615a.

Thanks Hans Aarne Liblik for the report.
Backport of 2a038521c4eabdc5f6d5026d3dd6d22868e329cd from master

Note: See TracTickets for help on using tickets.
Back to Top