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, 22 insertions, 59 deletions

diff --git a/test/sql/test_text.py b/test/sql/test_text.py
index 48302058d..bcaf905fe 100644
--- a/test/sql/test_text.py
+++ b/test/sql/test_text.py
@@ -22,10 +22,8 @@ from sqlalchemy.sql import column
 from sqlalchemy.sql import table
 from sqlalchemy.sql import util as sql_util
 from sqlalchemy.testing import assert_raises_message
-from sqlalchemy.testing import assert_warnings
 from sqlalchemy.testing import AssertsCompiledSQL
 from sqlalchemy.testing import eq_
-from sqlalchemy.testing import expect_warnings
 from sqlalchemy.testing import fixtures
 from sqlalchemy.types import NullType
 
@@ -574,16 +572,12 @@ class AsFromTest(fixtures.TestBase, AssertsCompiledSQL):
         eq_(set(t.element._bindparams), set(["bat", "foo", "bar"]))
 
 
-class TextWarningsTest(fixtures.TestBase, AssertsCompiledSQL):
+class TextErrorsTest(fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = "default"
 
-    def _test(self, fn, arg, offending_clause, expected):
-        with expect_warnings("Textual "):
-            stmt = fn(arg)
-            self.assert_compile(stmt, expected)
-
+    def _test(self, fn, arg, offending_clause):
         assert_raises_message(
-            exc.SAWarning,
+            exc.ArgumentError,
             r"Textual (?:SQL|column|SQL FROM) expression %(stmt)r should be "
             r"explicitly declared (?:with|as) text\(%(stmt)r\)"
             % {"stmt": util.ellipses_string(offending_clause)},
@@ -592,45 +586,28 @@ class TextWarningsTest(fixtures.TestBase, AssertsCompiledSQL):
         )
 
     def test_where(self):
-        self._test(
-            select([table1.c.myid]).where,
-            "myid == 5",
-            "myid == 5",
-            "SELECT mytable.myid FROM mytable WHERE myid == 5",
-        )
+        self._test(select([table1.c.myid]).where, "myid == 5", "myid == 5")
 
     def test_column(self):
-        self._test(select, ["myid"], "myid", "SELECT myid")
+        self._test(select, ["myid"], "myid")
 
     def test_having(self):
-        self._test(
-            select([table1.c.myid]).having,
-            "myid == 5",
-            "myid == 5",
-            "SELECT mytable.myid FROM mytable HAVING myid == 5",
-        )
+        self._test(select([table1.c.myid]).having, "myid == 5", "myid == 5")
 
     def test_from(self):
-        self._test(
-            select([table1.c.myid]).select_from,
-            "mytable",
-            "mytable",
-            "SELECT mytable.myid FROM mytable, mytable",  # two FROMs
-        )
+        self._test(select([table1.c.myid]).select_from, "mytable", "mytable")
 
 
 class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = "default"
 
-    def _test_warning(self, stmt, offending_clause, expected):
-        with expect_warnings(
-            "Can't resolve label reference %r;" % offending_clause
-        ):
-            self.assert_compile(stmt, expected)
+    def _test_exception(self, stmt, offending_clause):
         assert_raises_message(
-            exc.SAWarning,
-            "Can't resolve label reference %r; converting to text"
-            % offending_clause,
+            exc.CompileError,
+            r"Can't resolve label reference for ORDER BY / GROUP BY. "
+            "Textual SQL "
+            "expression %r should be explicitly "
+            r"declared as text\(%r\)" % (offending_clause, offending_clause),
             stmt.compile,
         )
 
@@ -680,9 +657,7 @@ class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_unresolvable_warning_order_by(self):
         stmt = select([table1.c.myid]).order_by("foobar")
-        self._test_warning(
-            stmt, "foobar", "SELECT mytable.myid FROM mytable ORDER BY foobar"
-        )
+        self._test_exception(stmt, "foobar")
 
     def test_group_by_label(self):
         stmt = select([table1.c.myid.label("foo")]).group_by("foo")
@@ -698,9 +673,7 @@ class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_unresolvable_warning_group_by(self):
         stmt = select([table1.c.myid]).group_by("foobar")
-        self._test_warning(
-            stmt, "foobar", "SELECT mytable.myid FROM mytable GROUP BY foobar"
-        )
+        self._test_exception(stmt, "foobar")
 
     def test_asc(self):
         stmt = select([table1.c.myid]).order_by(asc("name"), "description")
@@ -799,23 +772,13 @@ class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL):
             .order_by("myid", "t1name", "x")
         )
 
-        def go():
-            # the labels here are anonymized, so label naming
-            # can't catch these.
-            self.assert_compile(
-                s1,
-                "SELECT mytable_1.myid AS mytable_1_myid, "
-                "mytable_1.name AS name_1, foo(:foo_2) AS foo_1 "
-                "FROM mytable AS mytable_1 ORDER BY mytable_1.myid, t1name, x",
-            )
-
-        assert_warnings(
-            go,
-            [
-                "Can't resolve label reference 't1name'",
-                "Can't resolve label reference 'x'",
-            ],
-            regex=True,
+        assert_raises_message(
+            exc.CompileError,
+            r"Can't resolve label reference for ORDER BY / GROUP BY. "
+            "Textual SQL "
+            "expression 't1name' should be explicitly "
+            r"declared as text\('t1name'\)",
+            s1.compile,
         )
 
     def test_columnadapter_non_anonymized(self):