[GROOVY-5898] Sql.java throws IllegalStateException("Failed to process query. Unterminated ' character?") when comments contain quotes

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.8.8, 2.1.0-rc-1
• Fix Version/s: 2.2.0-beta-1
• Component/s: SQL processing
• Labels: sql
• Assignee: Paul King
• Reporter: Marc Silverboard
• Created: 10/Jan/13 10:49
• Updated: 10/Jul/13 04:42
• Resolved: 04/Jul/13 00:24

Description

In versions of Groovy since 1.8, when Groovy SQL prepares to execute a parameterized query, it checks the query SQL for unterminated single quotes (lines 4168 - 4188 of groovy.sql.Sql.java in the current trunk source). However, the current code doesn't recognize that when the SQL contains comments, and the comments include apostrophes or single quote characters, that the SQL is valid.

For example, this code will result in an exception:

def myQuery = """
  select count(*), cast(? as varchar(10))
  -- An ' apostrophe
  from art.option 
"""

sql.eachRow(myQuery.toString(), ['1.7.5']) 
{row ->
  println row
}

Perhaps you could use a regex or string replace to remove or ignore everything found between "--" and "\n" or "/" and "/" before testing for unterminated quotes?