ibatis的SQL注入，证实了我此前的想法

转载于：http://blog.csdn.net/oswin_jiang/article/details/4242431

 

在项目中，运用Ibatis中Like写法，没有研究下，结果SQL语句存在SQL注入漏洞，整理下，下次谨记啊！

sql语句：

Sql代码

1. select * 
2.         from (select 1 from poll 
3.         <dynamic prepend=" where "> 
4.             <isNotEmpty prepend=" and " property="title"> 
5.                 title like '%$title$%' 
6.             </isNotEmpty> 
7.             <isNotEmpty property="used"> 
8.                 <isEqual compareValue="true" prepend=" and " property="used"> 
9.                     <![CDATA[status & 2 > 0 and status & 1 <= 0 and status & 8 <= 0 ]]> 
10.                 </isEqual> 
11.             </isNotEmpty> 
12.             <isNotEmpty prepend=" and " property="startTimeBegin"> 
13.                 <![CDATA[ gmt_create >= #startTimeBegin# ]]> 
14.             </isNotEmpty> 
15.             <isNotEmpty prepend=" and " property="startTimeEnd"> 
16.                 <![CDATA[ gmt_create <= #startTimeEnd# ]]> 
17.             </isNotEmpty> 
18.         </dynamic> 
19.         limit 10000 
20.         ) as t 

[sql] view plain copy print ?

1. select * 
2.         from (select 1 from poll 
3.         <dynamic prepend=" where "> 
4.             <isNotEmpty prepend=" and " property="title"> 
5.                 title like '%$title$%' 
6.             </isNotEmpty> 
7.             <isNotEmpty property="used"> 
8.                 <isEqual compareValue="true" prepend=" and " property="used"> 
9.                     <![CDATA[status & 2 > 0 and status & 1 <= 0 and status & 8 <= 0 ]]> 
10.                 </isEqual> 
11.             </isNotEmpty> 
12.             <isNotEmpty prepend=" and " property="startTimeBegin"> 
13.                 <![CDATA[ gmt_create >= #startTimeBegin# ]]> 
14.             </isNotEmpty> 
15.             <isNotEmpty prepend=" and " property="startTimeEnd"> 
16.                 <![CDATA[ gmt_create <= #startTimeEnd# ]]> 
17.             </isNotEmpty> 
18.         </dynamic> 
19.         limit 10000 
20.         ) 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>
        limit 10000
        ) as t

请关注此写法的：

Sql代码

1. title like '%$title$%' 

[sql] view plain copy print ?

1. title like '%$title$%' 

title like '%$title$%'

存在SQL注入漏洞。

下面是一段单元测试：

Java代码

1. PollQuery query = new PollQuery(); 
2. query.setCurrentPage(1); 
3. query.setPageSize(50); 
4. query.setTitle("1231%' or '1%' = '1");//很简单的写法:( 
5. List<SnsPollDO> l = pollDAO.findPollList(query); 
6. System.out.println(l.size()) 

[java] view plain copy print ?

1. PollQuery query = new PollQuery(); 
2. query.setCurrentPage(1); 
3. query.setPageSize(50); 
4. query.setTitle("1231%' or '1%' = '1");//很简单的写法:( 
5. List<SnsPollDO> l = pollDAO.findPollList(query); 
6. System.out.println(l.size()) 

PollQuery query = new PollQuery();
query.setCurrentPage(1);
query.setPageSize(50);
query.setTitle("1231%' or '1%' = '1");//很简单的写法:(
List<SnsPollDO> l = pollDAO.findPollList(query);
System.out.println(l.size())

测试结果（打印处的sql语句）：

Java代码

1. select * from poll   where    title like '%1231%' or '1%' = '1%' 

[java] view plain copy print ?

1. 1. select * from poll   where    title like '%1231%' or '1%' = '1%' 

1. select * from poll   where    title like '%1231%' or '1%' = '1%'

尽管 title 没匹配对，但是or后面那句是恒等的。哎！

看来下面的写法只是简单的转义下：

Sql代码

1. title like '%$title$%' 

[sql] view plain copy print ?

1. title like '%$title$%' 

title like '%$title$%'

如何解决：

在oracle下面改成：title like '%'||#title#||'%'，这样肯定是可以的。

但是在mysql中，上述写法是不行，还是有上面的问题的：

Sql代码

1. select  * from poll where  title like '%'||?||'%'  order by gmt_create desc   limit ?, ? 

[sql] view plain copy print ?

1. select  * from poll where  title like '%'||?||'%'  order by gmt_create desc   limit ?, ? 

select  * from poll where  title like '%'||?||'%'  order by gmt_create desc   limit ?, ?

还能查出结果来！哎！

得用：title CONCAT('%',#title#,'%')

Sql代码

select * from poll  where  title like CONCAT('%',?,'%')  order by gmt_create desc limit ?, ?

呵呵，多次测试均没有发现问题！

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

以下读者注：

是否为：title like CONCAT('%',#title#,'%')