Parameterize the ORM request with where in

Question

I am trying to parameterize a query that is currently working, and is ripe for an SQL injection attack:

qryAwards = ORMExecuteQuery( "from Award where awardID in (#form.deleteAwardList#) and Game.Season.User.userID=:uid", {uid=session.userID} ); if(not isNull(qryAwards) and arrayLen(qryAwards)){ for(i in qryAwards){ entityDelete(i); } } 

I tried this with a parameter without single quotes:

 qryAwards = ORMExecuteQuery( "from Award where awardID in (:awardList) and Game.Season.User.userID=:uid", {awardList=form.deleteAwardList, uid=session.userID} ); 

I keep getting the following error:

The value 117,118 cannot be converted to a number.

And this, with a parameter enclosed in single quotes:

 qryAwards = ORMExecuteQuery( "from Award where awardID in (':awardList') and Game.Season.User.userID=:uid", {awardList=form.deleteAwardList, uid=session.userID} ); 

Gets the following error:

Invalid parameters specified for the query.

Answer 1

In HQL (this is what you use when executing ORMExecuteQuery() ) the parameters used in the IN clause must be passed as an array. You need to convert form.deleteAwardList to an array. There are several ways to handle this, but this will work.

 qryAwards = ORMExecuteQuery( "from Award where awardID in (:awardList) and Game.Season.User.userID=:uid", {awardList=listToArray( form.deleteAwardList ), uid=session.userID} );