Escape ‘_’ in SQL and Java

We had a table for which, a column say ‘ENAME’, was allowed to have underscore(_). The same column was allowed to be searchable from Flex User-Interface. As underscore(_) was allowed to be used in the text-box, we ended up retrieving all the records from the table when the user entered only ‘_’ in the text-box and queried for results. This happens as underscore(_) has a special meaning in Oracle and is used as a wild-card character(for single character) in SQL parlance.

The solution to this problem was to escape the underscore(_) in SQL and treat it as just another character as opposed to a wild-card character and escape it in Oracle SQL. Following should be the SQL query:

SELECT * FROM EMPLOYEE WHERE ENAME like '%\\_%'  ESCAPE '\';

The same fix applies to even ‘%’ which is used as a wild-card character for multiple characters.

In Java, we had to do the following as we were appending the search-value as a query parameter in SQL.

String query="SELECT * FROM EMPLOYEE";

if(!"".equals(ename))
{
    query=query.concat(" WHERE ENAME like "+"'%"+ename.replaceAll("_", "\\\\_")+"%'  ESCAPE '\\' ");

}

Just apply the above fix and fail the QA from breaking our robust Java and Oracle code.

Advertisements