SQL or HQL precompiled statements to prevent SQL injection, but cannot handle% and _ special characters

Recent projects in the rectification, the direct splicing of all the DAO layer of SQL string code, the way to use the precompiled statement. Individual by writing the DAO layer of unit test, there are the following points.

The DAO layer code is as follows

Pre-compiled Sqlpublic list<indvconfigmodel> selectconfigbysuffix (string suffix) {        string hql = "from Indvconfigmodel where configkey like '% ' | |?| | ' %'";        Return THIS.SELECTCONFIGBYHQL (HQL, New Object[]{suffix});

The unit test code and execution results are as follows:

@Testpublic void Testlike () {    list<indvconfigmodel> List = Dao.selectconfigbysuffix ("picqual");    Assert.assertequals (List.size (), 2);//1.true    list = Dao.selectconfigbysuffix ("picqua%");    Assert.assertequals (List.size (), 2);//2.true        list = Dao.selectconfigbysuffix ("Pic ' Qual");    Assert.assertequals (List.size (), 0);//3. True

1, the first assertion is true, indicating that the above practice, indeed can play the effect of fuzzy query

2. The second assertion is true, stating that% is considered a fuzzy match and is not considered a normal character by Oracle. This indicates that the precompiled statement is not able to handle special characters in the parameter values. Encountering the special characters of the% and _ This database fuzzy query requires the user to escape themselves.

3. The third assertion does not report an exception. Description: The precompiled statement has escaped the special character single quotation marks of Oracle. The single quotation mark is considered to be the query content, not the delimiter of the string.

Because SQL injection is actually the use of special character single quotes, generate or 1 = 1 in this format of SQL. Precompilation already handles single quotes, so you can prevent SQL injection

SQL or HQL precompiled statements to prevent SQL injection, but cannot handle% and _ special characters