groovy
  1. groovy
  2. GROOVY-4328

Sql.withBatch is not escaping SQL variables

    Details

    • Type: Bug Bug
    • Status: Open Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.7.3
    • Fix Version/s: None
    • Component/s: SQL processing
    • Labels:
      None
    • Environment:
      Windows 7, Java 1.6.0_20
    • Number of attachments :
      0

      Description

      I wanted to replace a loop of sql.update calls like this:

      sql.execute("update Foo set Baz = $

      {baz}

      where Bar = $

      {bar}")

      With

      sql.withBatch(200, { stmt ->
      stmt.addBatch("update Foo set Baz = ${bar}

      where Bar = $

      {bar}

      ")
      })

      But looks like Sql.withBatch is not escaping variables like Sql.execute and Sql.update do
      and the script freaks out as soon as one of the bar variables contains a quote character.

        Activity

        Hide
        Paul King added a comment -

        Yes, this is not supported. It is actually a little difficult to do it simply and properly given the underlying Java APIs we have to work with.

        The java.sql.Statement class has the executeBatch(String) method and PreparedStatement has an executeBatch() method with no parameters. It means we can execute arbitrary String style queries in a batch or a single prepared statement with many rows in batches but we can't mix different prepared statements in the one batch.

        So, the best we could (simply) do is convert such a GString to a normal String. For that we could do a little bit more than we currently do by parsing the String and try to quote or escape "string" looking things but not number or date looking things but it might not be very elegant. Normally we would use the "?" character placeholders and a prepared statement and would have much less to do.

        Show
        Paul King added a comment - Yes, this is not supported. It is actually a little difficult to do it simply and properly given the underlying Java APIs we have to work with. The java.sql.Statement class has the executeBatch(String) method and PreparedStatement has an executeBatch() method with no parameters. It means we can execute arbitrary String style queries in a batch or a single prepared statement with many rows in batches but we can't mix different prepared statements in the one batch. So, the best we could (simply) do is convert such a GString to a normal String. For that we could do a little bit more than we currently do by parsing the String and try to quote or escape "string" looking things but not number or date looking things but it might not be very elegant. Normally we would use the "?" character placeholders and a prepared statement and would have much less to do.
        Hide
        Behrang Saeedzadeh added a comment -

        Is there a way to execute parametrized SQL statements in batch mode using Sql, like PreparedStatement's executeBatch()? I couldn't find that in the docs. If Sql does have such a feature then it makes sense to leave withBatch as is.

        An off-topic question: Is there a way to find out which parts of a Groovy String contains ${} placeholders or is information regarding those placeholders lost at compile time?

        Show
        Behrang Saeedzadeh added a comment - Is there a way to execute parametrized SQL statements in batch mode using Sql, like PreparedStatement's executeBatch()? I couldn't find that in the docs. If Sql does have such a feature then it makes sense to leave withBatch as is. An off-topic question: Is there a way to find out which parts of a Groovy String contains ${} placeholders or is information regarding those placeholders lost at compile time?
        Hide
        Paul King added a comment -

        A little late perhaps but a GString does retain (in an implementation specific way) information about the placeholders and the String constants in between those placeholders.

        Show
        Paul King added a comment - A little late perhaps but a GString does retain (in an implementation specific way) information about the placeholders and the String constants in between those placeholders.

          People

          • Assignee:
            Unassigned
            Reporter:
            Behrang Saeedzadeh
          • Votes:
            2 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated: