Re: Unable to prepare a statement when the object names contain more than one $ symbol

Now including the attachement... ;-)

Michael Paesold wrote:
> I wrote:
> ...
>> 1) In parseDollarQuotes(), we look at the character directly before
>> the $-char and compare it against all valid identifier characters. If
>> it is, we don't have a possible dollar-quote start:
>>
>>  From scan.l:
>> ident_start             [A-Za-z\200-\377_]
>> ident_cont              [A-Za-z\200-\377_0-9\$]
> ...
>> Looking at gram.y and scan.l (...but I'm no expert) I can't see
>> another case of a dollar-character that I missed, except the one
>> allowed in ident_cont... so option 1) seems like the easiest solution.
>> I will send a patch implementing option 1 shortly.
>
> Ok, here is the patch. It also adds your tests exposing the bug to the
> test suite. The patch fixes both test cases and passes all other
> regression tests (at least with JDK 1.5....). Does it look OK to you?
>
> Should we do anything additional about error-ing out when we detect an
> unterminated dollar-quote (see previous mail)?
>
> Best Regards
> Michael Paesold


Fix dollar quote bug (identifiers may continue with a dollar-character)

diff -r ee30bbde84f5 org/postgresql/core/Parser.java
--- a/org/postgresql/core/Parser.java    Thu Oct 18 16:27:52 2007 +0200
+++ b/org/postgresql/core/Parser.java    Thu Oct 18 18:07:50 2007 +0200
@@ -89,7 +89,8 @@ public class Parser {
      * character.
      */
     public static int parseDollarQuotes(final char[] query, int offset) {
-        if (offset + 1 < query.length)
+        if (offset + 1 < query.length
+                && (offset == 0 || !isIdentifierContChar(query[offset-1])))
         {
             int endIdx = -1;
             if (query[offset + 1] == '$')
@@ -205,6 +206,37 @@ public class Parser {
     }

     /**
+     * Checks if a character is valid as the start of an identifier.
+     *
+     * @param c the character to check
+     * @return true if valid as first character of an identifier; false if not
+     */
+    public static boolean isIdentifierStartChar(char c) {
+        /*
+         * Extracted from {ident_start} and {ident_cont} in
+         * pgsql/src/backend/parser/scan.l:
+         * ident_start    [A-Za-z\200-\377_]
+         * ident_cont     [A-Za-z\200-\377_0-9\$]
+         */
+        return (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z')
+                || c == '_' || c > 127 ;
+    }
+
+    /**
+     * Checks if a character is valid as the second or later character of an
+     * identifier.
+     *
+     * @param c the character to check
+     * @return true if valid as second or later character of an identifier; false if not
+     */
+    public static boolean isIdentifierContChar(char c) {
+        return (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z')
+                || c == '_' || c > 127
+                || (c >= '0' && c <= '9')
+                || c == '$';
+    }
+
+    /**
      * @return true if the character terminates an identifier
      */
     public static boolean charTerminatesIdentifier(char c) {
@@ -228,7 +260,7 @@ public class Parser {
     }

     /**
-     * Checks if a character is valid as the second or latter character of a
+     * Checks if a character is valid as the second or later character of a
      * dollar quoting tag.
      *
      * @param c the character to check
diff -r ee30bbde84f5 org/postgresql/test/jdbc2/PreparedStatementTest.java
--- a/org/postgresql/test/jdbc2/PreparedStatementTest.java    Thu Oct 18 16:27:52 2007 +0200
+++ b/org/postgresql/test/jdbc2/PreparedStatementTest.java    Thu Oct 18 17:36:29 2007 +0200
@@ -405,6 +405,27 @@ public class PreparedStatementTest exten
         st.close();
     }

+    public void testDollarQuotesAndIdentifiers() throws SQLException {
+        // dollar-quotes are supported in the backend since version 8.0
+        if (!TestUtil.haveMinimumServerVersion(conn, "8.0"))
+            return;
+
+        PreparedStatement st;
+
+        conn.createStatement().execute("CREATE TEMP TABLE a$b$c(a varchar, b varchar)");
+        st = conn.prepareStatement("INSERT INTO a$b$c (a, b) VALUES (?, ?)");
+        st.setString(1, "a");
+        st.setString(2, "b");
+        st.executeUpdate();
+        st.close();
+
+        conn.createStatement().execute("CREATE TEMP TABLE e$f$g(h varchar, e$f$g varchar) ");
+        st = conn.prepareStatement("UPDATE e$f$g SET h = ? || e$f$g");
+        st.setString(1, "a");
+        st.executeUpdate();
+        st.close();
+    }
+
     public void testComments() throws SQLException {
         PreparedStatement st;
         ResultSet rs;