sqlgroovygstring

Groovy GString in Sql.execute - text variables are not surrounded by ' and query fails

I have the following problem, when I pass GString to SQL.executeInsert, the text variables are not automatically souranded by ' so the insert query failes:

String value4fa = "I would like to get know"
int value4fb = 2
def query = "INSERT INTO TAB_A (F_A, F_B) VALUES (${value4fa}, ${value4fb})"
sql.executeInsert(query);

If I put ' by myself:

 def query = "INSERT INTO TAB_A (F_A, F_B) VALUES ('${value4fa}', ${value4fb})"

Groovy informs me that I have introduced a security hole, because Groovy can not use PreparedStatement to execute the SQL query.

Could anybody explain me how to force Groovy to evaluate query body correctly and prepare the variables?

Solution

  • I have not tested this idea, but the code for 2.4.4 is here.

    The execute(String sql, List<Object> params) method uses prepared statements.

    Given that, consider this example:

    firstName = "yue"
lastName = "wu"
sql.execute("insert into people (firstName, lastName) "+
  " values (?,?)", [firstName, lastName])

    If necessary, it is easy to add single-quotes to the variables themselves (rather than the SQL string).