Hi everybody,

I am doing a Code Cleanup on a project, many people have worked on. To prevent SQL-Injections I am trying to replace all directly inserted Strings from all Queries with named parameters. (I know that the SQL statements may be constructed not in the best way, but I have to handle that problem later.)

The following problem occurs. While it is fine to replace the first occurance of searchString with :param, the second replacement fails. I colored it [color=red]red[/color] in the code.

Thank you for your attention.

[b]Hibernate version:[/b]
3.2.0.cr4


[b]Full stack trace of any exception that occurs:[/b]
[code]
javax.servlet.ServletException: org.hibernate.QueryException: Not all named parameters have been set: [param] [select count(distinct a.objectId) as count from document, coursecategoryallocation, course, page as a left outer join (select tagusagePage.taggedItem, tag.name as tag, match(tag.name) against (' ' in boolean mode) as rel from tagusagePage join tag on tagusagePage.tag = tag.objectId where match(tag.name) against (' ' in boolean mode)) as b on a.objectId = b.taggedItem where course.objectId in ('21', '23', '22') and a.document = document.objectId and document.status = 3 and document.courseCategoryAllocation = coursecategoryallocation.objectId and coursecategoryallocation.course = course.objectId and ((match(a.text) against ( :param in boolean mode)) or (b.tag <> 'null'))]
org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1858)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:459)
javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
de.learnr.filter.InitializeSystemFilter.doFilter(InitializeSystemFilter.java:22)
de.learnr.filter.EncodingFilter.doFilter(EncodingFilter.java:22)
de.learnr.filter.HibernateFilter.doFilter(HibernateFilter.java:35)
de.learnr.filter.LoginFilter.doFilter(LoginFilter.java:119)
[\code]


[b]The generated SQL (show_sql=true):[/b]
See second line in Exception, please.

The Code that cause the Exception is following:
[code]
String innerSelect = "select tagusagePage.taggedItem, tag.name as tag, match(tag.name) against ('"
+ strippedSearchString + "' in boolean mode) as rel";
String innerFrom = "from tagusagePage join tag";
String innerOn = "on tagusagePage.tag = tag.objectId";
String innerWhere = "where match(tag.name) against ('"
+ strippedSearchString + "' in boolean mode)";

String innerSql = innerSelect + " " + innerFrom + " " + innerOn + " "
+ innerWhere;

String select = "select a.objectId as pageId, a.text as text, document.name as document, a.pageNumberInDocument as pageNumber, course.name as course, b.tag, a.attention as attention, match(a.text) against ("
+ " :param "
+ SEARCH_MODE
+ ") as textrel, sum(rel) as tagrel";
String from = "from document, coursecategoryallocation, course, page as a left outer join ("
+ innerSql + ") as b";
String on = "on a.objectId = b.taggedItem";
String where = "where course.objectId in "
+ inCourseIds
+ " and a.document = document.objectId and document.status = "
+ CONVERTED
+ " and document.courseCategoryAllocation = coursecategoryallocation.objectId and coursecategoryallocation.course = course.objectId and ((match(a.text) against ("
[color=red] + " :param " [/color]
+ SEARCH_MODE
+ ")) or (b.tag <> 'null'))";
String groupBy = "group by a.objectId";
String orderBy = "order by textrel desc, tagrel desc, attention desc, pageId desc";

String sql = select + " " + from + " " + on + " " + where + " "
+ groupBy + " " + orderBy;
logger.debug(sql);

SQLQuery query = session.createSQLQuery(sql);
query.setParameter("param", searchString);
...
List results = query.list();
[/code]



The following code works. The only thing changed is, that the second parameter is replaced by an directly inserted String.

[code]
String innerSelect = "select tagusagePage.taggedItem, tag.name as tag, match(tag.name) against ('"
+ strippedSearchString + "' in boolean mode) as rel";
String innerFrom = "from tagusagePage join tag";
String innerOn = "on tagusagePage.tag = tag.objectId";
String innerWhere = "where match(tag.name) against ('"
+ strippedSearchString + "' in boolean mode)";

String innerSql = innerSelect + " " + innerFrom + " " + innerOn + " "
+ innerWhere;

String select = "select a.objectId as pageId, a.text as text, document.name as document, a.pageNumberInDocument as pageNumber, course.name as course, b.tag, a.attention as attention, match(a.text) against ("
+ " :param "
+ SEARCH_MODE
+ ") as textrel, sum(rel) as tagrel";
String from = "from document, coursecategoryallocation, course, page as a left outer join ("
+ innerSql + ") as b";
String on = "on a.objectId = b.taggedItem";
String where = "where course.objectId in "
+ inCourseIds
+ " and a.document = document.objectId and document.status = "
+ CONVERTED
+ " and document.courseCategoryAllocation = coursecategoryallocation.objectId and coursecategoryallocation.course = course.objectId and ((match(a.text) against ('"
+ [color=red]searchString [/color]
+ "' "
+ SEARCH_MODE
+ ")) or (b.tag <> 'null'))";
String groupBy = "group by a.objectId";
String orderBy = "order by textrel desc, tagrel desc, attention desc, pageId desc";
String sql = select + " " + from + " " + on + " " + where + " "
+ groupBy + " " + orderBy;
logger.debug(sql);

SQLQuery query = session.createSQLQuery(sql);
query.setParameter("param", searchString);
...
List results = query.list();
[/code]

----------------------
Edit: Problem wasn't caused by Hibernate, but by some strange code. This topic could be deleted.