Why Oracle doesn't escape double backslashes to single?

Question

I pass the SQL query from a python server to Oracle with double backslashes:

SELECT
   ID
FROM
   Works
WHERE
   NAME IN ('ABC\\ABC', 'sdf\\sdfsdf', 'lkj\\sdfsdf')

I got nothing after the query was executed. But when remove one backslash and execute in DBeaver (SQL Tool), result is OK.

NAME fields in the table have one backslash and look like this: ABC\ABC.

`'ABC\\ABC'` - this is the literal value, period. Oracle will not do anything with it. You need to prepare your value **before** sending it to oracle. phyton has `[string].replace("\\", "\")`. use it. — T.S., Sep 22 '21 at 14:35
See [Binding Multiple Values to a SQL WHERE IN Clause](https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html#binding-multiple-values-to-a-sql-where-in-clause). — Christopher Jones, Sep 22 '21 at 23:00
@T.S. python always stores double backslashes in a variable to escape it when print, etc. So `replace` and encode/decode don't work in this situation. — Shalamnik, Sep 23 '21 at 11:54
check this https://stackoverflow.com/questions/14820429/how-do-i-decodestring-escape-in-python3 — T.S., Sep 23 '21 at 14:25

score 0 · Answer 1 · answered Sep 23 '21 at 00:21

The backslash is not a special character inside an Oracle string literal, therefore, you do not need to escape it with another backslash.

One thing you do need to escape is the single quote mark by doubling it up (assuming you're not using the "q" syntax), so you'd have a string like 'Susan''s'

score 0 · Accepted Answer · edited Oct 17 '21 at 13:35

There are 2 solutions for my problem:

Bind values in a SQL query in Python:

https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html#binding-multiple-values-to-a-sql-where-in-clause;

Thanks to Christopher Jones for this solution of binding the values in the execute command. It works well with double backslashes.
```
 cursor.execute("""
     select employee_id, first_name, last_name
     from employees
     where last_name in (:name1, :name2)""",
     name1="Smith", name2="Taylor")
```
Play with decode/encode: how do I .decode('string-escape') in Python3?.

Why Oracle doesn't escape double backslashes to single?

2 Answers2