how to embed order by string in hql in grails for sorting?

Question

In grails 2 i used to use the following hql

def sortp = ""

if(params.sort){
    sortp = "order by r.${params.sort} ${params.order}"
}

def allRegsData = RaceRegistration.executeQuery("select r.id,  r.bibLabelPrinted, r.raceParticipant.bibNumber, r.bibLabelCollected, r.raceParticipant.firstName,r.raceParticipant.lastName,r.raceParticipant.gender,r.race.name, rt.name,   r.raceParticipant.ageDate, ad.genderOption, ad.minAge, ad.maxAge, sd.name, sm.encodedMark,  p.name, p.abbreviation,  ps.name, r.dateCreated, r.status, r.race.id, re.invoice.id, io.status, r.notes from InventoryOrder io inner join io.product p inner join io.productSize ps right outer join io.registration r left outer join r.registrationEntry re left outer join r.specialDivision sd left outer join r.ageDivision ad left outer join r.team rt left outer join r.seedMark sm where r.compositeEvent = ? ${sortp}", [raceGroup]);

I am now using grails 4 and it seems i can no longer use this way of embedding sortp string.

It throws the error

Unsafe query [select r.id, r.bibLabelPrinted, r.raceParticipant.bibNumber, r.bibLabelCollected, r.raceParticipant.firstName,r.raceParticipant.lastName,r.raceParticipant.gender,r.race.name, rt.name, r.raceParticipant.ageDate, ad.genderOption, ad.minAge, ad.maxAge, sd.name, sm.encodedMark, p.name, p.abbreviation, ps.name, r.dateCreated, r.status, r.race.id, re.invoice.id, io.status, r.notes from InventoryOrder io inner join io.product p inner join io.productSize ps right outer join io.registration r left outer join r.registrationEntry re left outer join r.specialDivision sd left outer join r.ageDivision ad left outer join r.team rt left outer join r.seedMark sm where r.compositeEvent = ? ]. GORM cannot automatically escape a GString value when combined with ordinal parameters, so this query is potentially vulnerable to HQL injection attacks. Please embed the parameters within the GString so they can be safely escaped.

i have tried this approach but it doesnt seem to sort

    def allRegsData = RaceRegistration.executeQuery("select r.id,  r.bibLabelPrinted, r.raceParticipant.bibNumber, r.bibLabelCollected, r.raceParticipant.firstName,r.raceParticipant.lastName,r.raceParticipant.gender,r.race.name, rt.name,   r.raceParticipant.ageDate, ad.genderOption, ad.minAge, ad.maxAge, sd.name, sm.encodedMark,  p.name, p.abbreviation,  ps.name, r.dateCreated, r.status, r.race.id, re.invoice.id, io.status, r.notes from InventoryOrder io inner join io.product p inner join io.productSize ps right outer join io.registration r left outer join r.registrationEntry re left outer join r.specialDivision sd left outer join r.ageDivision ad left outer join r.team rt left outer join r.seedMark sm where r.compositeEvent = :rg order by :s", [rg: raceGroup, s:  'r.' + params.sort + ' ' + params.order ]);

i appreciate any guide as to how to do the same in grails 4? Thanks!

score 0 · Accepted Answer · answered Jul 18 '22 at 04:32

thanks to Prudius from grails.slack.com

I had to change slightly how i form the string.

def sortp = ""

if(params.sort){
    sortp = "order by r.${params.sort} ${params.order}"
}

def q = "select r.id,  r.bibLabelPrinted, r.raceParticipant.bibNumber, r.bibLabelCollected, r.raceParticipant.firstName,r.raceParticipant.lastName,r.raceParticipant.gender,r.race.name, rt.name,   r.raceParticipant.ageDate, ad.genderOption, ad.minAge, ad.maxAge, sd.name, sm.encodedMark,  p.name, p.abbreviation,  ps.name, r.dateCreated, r.status, r.race.id, re.invoice.id, io.status, r.notes from InventoryOrder io inner join io.product p inner join io.productSize ps right outer join io.registration r left outer join r.registrationEntry re left outer join r.specialDivision sd left outer join r.ageDivision ad left outer join r.team rt left outer join r.seedMark sm where r.compositeEvent = :rg "

q <<= sortp

def allRegsData = RaceRegistration.executeQuery(q, [rg: raceGroup]);

this further helped.

Groovy literal StringBuilder/StringBuffer

how to embed order by string in hql in grails for sorting?

1 Answers1