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

1 files changed, 59 insertions, 28 deletions

diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 9e4f5d95d..a4623128f 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -37,6 +37,20 @@ def _clone(element, **kw):
     return element._clone()
 
 
+def _document_text_coercion(paramname, meth_rst, param_rst):
+    return util.add_parameter_text(
+        paramname,
+        (
+            ".. warning:: "
+            "The %s argument to %s can be passed as a Python string argument, "
+            "which will be treated "
+            "as **trusted SQL text** and rendered as given.  **DO NOT PASS "
+            "UNTRUSTED INPUT TO THIS PARAMETER**."
+        )
+        % (param_rst, meth_rst),
+    )
+
+
 def collate(expression, collation):
     """Return the clause ``expression COLLATE collation``.
 
@@ -1343,6 +1357,7 @@ class TextClause(Executable, ClauseElement):
             "refer to the :meth:`.TextClause.columns` method.",
         ),
     )
+    @_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
     def _create_text(
         self, text, bind=None, bindparams=None, typemap=None, autocommit=None
     ):
@@ -4430,32 +4445,64 @@ def _literal_and_labels_as_label_reference(element):
 
 
 def _expression_literal_as_text(element):
-    return _literal_as_text(element, warn=True)
+    return _literal_as_text(element)
 
 
-def _literal_as_text(element, warn=False):
+def _literal_as(element, text_fallback):
     if isinstance(element, Visitable):
         return element
     elif hasattr(element, "__clause_element__"):
         return element.__clause_element__()
     elif isinstance(element, util.string_types):
-        if warn:
-            util.warn_limited(
-                "Textual SQL expression %(expr)r should be "
-                "explicitly declared as text(%(expr)r)",
-                {"expr": util.ellipses_string(element)},
-            )
-
-        return TextClause(util.text_type(element))
+        return text_fallback(element)
     elif isinstance(element, (util.NoneType, bool)):
         return _const_expr(element)
     else:
         raise exc.ArgumentError(
-            "SQL expression object or string expected, got object of type %r "
+            "SQL expression object expected, got object of type %r "
             "instead" % type(element)
         )
 
 
+def _literal_as_text(element, allow_coercion_to_text=False):
+    if allow_coercion_to_text:
+        return _literal_as(element, TextClause)
+    else:
+        return _literal_as(element, _no_text_coercion)
+
+
+def _literal_as_column(element):
+    return _literal_as(element, ColumnClause)
+
+
+def _no_column_coercion(element):
+    element = str(element)
+    guess_is_literal = not _guess_straight_column.match(element)
+    raise exc.ArgumentError(
+        "Textual column expression %(column)r should be "
+        "explicitly declared with text(%(column)r), "
+        "or use %(literal_column)s(%(column)r) "
+        "for more specificity"
+        % {
+            "column": util.ellipses_string(element),
+            "literal_column": "literal_column"
+            if guess_is_literal
+            else "column",
+        }
+    )
+
+
+def _no_text_coercion(element, exc_cls=exc.ArgumentError, extra=None):
+    raise exc_cls(
+        "%(extra)sTextual SQL expression %(expr)r should be "
+        "explicitly declared as text(%(expr)r)"
+        % {
+            "expr": util.ellipses_string(element),
+            "extra": "%s " % extra if extra else "",
+        }
+    )
+
+
 def _no_literals(element):
     if hasattr(element, "__clause_element__"):
         return element.__clause_element__()
@@ -4529,23 +4576,7 @@ def _interpret_as_column_or_from(element):
     elif isinstance(element, (numbers.Number)):
         return ColumnClause(str(element), is_literal=True)
     else:
-        element = str(element)
-        # give into temptation, as this fact we are guessing about
-        # is not one we've previously ever needed our users tell us;
-        # but let them know we are not happy about it
-        guess_is_literal = not _guess_straight_column.match(element)
-        util.warn_limited(
-            "Textual column expression %(column)r should be "
-            "explicitly declared with text(%(column)r), "
-            "or use %(literal_column)s(%(column)r) "
-            "for more specificity",
-            {
-                "column": util.ellipses_string(element),
-                "literal_column": "literal_column"
-                if guess_is_literal
-                else "column",
-            },
-        )
+        _no_column_coercion(element)
     return ColumnClause(element, is_literal=guess_is_literal)