brianchandotcom/liferay-portal — pull request #8015 by epgarcia (2012-12-06 18:05:43+00:00)
Title: LPS-31033 Escape bad column names in sqlQueries

Hey Brian,

Regarding the pending tests for LPS-31033:

- I've confirmed that we have a potential issue with the OrderByComparators and the "bad column names" for the sqlQueries. In case we use a "bad column name" (such as id) in an OrderByComparator and we use this comparator to buid a sqlQuery, we will get an Exception. I've written a simple test JournalStructureOrderByTest containing a method which forces this error. If you run it now, you will get this:

```
[junit] Testcase: testOrderByPK(com.liferay.portlet.journal.service.JournalStructureOrderByTest):	Caused an ERROR
[junit] com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
...
[junit] Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'journalStructure.id' in 'order clause'
```

- We need to escape bad column names when building the order by clause in case we're using a sqlQuery instead of a simple query. So at BasePersistenceImpl.appendOrderByComparator we need to know if a sqlQuery is being used and if so, check if the order by field is a bad column name and it needs to be escaped. The first part is easy, since we already know in the ftl templates where we're using a sqlQuery. The second part requires an static list of bad column names per entity, since it'd be unefficient in terms of performance to look at the bad_column_name.txt file everytime.

This PR contains:

- A simple test case for the StructurePKComparator in JournalStructure. So far it only tests the critical case: using the StructurePKComparator in a method which uses sqlQuery. You can check that it fails before appliying the solution
- A possible solution for this issues, in the terms described above
- The regenerated *PersistenceImpl. With this latest commit, the test will not fail.

Please let me know if you have any question.

@shuyangzhou
@migue
@juliocamarero