(groovy) branch master updated: GROOVY-12350: Handle ?? escapes in SQL InListExpander

[pa...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

paulk-asert pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/groovy.git


The following commit(s) were added to refs/heads/master by this push:
     new 8d6d4d7086 GROOVY-12350: Handle ?? escapes in SQL InListExpander
8d6d4d7086 is described below

commit 8d6d4d7086e190e94ad654685e318d9f14780be4
Author: Paul King <pa...@asert.com.au>
AuthorDate: Fri Sep 4 14:58:25 2026 +1000

    GROOVY-12350: Handle ?? escapes in SQL InListExpander
---
 .../src/main/java/groovy/sql/InListExpander.java   | 13 +++++-
 .../groovy/groovy/sql/InListExpanderTest.groovy    | 50 ++++++++++++++++++++++
 2 files changed, 62 insertions(+), 1 deletion(-)

diff --git a/subprojects/groovy-sql/src/main/java/groovy/sql/InListExpander.java b/subprojects/groovy-sql/src/main/java/groovy/sql/InListExpander.java
index 221dda149a..0753175d7a 100644
--- a/subprojects/groovy-sql/src/main/java/groovy/sql/InListExpander.java
+++ b/subprojects/groovy-sql/src/main/java/groovy/sql/InListExpander.java
@@ -31,7 +31,8 @@ import java.util.List;
  * <p>
  * The SQL is walked character-by-character so that {@code ?} characters
  * appearing inside single-quoted strings, double-quoted identifiers, line
- * comments, and block comments are ignored.
+ * comments, block comments, and a doubled {@code ??} operator escape are
+ * ignored.
  * <p>
  * Package-private; consumed by {@link Sql} prior to {@code prepareStatement}.
  */
@@ -82,6 +83,16 @@ final class InListExpander {
                     }
                     break;
                 case '?':
+                    // A doubled '?' is how a driver escapes a literal '?' operator — PostgreSQL's
+                    // json/hstore operators ? ?| ?& collide with the placeholder, so pgjdbc reads
+                    // '??' as one of them and binds nothing to it. Two adjacent placeholders are
+                    // not valid SQL in any case, so there is nothing else this can mean. Passing it
+                    // through untouched leaves the driver to unescape it.
+                    if (i + 1 < len && sql.charAt(i + 1) == '?') {
+                        out.append("??");
+                        i += 2;
+                        break;
+                    }
                     if (paramIdx < params.size() && params.get(paramIdx) instanceof InListParameter) {
                         Collection<?> values = ((InListParameter) params.get(paramIdx)).getValues();
                         // Sql.inList(...) enforces non-empty, but the interface is public
diff --git a/subprojects/groovy-sql/src/test/groovy/groovy/sql/InListExpanderTest.groovy b/subprojects/groovy-sql/src/test/groovy/groovy/sql/InListExpanderTest.groovy
index 4eb14ef528..82ed3cd3d8 100644
--- a/subprojects/groovy-sql/src/test/groovy/groovy/sql/InListExpanderTest.groovy
+++ b/subprojects/groovy-sql/src/test/groovy/groovy/sql/InListExpanderTest.groovy
@@ -60,6 +60,56 @@ class InListExpanderTest {
         assert result.params == [42]
     }
 
+    @Test
+    void doubledQuestionMarkIsAnOperatorNotAPlaceholder() {
+        // PostgreSQL's driver takes ?? as an escaped literal ? operator, for the JSON
+        // operators ? ?| ?&, so it consumes no parameter
+        def result = InListExpander.expand(
+            "SELECT * FROM t WHERE data ?? 'k' AND id IN (?)",
+            [Sql.inList([1, 2, 3])])
+        assert result.sql == "SELECT * FROM t WHERE data ?? 'k' AND id IN (?, ?, ?)"
+        assert result.params == [1, 2, 3]
+    }
+
+    @Test
+    void escapedOperatorLeavesLaterParametersAligned() {
+        def result = InListExpander.expand(
+            "SELECT * FROM t WHERE data ?? 'k' AND id IN (?) AND active = ?",
+            [Sql.inList([1, 2]), true])
+        assert result.sql == "SELECT * FROM t WHERE data ?? 'k' AND id IN (?, ?) AND active = ?"
+        assert result.params == [1, 2, true]
+    }
+
+    @Test
+    void escapedFormsOfTheOtherJsonOperatorsArePreserved() {
+        // ?| and ?& escape as ??| and ??&; the list has two values so that an expansion
+        // landing on the operator instead of the marker is visible
+        def result = InListExpander.expand(
+            "SELECT * FROM t WHERE tags ??| ARRAY['a'] AND keys ??& ARRAY['b'] AND id IN (?)",
+            [Sql.inList([7, 8])])
+        assert result.sql == "SELECT * FROM t WHERE tags ??| ARRAY['a'] AND keys ??& ARRAY['b'] AND id IN (?, ?)"
+        assert result.params == [7, 8]
+    }
+
+    @Test
+    void anOddRunOfQuestionMarksIsAnEscapeThenAPlaceholder() {
+        // a list has to be present or expand returns before the walk begins
+        def result = InListExpander.expand(
+            'SELECT * FROM t WHERE data ??? AND id IN (?)',
+            ['x', Sql.inList([1, 2])])
+        assert result.sql == 'SELECT * FROM t WHERE data ??? AND id IN (?, ?)'
+        assert result.params == ['x', 1, 2]
+    }
+
+    @Test
+    void doubledQuestionMarkInsideAStringIsStillJustText() {
+        def result = InListExpander.expand(
+            "SELECT * FROM t WHERE note = '??' AND id IN (?)",
+            [Sql.inList([1, 2])])
+        assert result.sql == "SELECT * FROM t WHERE note = '??' AND id IN (?, ?)"
+        assert result.params == [1, 2]
+    }
+
     @Test
     void questionMarkInsideSingleQuotedStringIsNotExpanded() {
         def result = InListExpander.expand(