Remove all remaining text() coercions and ensure identifiers are safe

Fully removed the behavior of strings passed directly as components of a
:func:`.select` or :class:`.Query` object being coerced to :func:`.text`
constructs automatically; the warning that has been emitted is now an
ArgumentError or in the case of order_by() / group_by() a CompileError.
This has emitted a warning since version 1.0 however its presence continues
to create concerns for the potential of mis-use of this behavior.

Note that public CVEs have been posted for order_by() / group_by() which
are resolved by this commit:  CVE-2019-7164  CVE-2019-7548

Added "SQL phrase validation" to key DDL phrases that are accepted as plain
strings, including :paramref:`.ForeignKeyConstraint.on_delete`,
:paramref:`.ForeignKeyConstraint.on_update`,
:paramref:`.ExcludeConstraint.using`,
:paramref:`.ForeignKeyConstraint.initially`, for areas where a series of SQL
keywords only are expected.Any non-space characters that suggest the phrase
would need to be quoted will raise a :class:`.CompileError`.   This change
is related to the series of changes committed as part of :ticket:`4481`.

Fixed issue where using an uppercase name for an index type (e.g. GIST,
BTREE, etc. ) or an EXCLUDE constraint would treat it as an identifier to
be quoted, rather than rendering it as is. The new behavior converts these
types to lowercase and ensures they contain only valid SQL characters.

Quoting is applied to :class:`.Function` names, those which are usually but
not necessarily generated from the :attr:`.sql.func` construct,  at compile
time if they contain illegal characters, such as spaces or punctuation. The
names are as before treated as case insensitive however, meaning if the
names contain uppercase or mixed case characters, that alone does not
trigger quoting. The case insensitivity is currently maintained for
backwards compatibility.

Fixes: #4481
Fixes: #4473
Fixes: #4467
Change-Id: Ib22a27d62930e24702e2f0f7c74a0473385a08eb

2 files changed, 26 insertions, 23 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 4004a2b9a..4d302dabe 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -948,6 +948,8 @@ except ImportError:
     _python_UUID = None
 
 
+IDX_USING = re.compile(r"^(?:btree|hash|gist|gin|[\w_]+)$", re.I)
+
 AUTOCOMMIT_REGEXP = re.compile(
     r"\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER|GRANT|REVOKE|"
     "IMPORT FOREIGN SCHEMA|REFRESH MATERIALIZED VIEW|TRUNCATE)",
@@ -1908,7 +1910,10 @@ class PGDDLCompiler(compiler.DDLCompiler):
 
         using = index.dialect_options["postgresql"]["using"]
         if using:
-            text += "USING %s " % preparer.quote(using)
+            text += (
+                "USING %s "
+                % self.preparer.validate_sql_phrase(using, IDX_USING).lower()
+            )
 
         ops = index.dialect_options["postgresql"]["ops"]
         text += "(%s)" % (
@@ -1983,7 +1988,9 @@ class PGDDLCompiler(compiler.DDLCompiler):
                 "%s WITH %s" % (self.sql_compiler.process(expr, **kw), op)
             )
         text += "EXCLUDE USING %s (%s)" % (
-            constraint.using,
+            self.preparer.validate_sql_phrase(
+                constraint.using, IDX_USING
+            ).lower(),
             ", ".join(elements),
         )
         if constraint.where is not None:
diff --git a/lib/sqlalchemy/dialects/postgresql/ext.py b/lib/sqlalchemy/dialects/postgresql/ext.py
index 49b5e0ec0..426028239 100644
--- a/lib/sqlalchemy/dialects/postgresql/ext.py
+++ b/lib/sqlalchemy/dialects/postgresql/ext.py
@@ -91,6 +91,11 @@ class ExcludeConstraint(ColumnCollectionConstraint):
 
     where = None
 
+    @elements._document_text_coercion(
+        "where",
+        ":class:`.ExcludeConstraint`",
+        ":paramref:`.ExcludeConstraint.where`",
+    )
     def __init__(self, *elements, **kw):
         r"""
         Create an :class:`.ExcludeConstraint` object.
@@ -123,21 +128,15 @@ class ExcludeConstraint(ColumnCollectionConstraint):
             )
 
         :param \*elements:
+
           A sequence of two tuples of the form ``(column, operator)`` where
           "column" is a SQL expression element or a raw SQL string, most
-          typically a :class:`.Column` object,
-          and "operator" is a string containing the operator to use.
-
-          .. note::
-
-                A plain string passed for the value of "column" is interpreted
-                as an arbitrary SQL  expression; when passing a plain string,
-                any necessary quoting and escaping syntaxes must be applied
-                manually. In order to specify a column name when a
-                :class:`.Column` object is not available, while ensuring that
-                any necessary quoting rules take effect, an ad-hoc
-                :class:`.Column` or :func:`.sql.expression.column` object may
-                be used.
+          typically a :class:`.Column` object, and "operator" is a string
+          containing the operator to use.   In order to specify a column name
+          when a  :class:`.Column` object is not available, while ensuring
+          that any necessary quoting rules take effect, an ad-hoc
+          :class:`.Column` or :func:`.sql.expression.column` object should be
+          used.
 
         :param name:
           Optional, the in-database name of this constraint.
@@ -159,12 +158,6 @@ class ExcludeConstraint(ColumnCollectionConstraint):
           If set, emit WHERE <predicate> when issuing DDL
           for this constraint.
 
-          .. note::
-
-                A plain string passed here is interpreted as an arbitrary SQL
-                expression; when passing a plain string, any necessary quoting
-                and escaping syntaxes must be applied manually.
-
         """
         columns = []
         render_exprs = []
@@ -184,11 +177,12 @@ class ExcludeConstraint(ColumnCollectionConstraint):
                 # backwards compat
                 self.operators[name] = operator
 
-            expr = expression._literal_as_text(expr)
+            expr = expression._literal_as_column(expr)
 
             render_exprs.append((expr, name, operator))
 
         self._render_exprs = render_exprs
+
         ColumnCollectionConstraint.__init__(
             self,
             *columns,
@@ -199,7 +193,9 @@ class ExcludeConstraint(ColumnCollectionConstraint):
         self.using = kw.get("using", "gist")
         where = kw.get("where")
         if where is not None:
-            self.where = expression._literal_as_text(where)
+            self.where = expression._literal_as_text(
+                where, allow_coercion_to_text=True
+            )
 
     def copy(self, **kw):
         elements = [(col, self.operators[col]) for col in self.columns.keys()]