CFQUERYPARAM throws 'incompatible type' error with HQL

Question

My simple query:

<CFQUERY name="thisRecipePreps" dbtype="hql">
    FROM recipeItems r
    WHERE r.recipe = <cfqueryparam value="#thisrecipeid#">
    AND r.otherRecipe_id <> <cfqueryparam value="0">
</CFQUERY>

My object recipeItems has a 'many-to-one' relationship with 'recipe' which would return an array (I believe). I want to filter the results by recipe_id and this works when I do not have the CFQUERYPARAM tag in there but like this it throws the error:

Error casting an object of type java.lang.Integer to an incompatible type. This usually indicates a programming error in Java, although it could also mean you have tried to use a foreign object in a different way than it was designed.

I believe the error means that the 'thisrecipeid' variable is of type 'integer' but 'r.recipe' is not.

I want to (and need to) use CFQUERYPARAM but I don't seem to be able to.

Thanks for any insights

NOTE: I am getting suggestions from the site as well as members that this is the same question as another question that was supposedly solved (https://stackoverflow.com/a/4185205/4575762). However, the question was solved by removing the relationship between two entities which completely defeats the purpose of using ORM/Hibernate to begin with. Solving a problem by breaking something else is not solving a problem. Furthermore, the error on that question showed a 'NULL pointer' issue, this error is an incompatible type issue.

What happens when you include a `CFSQLType` attribute in your `cfqueryparam` tag? — Dan Bracuk, Mar 28 '21 at 19:45
The ```cfsqltype``` isn't compatible with HQL. HQL Already knows the type. If you include the type it actually throws an error no matter what. — Robert Santen, Mar 28 '21 at 21:20
I wonder if variables are already protected when `dbtype="hql"` — James A Mohler, Mar 28 '21 at 21:33
Unfortunately they are not. They are still vulnerable to a XSS attack and we must use CFQUERYPARAM — Robert Santen, Mar 28 '21 at 21:45
Probably not related to your problem, but what is the point of a query param for a constant - 0 in this case? — Dan Bracuk, Mar 29 '21 at 03:28
Is the answer to this old question of any help: https://stackoverflow.com/a/4185205/4575762 ? — Sev Roberts, Mar 29 '21 at 08:30
@SevRoberts Yeah, I found that question and answer and although that would most definitely solve the error message, it would break the relationship which is one of the main reasons to use ORM to begin with. — Robert Santen, Mar 29 '21 at 14:55

score 0 · Answer 1 · answered Mar 28 '21 at 21:50

0

Whitespace can cause an issue with HQL dbtype https://blog.adamcameron.me/2013/03/whitespace-in-hql-causes-exceptions.html

answered Mar 28 '21 at 21:50

Bobmd

125
7

This article has to do with TABS in the query which I don't have (interesting article though but I don't use tabs in queries), thanks – Robert Santen Mar 29 '21 at 01:43

CFQUERYPARAM throws 'incompatible type' error with HQL

1 Answers1