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

4 files changed, 26 insertions, 8 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 8c8260f3b..cefaad41d 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -2615,6 +2615,9 @@ def _schema_elements(schema):
     # test/dialect/mssql/test_compiler.py -> test_schema_many_tokens_*
     #
 
+    if schema.startswith("__[SCHEMA_"):
+        return None, schema
+
     push = []
     symbol = ""
     bracket = False
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.
diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index 6fbd74689..ba176bcd9 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -2,7 +2,6 @@ import operator
 import re
 
 import sqlalchemy as sa
-from sqlalchemy import func
 from .. import config
 from .. import engines
 from .. import eq_
@@ -15,6 +14,7 @@ from ..schema import Column
 from ..schema import Table
 from ... import event
 from ... import ForeignKey
+from ... import func
 from ... import Identity
 from ... import inspect
 from ... import Integer
@@ -25,6 +25,7 @@ from ... import types as sql_types
 from ...schema import DDL
 from ...schema import Index
 from ...sql.elements import quoted_name
+from ...sql.schema import BLANK_SCHEMA
 from ...testing import is_false
 from ...testing import is_true
 
@@ -539,6 +540,20 @@ class ComponentReflectionTest(fixtures.TablesTest):
         self.assert_(testing.config.test_schema in insp.get_schema_names())
 
     @testing.requires.schema_reflection
+    def test_get_schema_names_w_translate_map(self, connection):
+        """test #7300"""
+
+        connection = connection.execution_options(
+            schema_translate_map={
+                "foo": "bar",
+                BLANK_SCHEMA: testing.config.test_schema,
+            }
+        )
+        insp = inspect(connection)
+
+        self.assert_(testing.config.test_schema in insp.get_schema_names())
+
+    @testing.requires.schema_reflection
     def test_dialect_initialize(self):
         engine = engines.testing_engine()
         inspect(engine)
diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py
index bea8a6075..b5a3dca3a 100644
--- a/lib/sqlalchemy/testing/suite/test_select.py
+++ b/lib/sqlalchemy/testing/suite/test_select.py
@@ -884,7 +884,7 @@ class PostCompileParamsTest(
         self.assert_compile(
             stmt,
             "SELECT some_table.id FROM some_table "
-            "WHERE some_table.x = [POSTCOMPILE_q]",
+            "WHERE some_table.x = __[POSTCOMPILE_q]",
             {},
         )