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

Hi All,
 I have a table with two columns of type varchar and the table name contains two dollars. I am unable to prepare a statement for this table. Here is the code:

con.createStatement().executeUpdate("create table a$b$c(a varchar, b varchar)");
PreparedStatement pstmt = con.prepareStatement("insert into a$b$c values( ? , ?)");
pstmt.setString(1,"Hello");
pstmt.setString(2,"Welcome");
pstmt.execute();

This code throws the following exception.
Exception in thread "main" org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
        at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:52)
        at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:117)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2118)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1241)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1227)
        at Main.main(Main.java:33)

How can i insert values in this table using PreparedStatement?

--Altaf Malik

---

Shape Yahoo! in your own image. Join our Network Research Panel today!

On Mon, 9 Jul 2007, Altaf Malik wrote:

> I have a table with two columns of type varchar and the table name
> contains two dollars. I am unable to prepare a statement for this table.
> Here is the code:
>
> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
> values( ? , ?)");

> Exception in thread "main" org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns:
0.

This is a bug in dollar quote parsing.  It assumes that any potential
dollar quote start will have an end tag.  When it doesn't find the end it
reports the end of the query, so the driver ends up skipping over the "?"
parameters.

Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> On Mon, 9 Jul 2007, Altaf Malik wrote:
>> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
>> values( ? , ?)");

> This is a bug in dollar quote parsing.  It assumes that any potential
> dollar quote start will have an end tag.

If it thinks that's a dollar quote start, it's wrong already... that's
a perfectly valid table name.  (Although personally I'd suggest that the
OP avoid dollar signs in identifiers, since they're none too portable.)

            regards, tom lane

I think $$ should start a quote instead of $x$. Or $ character have special meaning but if there is one dollar does not happen anything wrong. Are $$ and $x$ equal? If yes, what happens with the character(s) between the two dollar signs?

--Altaf


Tom Lane <tgl@sss.pgh.pa.us> wrote:

Kris Jurka writes:
> On Mon, 9 Jul 2007, Altaf Malik wrote:
>> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
>> values( ? , ?)");

> This is a bug in dollar quote parsing. It assumes that any potential
> dollar quote start will have an end tag.

If it thinks that's a dollar quote start, it's wrong already... that's
a perfectly valid table name. (Although personally I'd suggest that the
OP avoid dollar signs in identifiers, since they're none too portable.)

regards, tom lane

---

Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out.

"Altaf Malik" <mmalik_altaf@yahoo.com> writes:

> I think $$ should start a quote instead of $x$. Or $ character have special
> meaning but if there is one dollar does not happen anything wrong. Are $$ and
> $x$ equal? If yes, what happens with the character(s) between the two dollar
> signs?

$anything$ starts a quote and ends with $anything$ as in:

postgres=# select $foo$bar$foo$;
 ?column?
----------
 bar
(1 row)


However the $ must start a new token:

postgres=# select foo$foo$bar$foo$;
ERROR:  column "foo$foo$bar$foo$" does not exist
LINE 1: select foo$foo$bar$foo$;


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

Tom Lane schrieb:
> Kris Jurka <books@ejurka.com> writes:
>> On Mon, 9 Jul 2007, Altaf Malik wrote:
>>> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
>>> values( ? , ?)");
>
>> This is a bug in dollar quote parsing.  It assumes that any potential
>> dollar quote start will have an end tag.
>
> If it thinks that's a dollar quote start, it's wrong already... that's
> a perfectly valid table name.  (Although personally I'd suggest that the
> OP avoid dollar signs in identifiers, since they're none too portable.)

I guess I missed that case when implementing dollar quoting in the
driver. I will have a look at it shortly.

Best Regards
Michael Paesold

On Tue, 10 Jul 2007, Michael Paesold wrote:

> Tom Lane schrieb:
>> Kris Jurka <books@ejurka.com> writes:
>>> On Mon, 9 Jul 2007, Altaf Malik wrote:
>>>> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c values(
>>>> ? , ?)");
>>
>>> This is a bug in dollar quote parsing.  It assumes that any potential
>>> dollar quote start will have an end tag.
>>
>> If it thinks that's a dollar quote start, it's wrong already... that's
>> a perfectly valid table name.  (Although personally I'd suggest that the
>> OP avoid dollar signs in identifiers, since they're none too portable.)
>
> I guess I missed that case when implementing dollar quoting in the driver. I
> will have a look at it shortly.
>

Did you ever get a chance to look at this?  The attached patch fixes the
original complaint by ensuring that the ending tag exists, but as Tom
pointed out that's still not good enough as the second attached test case
demonstrates.

Kris Jurka

Hi Kris,

sorry for not coming back to this earlier. Yeah, this is no real solution.
I thought about checking the character directly before the dollar quote.
But I have not had the time to look at the grammer if that is enough to fix
all error cases... I will do so tomorrow.

Best Regards
Michael Paesold

Kris Jurka schrieb:
>
>
> On Tue, 10 Jul 2007, Michael Paesold wrote:
>
>> Tom Lane schrieb:
>>> Kris Jurka <books@ejurka.com> writes:
>>>> On Mon, 9 Jul 2007, Altaf Malik wrote:
>>>>> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
>>>>> values( ? , ?)");
>>>
>>>> This is a bug in dollar quote parsing.  It assumes that any
>>>> potential dollar quote start will have an end tag.
>>>
>>> If it thinks that's a dollar quote start, it's wrong already... that's
>>> a perfectly valid table name.  (Although personally I'd suggest that the
>>> OP avoid dollar signs in identifiers, since they're none too portable.)
>>
>> I guess I missed that case when implementing dollar quoting in the
>> driver. I will have a look at it shortly.
>>
>
> Did you ever get a chance to look at this?  The attached patch fixes the
> original complaint by ensuring that the ending tag exists, but as Tom
> pointed out that's still not good enough as the second attached test
> case demonstrates.
>
> Kris Jurka
>

Michael Paesold wrote:
> Hi Kris,
>
> sorry for not coming back to this earlier. Yeah, this is no real
> solution. I thought about checking the character directly before the
> dollar quote. But I have not had the time to look at the grammer if that
> is enough to fix all error cases... I will do so tomorrow.

Well, I think we have several simple options (apart from implementing a
real parser in the driver ;-)...

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\$]

2) Same concept as 1), but instead create a new method parseIdentifier()
in the Parser class that would be used to detect and "skip" all regular
keywords and identifiers in the query string, and therefore avoid the
current problem. Seems like a performance loss though, because we would
do more checks on each character.

3) Again in parseDollarQuotes(), we look at the character directly
before the $-char and see if it is an *allowed* character before a
dollar-quote. But what is allowed? Operators, a comma, parenthesis, etc.
... this is much harder to get right.

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.

Regarding your patch: I'm not sure if we should apply it, too. You will
probably get a different error messages in case of an unterminated
dollar quote. I haven't looked at the possible results.

My original reasoning was that if there is an unterminated quote, this
should not cause us parsing what is inside the quotes. So I would leave
it as-is. Another option would be to throw an "unterminated
dollar-quote" exception directly in the driver, which would be more
helpful in some cases.

Best Regards
Michael Paesold

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

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;

On Thu, 18 Oct 2007, Michael Paesold wrote:

> 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?

Looks good.  Applied.

> Should we do anything additional about error-ing out when we detect an
> unterminated dollar-quote (see previous mail)?
>

I don't think that's necessary.  We don't do so for string literals,
quoted identifiers, or block comments.

Kris Jurka