change the POSTCOMPILE/ SCHEMA symbols to not conflict w mssql quoting

Adjusted the compiler's generation of "post compile" symbols including
those used for "expanding IN" as well as for the "schema translate map" to
not be based directly on plain bracketed strings with underscores, as this
conflicts directly with SQL Server's quoting format of also using brackets,
which produces false matches when the compiler replaces "post compile" and
"schema translate" symbols. The issue created easy to reproduce examples
both with the :meth:`.Inspector.get_schema_names` method when used in
conjunction with the
:paramref:`_engine.Connection.execution_options.schema_translate_map`
feature, as well in the unlikely case that a symbol overlapping with the
internal name "POSTCOMPILE" would be used with a feature like "expanding
in".

Fixes: #7300
Change-Id: I6255c850b140522a4aba95085216d0bca18ce230

1 files changed, 6 insertions, 6 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 4611c5e13..8ea35099d 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1247,7 +1247,7 @@ class SQLCompiler(Compiled):
             return expr
 
         statement = re.sub(
-            r"\[POSTCOMPILE_(\S+?)(~~.+?~~)?\]",
+            r"__\[POSTCOMPILE_(\S+?)(~~.+?~~)?\]",
             process_expanding,
             self.string,
         )
@@ -2374,9 +2374,9 @@ class SQLCompiler(Compiled):
                     # for postcompile w/ expanding, move the "wrapped" part
                     # of this into the inside
                     m = re.match(
-                        r"^(.*)\(\[POSTCOMPILE_(\S+?)\]\)(.*)$", wrapped
+                        r"^(.*)\(__\[POSTCOMPILE_(\S+?)\]\)(.*)$", wrapped
                     )
-                    wrapped = "([POSTCOMPILE_%s~~%s~~REPL~~%s~~])" % (
+                    wrapped = "(__[POSTCOMPILE_%s~~%s~~REPL~~%s~~])" % (
                         m.group(2),
                         m.group(1),
                         m.group(3),
@@ -2582,7 +2582,7 @@ class SQLCompiler(Compiled):
                 self.escaped_bind_names = {}
             self.escaped_bind_names[escaped_from] = name
         if post_compile:
-            return "[POSTCOMPILE_%s]" % name
+            return "__[POSTCOMPILE_%s]" % name
         else:
             return self.bindtemplate % {"name": name}
 
@@ -5039,7 +5039,7 @@ class IdentifierPreparer(object):
                         "in schema translate name '%s'" % name
                     )
                 return quoted_name(
-                    "[SCHEMA_%s]" % (name or "_none"), quote=False
+                    "__[SCHEMA_%s]" % (name or "_none"), quote=False
                 )
             else:
                 return obj.schema
@@ -5065,7 +5065,7 @@ class IdentifierPreparer(object):
                     )
             return self.quote_schema(effective_schema)
 
-        return re.sub(r"(\[SCHEMA_([^\]]+)\])", replace, statement)
+        return re.sub(r"(__\[SCHEMA_([^\]]+)\])", replace, statement)
 
     def _escape_identifier(self, value):
         """Escape an identifier.