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"
开发者_StackOverflowint 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?

You should not have to decorate strings with anything to have them converted to PreparedStatement automatically.

sql.execute("INSERT INTO TAB_A (F_A, F_B) VALUES ($value4fa, $value4fb)")

will do the correct thing for all the methods that accept a GString as a single parameter. note the lack of {} which is syntactic sugar for .toString()

The reason yours causes the complaint is that,

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

is different than passing the GString directly to the method.

it applies the substitutions before passing query to the .execute() method. Given your example data, it passes the following to and the replacements have already happened. "INSERT INTO TAB_A (F_A, F_B) VALUES (I would like to get know, 2)" which is not even a valid SQL statement because the string value is missing the ' around it.

This is functionally equivalent to using String.format(), StringBuilder/Buffer.append() or plain on concatenation using +.

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).