Opened 6 months ago

Closed 6 months ago

Last modified 6 months ago

#30307 closed Bug (fixed)

dbshell doesn't pass password properly on Oracle 18c.

Reported by: Mark Gordon Owned by: msg555@…
Component: Database layer (models, ORM) Version: 2.2
Severity: Normal Keywords: oracle dbshell runshell
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

The oracle backend client erroneously backslash escapes the password field passed to the sqlplus binary when trying to execute runshell.

https://github.com/django/django/commit/acfc650f2a6e4a79e80237eabfa923ea3a05d709#diff-54b46d05e1da568b3cc987c423e00c50R197 has the PR and line that introduced this issue.

Expectation:

./manage.py dbshell opens an interactive shell when an Oracle database is configured.

Actual behavior:

./manage.py dbshell fails to login yielding error ORA-01017: invalid username/password; logon denied. Additionally it will print the arguments passed to the sqlplus binary to standard error and reveal the password has unexpected backslash characters in it.

Change History (8)

comment:1 Changed 6 months ago by felixxm

Component: UtilitiesDatabase layer (models, ORM)
Summary: dbshell fails for Oracledbshell doesn't pass password properly on Oracle 18c.
Triage Stage: UnreviewedAccepted

Current solution works properly with sqlplus on Oracle 12c (also when password contains @ sign), but doesn't work on Oracle 18c. Moreover it looks that sqlplus still supports format with backslashes on Oracle 18c, e.g.:

sqlplus -L username/\"p@ssword\"@localhost:1521/some_database

I'm puzzled why it doesn't work with dbshell.

Removing backslashes is not a solution because it would create regression on Oracle 12c and 18c for password with special characters (leaving only quotation marks does nothing).

comment:2 Changed 6 months ago by Mark Gordon

Are you finding documentation about this anywhere?

Apologies if you were just being brief, but let's make sure we're talking about the same thing. If

sqlplus -L username/\"p@ssword\"@localhost:1521/some_database

is what you entered on the command line then yes it will work because the normal shell escaping mechanisms will convert the \" sequence to just a double quote when passed to the underlying sqlplus command. However, there is no such unescaping shell layer when you use the array form of subprocess.run, the arguments are passed literally meaning that there's going to be random backslashes in the password portion of the connect string.

e.g. if you ran

sqlplus -L 'username/\"p@ssword\"@localhost:1521/some_database'

which is the equivalant of what dbshell is doing then it should fail.

Additionally, there are no tests of this function in the codebase as far as I can tell. The PR that introduced this change did not add tests for the dbshell related functionality.

comment:3 Changed 6 months ago by Mark Gordon

Has patch: set

comment:4 Changed 6 months ago by felixxm

I'm really sure that previous solution worked properly with sqlplus for Oracle 11.2 and 12.1, it has been also confirmed also by Shane Allgeier who reported #29199.

There is a test to check new format in the (test_password_with_at_sign), we resigned from creating dynamically a custom Oracle user etc. to check this.

I double-checked and can confirmed that format without backslashes works for Oracle 12.2 and 18c so changes in django/db/backends/oracle/base.py looks good.

comment:5 Changed 6 months ago by felixxm

Patch needs improvement: set

comment:6 Changed 6 months ago by felixxm

Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

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

Resolution: fixed
Status: assignedclosed

In 755673e:

Fixed #30307 -- Fixed incorrect quoting of database user password when using dbshell on Oracle.

Regression in acfc650f2a6e4a79e80237eabfa923ea3a05d709.

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

In 1279fb4a:

[2.2.x] Fixed #30307 -- Fixed incorrect quoting of database user password when using dbshell on Oracle.

Regression in acfc650f2a6e4a79e80237eabfa923ea3a05d709.

Backport of 755673e1bca7edb6bee7a958f40d9ae54d85d44c from master

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