oracledb.connect() method has encountered an issue with user argument containing special characters

[Lidoca]

1. What versions are you using?

platform.platform: Linux-5.15.0-70-generic-x86_64-with-glibc2.2.5
sys.maxsize > 2**32: True
platform.python_version: 3.8.7

oracledb.__version__: 1.3.1

2. Is it an error or a hang or a crash?

error

3. What error(s) or behavior you are seeing?

When running the program in the official document(but with Thick mode) the following error occurs:

Traceback (most recent call last):
  File "test.py", line 12, in <module>
    with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
  File "/usr/local/lib/python3.8/site-packages/oracledb/connection.py", line 1008, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/oracledb/connection.py", line 133, in __init__
    impl.connect(params_impl, pool_impl)
  File "src/oracledb/impl/thick/connection.pyx", line 387, in oracledb.thick_impl.ThickConnImpl.connect
  File "src/oracledb/impl/thick/utils.pyx", line 413, in oracledb.thick_impl._raise_from_odpi
  File "src/oracledb/impl/thick/utils.pyx", line 403, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: ORA-01017: invalid username/password; logon denied

The PYTHON_USERNAME contains special characters(like "C##test-account").

This issue can be circumvented by wrapping the string passed to user in double quotes.

4. Does your application call init_oracle_client()?

yes

5. Include a runnable Python script that shows the problem.

# test.py

import oracledb
import os

un = os.environ.get('PYTHON_USERNAME')
pw = os.environ.get('PYTHON_PASSWORD')
cs = os.environ.get('PYTHON_CONNECTSTRING')

oracledb.init_oracle_client()

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        sql = """select sysdate from dual"""
        for r in cursor.execute(sql):
            print(r)

[cjbj]

Thanks for the report. What did you mean "This issue can be circumvented by wrapping the string passed to user in double quotes."?

[Lidoca]

Thanks for the report. What did you mean "This issue can be circumvented by wrapping the string passed to user in double quotes."?

In the oracledb.connect method, instead of directly passing un to the user, there is no problem if you wrap it with double quotes like f'"{un}"'.

# test.py

import oracledb
import os

un = os.environ.get("PYTHON_USERNAME")
pw = os.environ.get("PYTHON_PASSWORD")
cs = os.environ.get("PYTHON_CONNECTSTRING")

oracledb.init_oracle_client()

with oracledb.connect(user=f'"{un}"', password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        sql = """select sysdate from dual"""
        for r in cursor.execute(sql):
            print(r)

[cjbj]

We'll take a look

[anthony-tuininga]

How was the user created? I just did this in SQL*Plus:

create user c##test identified by dev;

Then this code worked just fine:

conn = oracledb.connect(user="c##test", pssword="dev", dsn=cs)

Are you able to connect with SQL*Plus?

[anthony-tuininga]

Ah, I tried with the "-" in the username. In that case you have to enclose it in double quotes since that is not a valid identifier. So that is expected behavior and not a bug!

[Lidoca]

Yes, for the testing, I tried to create random user names based on "/proc/sys/kernel/random/uuid" which generates strings containing '-'. I should have provided more detailed context.

I personally think the behaviour is not very informative, since the argument is plain str. While I can presume use of '-' for username is not that good practice, but Oracle Database does not restrict or warn about this.

Is there any better way to state about this problem?

[anthony-tuininga]

The requirements for the user and password are documented here. Specifically, both of these (along with table names, column names, etc.) must follow these rules. We can add links to this documentation in the documentation for python-oracledb if @cjbj thinks it is worthwhile doing. Generally, the assumption is that you are familiar with the rules surrounding the Oracle Database!

[cjbj]

@anthony-tuininga can you internally re-quote all usernames "just in case" they have a special char?

If there's an obvious place we can update doc quickly, otherwise we'll add it to the very long todo list! Ping @priyankanair8

[anthony-tuininga]

@anthony-tuininga can you internally re-quote all usernames "just in case" they have a special char?

Theoretically that could be done. There is an obvious performance implication since we would have to check if a quote is already in place and if not, allocate memory and copy the string. I'm not sure if there are any other implications (functionally) but we can ask internally about that.

[cjbj]

I'll add the code tweak to the future todo list

[Lidoca]

Thanks for the links. I had a hard time to get updated official documents.