Friday, March 26, 2010

Converting FIQL expressions into SQL queries in CXF JAXRS

I've done some more work on the search extensions so that users can easily introspect a given SearchCondition instance representing either a primitive or complex query and also added a utility SearchCondition.toSQL(String tableName, String... columnNames) which can be used to convert this search condition into an equivalent SQL query to be subsequently executed against a database.

For example,


// find all conditions with names starting from 'ami'
// and levels greater than 10
SearchCondition sc =
fiqlParser.parse("name==ami*;level=gt=10");
assertEquals("SELECT * FROM table
WHERE
name LIKE 'ami%'
AND
level > '10'",
sq.toSQL("table"));

// find all conditions with names starting from 'foo'
// and those with names not ending with 'bar' or
// levels greater than 10
SearchCondition sc2 =
fiqlParser.parse(
"name==foo*;(name!=*bar,level=gt=10)");
assertEquals("SELECT * FROM table
WHERE
(name LIKE 'foo%')
AND
((level > '10'")
OR
(name NOT LIKE '%bar')),
sq.toSQL("table"));




Here are some clarifications. First, note that the above code shows that a FIQL parser creates a search condition but it is a unit test code, the actual user code will use a SearchContext instead in a type safe way, providing a bean class to it. SearchContext will create an instance and will inject into it the property values, those provided and extracted from the original FIQL expression.

This provides for a possibility to protect against possible SQL injection attacks at the Java and the bean validation levels. For example, if a Book bean has an integer 'id' property then there's no way an unsafe FIQL query such as 'id==DROP%20users' can result in a 'DROP users' value injected due to a NumberFormatException.

Additionally, bean setters may have some custom validation logic and the CXF FIQL parser may also get enhanced to check for the bean validation annotations.

Perhaps you might want to give it a try and see how safe this option of auto-converting FIQL expressions into SQL queries is ?

In the end of the day, the good thing is that you actually do not have to use
SearchCondition.toSQL to get the SQL query. Suppose you are still concerned about the security or see that this method is not going to help you with building the advanced SQL queries which you actually use in the production, may be you are using java.sql.PreparedStatements or perhaps you don't even use a relational database but rather work with say Cassandra. If it is the case then just introspect a SearchCondition by getting to all the subconditions it has, and build a query which suits best.

2 comments:

Dimitris Zenios said...

Hi

I have been using fiql search for a project I have and I think is the perfect tool for searching with rest service.One problem though is that when I have fiql expressions that start with a "(" then I get an FiqlParseException.For example (name==test,surname==test);(age!=18,middle_name==test). should convert to WHERE (name = 'test' OR surname = 'test') AND (age != 18 OR middle_name = 'test') in SQL but instead i get an exception in ");(" character sequence.Any idea?

Sergey Beryozkin said...

Andy Michalec has just fixed it - thanks for letting us know; you're also welcome to ask on the users list - this will also help users to see that the FIQL search extension is actually being experimented with