Ibatis's SQL Injection confirms my previous ideas

In the project, I used the Like statement in Ibatis. I haven't studied it. As a result, the SQL statement has the SQL injection vulnerability. Sort it out and remember it next time!
 
SQL statement:
 
 
Select *
From (select 1 from poll
<Dynamic prepend = "where">
<IsNotEmpty prepend = "and" property = "title">
Title like '% $ title $ %'
</IsNotEmpty>
<IsNotEmpty property = "used">
<IsEqual compareValue = "true" prepend = "and" property = "used">
<! [CDATA [status & 2> 0 and status & 1 <= 0 and status & 8 <= 0]>
</IsEqual>
</IsNotEmpty>
<IsNotEmpty prepend = "and" property = "startTimeBegin">
<! [CDATA [gmt_create >=# startTimeBegin #]>
</IsNotEmpty>
<IsNotEmpty prepend = "and" property = "startTimeEnd">
<! [CDATA [gmt_create <= # startTimeEnd #]>
</IsNotEmpty>
</Dynamic>
The limit 10000
) As t
 
Select *
From (select 1 from poll
<Dynamic prepend = "where">
<IsNotEmpty prepend = "and" property = "title">
Title like '% $ title $ %'
</IsNotEmpty>
<IsNotEmpty property = "used">
<IsEqual compareValue = "true" prepend = "and" property = "used">
<! [CDATA [status & 2> 0 and status & 1 <= 0 and status & 8 <= 0]>
</IsEqual>
</IsNotEmpty>
<IsNotEmpty prepend = "and" property = "startTimeBegin">
<! [CDATA [gmt_create >=# startTimeBegin #]>
</IsNotEmpty>
<IsNotEmpty prepend = "and" property = "startTimeEnd">
<! [CDATA [gmt_create <= # startTimeEnd #]>
</IsNotEmpty>
</Dynamic>
The limit 10000
) As t
Note the following:
 
 
Title like '% $ title $ %'
 
Title like '% $ title $ %'
The SQL injection vulnerability exists.
 
Below is a unit test:
 
Java code
PollQuery query = new PollQuery ();
Query. setCurrentPage (1 );
Query. setPageSize (50 );
Query. setTitle ("1231% 'or '1%' = '1"); // simple Syntax :(
List <snspoldrop> l = pollDAO. findPollList (query );
System. out. println (l. size ())
[Java] view plaincopy
PollQuery query = new PollQuery ();
Query. setCurrentPage (1 );
Query. setPageSize (50 );
Query. setTitle ("1231% 'or '1%' = '1"); // simple Syntax :(
List <snspoldrop> l = pollDAO. findPollList (query );
System. out. println (l. size ())
Test result (the SQL statement at the print position ):
 
 
Select * from poll where title like '% 100' or '1%' = '1%'
[Java] view plaincopy
1. select * from poll where title like '% 100' or '1%' = '1%'
Although the title does not match correctly, the or clause is constant. Ah!
 
It seems that the following statement is a simple escape:
 
 
Title like '% $ title $ %'
 
Title like '% $ title $ %'
Solution:
 
In oracle, change it to title like '%' | # title # | '%.
 
However, in mysql, the above statement is not feasible, but it still has the following problems:
 
 
Select * from poll where title like '%' |? | '%' Order by gmt_create desc limit ?, ?
 
Select * from poll where title like '%' |? | '%' Order by gmt_create desc limit ?, ?
You can also find out the results! Ah!
 
Required: title CONCAT ('%', # title #, '% ')
 
 
Select * from poll where title like CONCAT ('% ',?, '%') Order by gmt_create desc limit ?, ?
 
No problems were found during multiple tests!
 
 
 
------------------------------------------
 
Note:
 
Title like CONCAT ('%', # title #, '% ')

Column of the author oswin_jiang