groovy.sql.Sql.asSql In Groovy SQL please do not use quotes around dynamic expressions

Question

I have a query in Grails like this:

 def strQuery = """select date_trunc('${type}', range) as range, sum(total_count) as total_count from connector_message_statistic  
                where range >= '${startDate}' and range < '${endDate}'
                group by date_trunc('${type}', range)                
            order by 1 asc;"""

I have this warning in the catalina log:

groovy.sql.Sql.asSql In Groovy SQL please do not use quotes around dynamic expressions (which start with $) as this means we cannot use a JDBC PreparedStatement and so is a security hole. Groovy has worked around your mistake but the security hole is still there. The expression so far is: select date_trunc('?', range) as range, is_internal,direction, sum(total_count) as total_count, sum(total_message_size) as total_message_size

How should I get rid of it? The problem is the dynamic date_trunc parameter. When I try something like this:

select date_trunc(:type, range) ....... group by date_trunc(:type, range)
sql.eachRow(strQuery, type: type)

Then I get this exception:

ERROR: column "connector_message_statistic.range" must appear in the GROUP BY clause or be used in an aggregate function Position: 23

How can I rewrite such a query to avoid these warning?

Answer 1

Generally you should be using the parametrized query instead of such like yours. This way the Hibernate or Groovy SQL can properly box the parameters according to their types.

I'd put the query like so:

def result = SomeDomain.executeQuery( 'select date_trunc(:type, range) as range, sum(total_count) as total_count from connector_message_statistic  
           where range >= :startDate and range < :endDate
           group by date_trunc( :type, range)                
           order by 1 asc", [ type:type, startDate:startDate, endDate:endDate ] )

You could also use positional params:

def result = SomeDomain.executeQuery( 'select date_trunc(?, range) as range, sum(total_count) as total_count from connector_message_statistic  
           where range >= ? and range < ?
           group by date_trunc( ?, range)                
           order by 1 asc", [ type, startDate, endDate, type ] )