add Oracle-specific parameter escapes for expanding params

Fixed issue where bound parameter names, including those automatically
derived from similarly-named database columns, which contained characters
that normally require quoting with Oracle would not be escaped when using
"expanding parameters" with the Oracle dialect, causing execution errors.
The usual "quoting" for bound parameters used by the Oracle dialect is not
used with the "expanding parameters" architecture, so escaping for a large
range of characters is used instead, now using a list of characters/escapes
that are specific to Oracle.

Fixes: #8708
Change-Id: I90c24e48534e1b3a4c222b3022da58159784d91a

1 files changed, 36 insertions, 0 deletions

diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index 4571f51f7..24262c181 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -445,6 +445,15 @@ from ...sql._typing import is_sql_compiler
 _CX_ORACLE_MAGIC_LOB_SIZE = 131072
 
 
+_ORACLE_BIND_TRANSLATE_RE = re.compile(r"[%\(\):\[\]\.\/\?]")
+
+# Oracle bind names can't start with digits or underscores.
+# currently we rely upon Oracle-specific quoting of bind names in most cases.
+# however for expanding params, the escape chars are used.
+# see #8708
+_ORACLE_BIND_TRANSLATE_CHARS = dict(zip("%():[]./?", "PAZCCCCCCC"))
+
+
 class _OracleInteger(sqltypes.Integer):
     def get_dbapi_type(self, dbapi):
         # see https://github.com/oracle/python-cx_Oracle/issues/
@@ -693,6 +702,10 @@ class OracleCompiler_cx_oracle(OracleCompiler):
             quote is True
             or quote is not False
             and self.preparer._bindparam_requires_quotes(name)
+            # bind param quoting for Oracle doesn't work with post_compile
+            # params.  For those, the default bindparam_string will escape
+            # special chars, and the appending of a number "_1" etc. will
+            # take care of reserved words
             and not kw.get("post_compile", False)
         ):
             # interesting to note about expanding parameters - since the
@@ -703,6 +716,29 @@ class OracleCompiler_cx_oracle(OracleCompiler):
             quoted_name = '"%s"' % name
             kw["escaped_from"] = name
             name = quoted_name
+            return OracleCompiler.bindparam_string(self, name, **kw)
+
+        # TODO: we could likely do away with quoting altogether for
+        # Oracle parameters and use the custom escaping here
+        escaped_from = kw.get("escaped_from", None)
+        if not escaped_from:
+
+            if _ORACLE_BIND_TRANSLATE_RE.search(name):
+                # not quite the translate use case as we want to
+                # also get a quick boolean if we even found
+                # unusual characters in the name
+                new_name = _ORACLE_BIND_TRANSLATE_RE.sub(
+                    lambda m: _ORACLE_BIND_TRANSLATE_CHARS[m.group(0)],
+                    name,
+                )
+                if new_name[0].isdigit():
+                    new_name = "D" + new_name
+                kw["escaped_from"] = name
+                name = new_name
+            elif name[0].isdigit():
+                new_name = "D" + name
+                kw["escaped_from"] = name
+                name = new_name
 
         return OracleCompiler.bindparam_string(self, name, **kw)