Ensure escaping of percent signs in columns, parameters

Improved support for column names that contain percent signs in the string,
including repaired issues involving anoymous labels that also embedded a
column name with a percent sign in it, as well as re-established support
for bound parameter names with percent signs embedded on the psycopg2
dialect, using a late-escaping process similar to that used by the
cx_Oracle dialect.

* Added new constructor for _anonymous_label() that ensures incoming
  string tokens based on column or table names will have percent
  signs escaped; abstracts away the format of the label.

* generalized cx_Oracle's quoted_bind_names facility into the compiler
  itself, and leveraged this for the psycopg2 dialect's issue with
  percent signs in names as well.  the parameter substitution is now
  integrated with compiler.construct_parameters() as well as the
  recently reworked set_input_sizes(), reducing verbosity in the
  cx_Oracle dialect.

Fixes: #5653

Change-Id: Ia2ad13ea68b4b0558d410026e5a33f5cb3fbab2c

3 files changed, 13 insertions, 24 deletions

diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 655614769..1f1f7501b 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -849,7 +849,6 @@ class OracleCompiler(compiler.SQLCompiler):
 
     def __init__(self, *args, **kwargs):
         self.__wheres = {}
-        self._quoted_bind_names = {}
         super(OracleCompiler, self).__init__(*args, **kwargs)
 
     def visit_mod_binary(self, binary, operator, **kw):
diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index 7bde19090..8eb9f8b3c 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -598,28 +598,20 @@ class OracleCompiler_cx_oracle(OracleCompiler):
             # need quoting :).    names that include illegal characters
             # won't work however.
             quoted_name = '"%s"' % name
-            self._quoted_bind_names[name] = quoted_name
-            return OracleCompiler.bindparam_string(self, quoted_name, **kw)
-        else:
-            return OracleCompiler.bindparam_string(self, name, **kw)
+            kw["escaped_from"] = name
+            name = quoted_name
+
+        return OracleCompiler.bindparam_string(self, name, **kw)
 
 
 class OracleExecutionContext_cx_oracle(OracleExecutionContext):
     out_parameters = None
 
-    def _setup_quoted_bind_names(self):
-        quoted_bind_names = self.compiled._quoted_bind_names
-        if quoted_bind_names:
-            for param in self.parameters:
-                for fromname, toname in quoted_bind_names.items():
-                    param[toname] = param[fromname]
-                    del param[fromname]
-
     def _generate_out_parameter_vars(self):
         # check for has_out_parameters or RETURNING, create cx_Oracle.var
         # objects if so
         if self.compiled.returning or self.compiled.has_out_parameters:
-            quoted_bind_names = self.compiled._quoted_bind_names
+            quoted_bind_names = self.compiled.escaped_bind_names
             for bindparam in self.compiled.binds.values():
                 if bindparam.isoutparam:
                     name = self.compiled.bind_names[bindparam]
@@ -684,9 +676,6 @@ class OracleExecutionContext_cx_oracle(OracleExecutionContext):
 
         self.out_parameters = {}
 
-        if self.compiled._quoted_bind_names:
-            self._setup_quoted_bind_names()
-
         self._generate_out_parameter_vars()
 
         self._generate_cursor_outputtype_handler()
@@ -1184,12 +1173,6 @@ class OracleDialect_cx_oracle(OracleDialect):
                 for key, dbtype, sqltype in list_of_tuples
                 if dbtype
             )
-            if context and context.compiled:
-                quoted_bind_names = context.compiled._quoted_bind_names
-                collection = (
-                    (quoted_bind_names.get(key, key), dbtype)
-                    for key, dbtype in collection
-                )
 
             if not self.supports_unicode_binds:
                 # oracle 8 only
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index 2446604ba..72c36b4a8 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -644,7 +644,14 @@ class PGExecutionContext_psycopg2(PGExecutionContext):
 
 
 class PGCompiler_psycopg2(PGCompiler):
-    pass
+    def bindparam_string(self, name, **kw):
+        if "%" in name and not kw.get("post_compile", False):
+            # psycopg2 will not allow a percent sign in a
+            # pyformat parameter name even if it is doubled
+            kw["escaped_from"] = name
+            name = name.replace("%", "P")
+
+        return PGCompiler.bindparam_string(self, name, **kw)
 
 
 class PGIdentifierPreparer_psycopg2(PGIdentifierPreparer):