Seam Framework EntityQuery restrictions does not work for My

[lkw]

Hi,

I'm a newbie in Seam. Appreciate if someone can help on this.

I run the contactlist example from Seam version 1.2.1 . Everything works if I use HSQLDB. I'm able to do pagination and restriction with the EntityQuery framework. However, the restrictions does NOT work when I switch to MySql database. Has anyone encounter this problem?

My configuration is as below:

components.xml

<?xml version="1.0" encoding="UTF-8"?>
<components xmlns="http://jboss.com/products/seam/components"
 xmlns:fwk="http://jboss.com/products/seam/framework"
 xmlns:core="http://jboss.com/products/seam/core"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation=
 "http://jboss.com/products/seam/framework http://jboss.com/products/seam/framework-1.2.xsd
 http://jboss.com/products/seam/core http://jboss.com/products/seam/core-1.2.xsd
 http://jboss.com/products/seam/components http://jboss.com/products/seam/components-1.2.xsd">

 <factory name="contact" value="#{contactHome.instance}"/>
 <fwk:entity-home name="contactHome"
 entity-class="Contact"/>

 <fwk:entity-query name="contacts"
 max-results="20">
 <fwk:ejbql>from Contact</fwk:ejbql>
 <fwk:order>lastName</fwk:order>
 <fwk:restrictions>
 <value>lower(firstName) like lower( #{exampleContact.firstName} + '%' )</value>
 <value>lower(lastName) like lower( #{exampleContact.lastName} + '%' )</value>
 </fwk:restrictions>
 </fwk:entity-query>

 <component name="exampleContact"
 class="Contact"/>

</components>

[cptnkirk]

exception?

[fernando_jmt]

Just try:

 <fwk:restrictions>
 <value>lower(firstName) like lower( concat(#{exampleContact.firstName}, '%' ))</value>
 <value>lower(lastName) like lower(concat( #{exampleContact.lastName}, '%' ))</value>
 </fwk:restrictions>

That should work in MySQL.

[lkw]

Thx everyone for the help. The solution provided by fernando_jmt works. Look like the value element in the restrictions is dependent on the underlying DBMS

[cptnkirk]

This just seems begging for a SQL injection problem. Be careful when using this feature.

[matt.drees]

How so? El expressions get replaced by a parameter, which would cause the input to be escaped correctly, right?

[cptnkirk]

If that's correct, then great. I'm wrong and everything is safe. I've been cleaning out a lot of "select ... where name like "+name+"%" from a codebase I inherited. So I may just have been spooked by seeing "#{expression} + %". That said, I don't recall reading anywhere that Seam does what you describe. Seam inlines these EL expressions elsewhere in components.xml.

<component class="com.helloworld.Hello" name="hello">
 <property name="name">#{user.name}</property>
</component>

[cptnkirk]

I guess I'm just expecting the values to be run through Seam's interpolator, which just does a string replace. I don't expect that Seam will generate a prepared statement, replace the EL with bind variables, etc. If it does, more power to Gavin.

[pmuir]

AFAICS this is exactly what it does - QueryParser in org.jboss.seam.persistence takes a ejbql which contains EL, , and replaces it with a parameter number, and creates a list of parameter value bindings. Then in EntityQuery query.setParameter is used.

[matt.drees]

http://docs.jboss.com/seam/1.2.1.GA/reference/en/html/persistence.html#d0e5563

So this only works if you're using a seam-managed persistence context. If you're not, I'm not sure if you'll get a parse error or an sql injection vulnerability.

[pmuir]

Err, that link says it works whether you use an SMPC or a plain JPA PC. It also clears up the sql injection question

[cptnkirk]

Yep, +1 Seam crew. I should have known better. :)

[matt.drees]

Duh, yeah, you're right. Somehow missed the second part of that sentence. Thanks for the correction.