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

The comment from injecteer helped me, this code is working now:

  strQuery = """select date_trunc(:type, range) as rangenew, is_internal,direction, sum(total_count) as total_count, sum(total_message_size) as total_message_size 
                    from connector_message_statistic 
                    where range >= :startDate::timestamp and range < :endDate::timestamp 
                    group by rangenew, is_internal,direction
                    order by 1 asc;"""
    sql.eachRow(strQuery, [type: type, startDate: startDate, endDate: endDate]

I renamed the fieldname in date_trunc and added the field in the group by clause