Using cfqueryparam with a ColdFusion HQL query

[ciaranarcher]

I am using a HQL query to get a bunch of state objects like so:

    <cfquery name="LOCAL.qStates" dbtype="hql">

        from States where countryID = #ARGUMENTS.countryID#

        order by name asc

    </cfquery>

This works fine. However, I was brought up well and I want to use cfqueryparam, ideally like so:

   <cfquery name="LOCAL.qStates" dbtype="hql">

        from States

        where countryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.countryID#" />

        order by name asc

    </cfquery>

But this throws an error:

    [empty string] java.lang.NullPointerException at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:353) at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:323) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:98) at coldfusion.orm.hibernate.HibernatePersistenceManager._executeHQL(HibernatePersistenceManager.java:822) at coldfusion.orm.hibernate.HibernatePersistenceManager.executeHQL(HibernatePersistenceManager.java:751) at ....

Anyone know how to get around this and use cfqueryparam with cfquery HQL queries?

Thanks in advance!

PS: Also posted on SatckOverflow here.

[12Robots]

Take off the Datatype, it's not required and hibernate probably does not understand them.

Jason

[Adam Cameron.]

Do you need to parameterise dynamic values in an HQL statement?  I mean... you're not passing it to the DB, you're passing it to Hibernate.

Have you checked to see that Hibernate doesn't handle the parameterisation automatically for you?  IE: what's the resultant SQL sent to the DB without attempting to put our <cfqueryparam> tags in?

--

Adam

[12Robots]

With HQL Hibernate does NOT handle parameterization for you. you must do it yourself. And yes, you need to do it. HQL can be injected just like SQL.

http://www.12robots.com/index.cfm/2009/11/19/ORM-Hibernate-Injection--Security-Series-14

With the other ORM methods, params are handled for you. You only need to param HQL yourself.

[Adam Cameron.]

Cool: good to know.  Pity it possibly doesn't work in CF, eh?

--

Adam

[12Robots]

Pity what doesn't work?

[Adam Cameron.]

Doing the parameterisation with <cfquery> (which, according to the OP, doesn't work).  Sorry, wasn't clear.

--

Adam

[12Robots]

But it *does* work, if you leave off the datatype.

At least that is how the LiveDocs give the example.

<cfquery dbtype="hql" name="artists" ormoptions=#{cachename=""}#>

     from Artists where firstname=<cfqueryparam value="Aiden">

</cfquery>

[ciaranarcher]

So guys, using the cfquery method, if you leave out the datatype, does Hibernate just use it's own object model to work out the property type (e.g. int) and somehow do the type checking? I'm struggling to understand how this protects me and ensures that I can't inject SQL as there is no (explicit!) type checking!

Thanks for all the feedback so far and the articles too.

[Adam Cameron.]

Well I reckon give it a go & see if it doesn't error.

You're protected from SQL injection because a parameter is intrinsically treated as just a value, and not as part of the SQL statement. So it can never be treated as SQL, so can never be executed.

Type-checking & injection aside, the •chief• reason to paramterise one's dynamic values is to improve the SQL statement's execution plan by reducing the frequency one needs to compile the SQL statement. If the dynamic values are hard-coded in the SQL statement, then the SQL statement needs recompilation every time the value changes. Whereas if it's a parameter, a change in value does not change the SQL statement so there's no recompile needed. Also the DB caches compiled SQL statements, and using params reduces the number of (mostly useless because they won't be reused) SQL statements in the cache.

Good to know the 's types don't quite fit the purpose? Or is it because Hibernate knows the necessary data-type so it can do this bit of the parameterisation itself? Probably the latter, I s'pose.

--

Adam

[ciaranarcher]

Hi all

Just an update:

Even if I use cfquery HQL with a cfquery param and no cfsqltype, e.g.

    <cfquery name="LOCAL.qStates" dbtype="hql">

        from States

        where countryID = <cfqueryparam value="#ARGUMENTS.countryID#" />

        order by name asc

    </cfquery>

BUT I get the same error:

java.lang.NullPointerException at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:353) at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:323) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:98) at coldfusion.orm.hibernate.HibernatePersistenceManager._executeHQL(HibernatePersistenceManager.java:822) at coldfusion.orm.hibernate.HibernatePersistenceManager.executeHQL(HibernatePersistenceManager.java:751) at coldfusion.orm.hibernate.HibernatePersistenceManager.executeQuery(HibernatePersistenceManager.java:613) at coldfusion.orm.ORMUtils._executeQuery(ORMUtils.java:330) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:575) at cfAccountService2ecfc1675673048$funcGETSTATES.runFunction(Q:\inetpub\wwwroot\com\ewallet\services\AccountService.cfc:39) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:491) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:337) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2360) at ...

I know I should be able to do this.

Can someone please verify this? If so I'll submit a bug, As Mark says, this example is the same as the LiveDocs example and should work.

Cheers,

Ciaran

PS: Since I am using HQL with cfquery I am obviously using CF 9.0.1, running against a SQL Server 2000 database.

[12Robots]

I have verified that cfqueryparam DOES indeed work with dbtype="hql" using the CFARTGALLERY Apache Derby DB embedded in CF9.

Application.cfc

component {

     this.name="TestORM";

     this.datasource = "cfartgallery";

     this.ormEnabled = true;

     public void function onRequestStart() {

      ormReload();

     }

}

Artist.cfc:

component persistent="true" table="ARTISTS"  {

}

index.cfm

<cfset lastnameletter = "W" />

<cfquery name="getArtists" dbtype="hql">

FROM Artist WHERE lastname like <cfqueryparam value="#lastnameletter#%" />

order by lastname

</cfquery>

[12Robots]

I have now also confirmed it works with:

MySQL 5

MSSQL 2000

[ciaranarcher]

Thanks for the feedback guys, I'm still getting an error - so more investigation required...on Monday. I'll post what I have then.

Thanks,

Ciaran

[ciaranarcher]

Hi all

I got to the bottom of this.

My state object was setup like so:

  <cfcomponent output="false" persistent="true">

      <cfproperty name="stateID" type="numeric" fieldType="id" generator="identity" />

      <cfproperty name="name" type="string" />

      <cfproperty name="alphaCode" type="string" />

      <!--- Relationships --->

      <cfproperty name="country" type="array" fieldtype="many-to-one" cfc="Countries" fkcolumn="countryID" lazy="true" />

  </cfcomponent>

When using the <cfqueryparam> tag Hibernate was perhaps trying to map the number I was passing in as an array and failing thus throwing an error.

If I remove the relationship from the property like so:

  <cfproperty name="countryID" type="numeric" />

...then it works.

Thanks for all the contributions!

[Guest]

I am running into the same issue (param on a field that has a defined relationship).  So, this then means that you can't do that?  Seems ridiculous - in my opinion.

[Dave Watts]

The URL that Jason posted (which points to an article he wrote) describes how to parameterize HQL. You don't use CFQUERYPARAM, but it's parameterized just the same.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Read this before you post:

http://forums.adobe.com/thread/607238