Fix: native parameters failed if a query included the SQL LIKE wildcard

Jesse Whitehouse · Jesse Whitehouse · commit 91a86c07453b · 2023-11-15T18:01:00.000-05:00
Signed-off-by: Jesse Whitehouse &lt;jesse.whitehouse@databricks.com&gt;
diff --git a/docs/parameters.md b/docs/parameters.md
@@ -212,7 +212,7 @@ The result of the above two examples is identical.
 
 **Note**: `%s` is not compliant with PEP-249 and only works due to the specific implementation of our inline renderer. 
 
-**Note:** This `%s` syntax overlaps with valid SQL syntax around the usage of `LIKE` DML. For example if your query includes a clause like `WHERE field LIKE '%sequence'`, the parameter inlining function will raise an exception because this string appears to include an inline marker but none is provided. When `use_inline_params=False`, we will pass `%s` occurrences along to the database, allowing it to be used as expected in `LIKE` statements.
+**Note:** This `%s` syntax overlaps with valid SQL syntax around the usage of `LIKE` DML. For example if your query includes a clause like `WHERE field LIKE '%sequence'`, the parameter inlining function will raise an exception because this string appears to include an inline marker but none is provided. This means that connector versionss below 3.0.0 it has been impossible to execute a query that included both parameters and LIKE wildcards. When `use_inline_params=False`, we will pass `%s` occurrences along to the database, allowing it to be used as expected in `LIKE` statements.
 
 ### Passing sequences as parameter values
 
@@ -238,7 +238,7 @@ SELECT field FROM table WHERE field IN (1,2,3,4,5)
 
 ### Migrating to native parameters
 
-Native parameters are meant to be a drop-in replacement for inline parameters. In most use-cases, upgrading to `databricks-sql-connector>=3.0.0` will grant an immediate improvement to safety. And future improvements to parameterization (such as support for binding complex types like `STRUCT`, `MAP`, and `ARRAY`) will only be available when `use_inline_params=False`.
+Native parameters are meant to be a drop-in replacement for inline parameters. In most use-cases, upgrading to `databricks-sql-connector>=3.0.0` will grant an immediate improvement to safety. Plus, native parameters allow you to use SQL LIKE wildcards (`%`) in your queries which is impossible with inline parametrs. Future improvements to parameterization (such as support for binding complex types like `STRUCT`, `MAP`, and `ARRAY`) will only be available when `use_inline_params=False`.
 
 To completely migrate, you need to [revise your SQL queries](#legacy-pyformat-paramstyle-usage-example) to use the new paramstyles.
 
diff --git a/src/databricks/sql/utils.py b/src/databricks/sql/utils.py
@@ -7,7 +7,8 @@
 from collections.abc import Iterable
 from decimal import Decimal
 from enum import Enum
-from typing import Any, Dict, List, Union, TypeVar
+from typing import Any, Dict, List, Union
+import re
 
 import lz4.frame
 import pyarrow
@@ -429,6 +430,9 @@ def _interpolate_named_markers(
     except TypeError:
         # TypeError occurs if there are no %(param)s markers in the operation
         return operation
+    except ValueError:
+        # ValueError occurs if a query contains % signs that are not part of a %(param)s marker
+        return operation
 
 
 def transform_paramstyle(
diff --git a/tests/e2e/test_parameterized_queries.py b/tests/e2e/test_parameterized_queries.py
@@ -429,3 +429,24 @@ def test_native_ordinals_dont_break_sql(self):
 
         assert result.samsonite == "samsonite"
         assert result.luggage == "luggage"
+
+    def test_inline_like_wildcard_breaks(self):
+        """One flaw with the ParameterEscaper is that it fails if a query contains
+        a SQL LIKE wildcard %. This test proves that's the case.
+        """
+        query = "SELECT 1 `col` WHERE 'foo' LIKE '%'"
+        params ={"param": 'bar'}
+        with self.cursor(extra_params={"use_inline_params": True}) as cursor:
+            with pytest.raises(ValueError, match="unsupported format character"):
+                result = cursor.execute(query, parameters=params).fetchone()
+
+    def test_native_like_wildcard_works(self):
+        """This is a mirror of test_inline_like_wildcard_breaks that proves that LIKE 
+        wildcards work under the native approach.
+        """
+        query = "SELECT 1 `col` WHERE 'foo' LIKE '%'"
+        params ={"param": 'bar'}
+        with self.cursor(extra_params={"use_inline_params": False}) as cursor:
+            result = cursor.execute(query, parameters=params).fetchone()
+        
+        assert result.col == 1
diff --git a/tests/unit/test_param_escaper.py b/tests/unit/test_param_escaper.py
@@ -196,6 +196,18 @@ class TestInlineToNativeTransformer(object):
                 {"param": None, "list": None},
                 "SELECT * FROM table WHERE field = :param AND other_field IN (:list)",
             ),
+            (
+                "query with like wildcard",
+                'select * from table where field like "%"',
+                {},
+                'select * from table where field like "%"'
+            ),
+            (
+                "query with named param and like wildcard",
+                'select :param from table where field like "%"',
+                {"param": None},
+                'select :param from table where field like "%"'
+            )
         ),
     )
     def test_transformer(
