CODEWITHSUNDEEP
hibernateormcoldfusion
TechFanDan
TechFanDan

Reputation: 3482

Parameterize ORM query with where in clause

I'm trying to parametrize 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, having the param 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 the param 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 me the following error:

Invalid parameters specified for the query.

Upvotes: 6

Views: 649

Answers (1)

Scott Stroz
Scott Stroz

Reputation: 7519

In HQL (which is what you use when you do ORMExecuteQuery() ) parameters used in an IN clause need to be passed as an array. You need to convert form.deleteAwardList to an array. There are a few different 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}
);

Upvotes: 7

Related Questions