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, 21 insertions, 11 deletions

diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index a5dee068c..ac08604f5 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -31,11 +31,13 @@ from .elements import _clause_element_as_expr
 from .elements import _clone
 from .elements import _cloned_difference
 from .elements import _cloned_intersection
+from .elements import _document_text_coercion
 from .elements import _expand_cloned
 from .elements import _interpret_as_column_or_from
 from .elements import _literal_and_labels_as_label_reference
 from .elements import _literal_as_label_reference
 from .elements import _literal_as_text
+from .elements import _no_text_coercion
 from .elements import _select_iterables
 from .elements import and_
 from .elements import BindParameter
@@ -43,7 +45,6 @@ from .elements import ClauseElement
 from .elements import ClauseList
 from .elements import Grouping
 from .elements import literal_column
-from .elements import TextClause
 from .elements import True_
 from .elements import UnaryExpression
 from .. import exc
@@ -55,14 +56,7 @@ def _interpret_as_from(element):
     insp = inspection.inspect(element, raiseerr=False)
     if insp is None:
         if isinstance(element, util.string_types):
-            util.warn_limited(
-                "Textual SQL FROM expression %(expr)r should be "
-                "explicitly declared as text(%(expr)r), "
-                "or use table(%(expr)r) for more specificity",
-                {"expr": util.ellipses_string(element)},
-            )
-
-            return TextClause(util.text_type(element))
+            _no_text_coercion(element)
     try:
         return insp.selectable
     except AttributeError:
@@ -266,6 +260,11 @@ class HasPrefixes(object):
     _prefixes = ()
 
     @_generative
+    @_document_text_coercion(
+        "expr",
+        ":meth:`.HasPrefixes.prefix_with`",
+        ":paramref:`.HasPrefixes.prefix_with.*expr`",
+    )
     def prefix_with(self, *expr, **kw):
         r"""Add one or more expressions following the statement keyword, i.e.
         SELECT, INSERT, UPDATE, or DELETE. Generative.
@@ -297,7 +296,10 @@ class HasPrefixes(object):
 
     def _setup_prefixes(self, prefixes, dialect=None):
         self._prefixes = self._prefixes + tuple(
-            [(_literal_as_text(p, warn=False), dialect) for p in prefixes]
+            [
+                (_literal_as_text(p, allow_coercion_to_text=True), dialect)
+                for p in prefixes
+            ]
         )
 
 
@@ -305,6 +307,11 @@ class HasSuffixes(object):
     _suffixes = ()
 
     @_generative
+    @_document_text_coercion(
+        "expr",
+        ":meth:`.HasSuffixes.suffix_with`",
+        ":paramref:`.HasSuffixes.suffix_with.*expr`",
+    )
     def suffix_with(self, *expr, **kw):
         r"""Add one or more expressions following the statement as a whole.
 
@@ -335,7 +342,10 @@ class HasSuffixes(object):
 
     def _setup_suffixes(self, suffixes, dialect=None):
         self._suffixes = self._suffixes + tuple(
-            [(_literal_as_text(p, warn=False), dialect) for p in suffixes]
+            [
+                (_literal_as_text(p, allow_coercion_to_text=True), dialect)
+                for p in suffixes
+            ]
         )