使用url接拼接SQL模糊查询报错

细节决定成败

今天rn使用url拼接SQL语句查询信息，报错

Whitelabel Error Page
This application has no explicit mapping for /error, so you are seeing this as a fallback.

Sun Aug 26 20:15:56 CST 2018
There was an unexpected error (type=Internal Server Error, status=500).
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Query was empty ### The error may exist in URL [jar:file:/java/saleApp-0.0.1-SNAPSHOT.jar!/BOOT-INF/classes!/mapper/PublicMapper.xml] ### The error may involve com.yonyou.cc.app.mapper.BaseMapper.getObject-Inline ### The error occurred while setting parameters ### SQL: ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Query was empty ; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Query was empty

 

这是报错的链接：
url:http://localhost:12040/BaseSQL?sql=select * from ordercontent where customer_telephone like '%11%' or customer_name like '%11%' limit 0,20 &token=101d932a7747326d2ad6695891a350b4&refToken=b51b2dd576644023e519485fb3a443b4

图片中SQL报错，我将SQL语句直接执行时，发现运行正常

于是我使用swagger2直接插入SQL语句

运行成功

使用成功链接访问

于是想到比对两个链接：

上为成功url，下为失败url
http://localhost:12040/BaseSQL?sql=select%20*%20from%20ordercontent%20where%20customer_telephone%20like%20'%2512%25'%20or%20customer_name%20like%20'%2512%25'%20limit%200%2C20%20&token=67595cb8b2476cb37b5e16200faa0589&reftoken=dcb2c5f1903a7c8e886e4e35dffc325e
http://localhost:12040/BaseSQL?sql=select%20*%20from%20ordercontent%20where%20customer_telephone%20like%20'%12%'%20or%20customer_name%20like%20'%12%'%20limit%200%2C20%20&token=67595cb8b2476cb37b5e16200faa0589&reftoken=dcb2c5f1903a7c8e886e4e35dffc325e

经对比发现

'%2512%25'
'%12%'

%后无字符 25 

查询得知

1. +   ============》 %2B 
2. 空格 ===========》 %20 
3. /   ============》 %2F  
4. ?   ============》 %3F  
5. %   ============》 %25  
6. #   ============》 %23  
7. &   ============》 %26  
8. =   ============》 %3D 

 

故在SQL拼接时,%后接上25
http://localhost:12040/BaseSQL?sql=select * from ordercontent where customer_telephone like '%25"+info+"%25' or customer_name like '%25"+info+"%25' limit 0,20 &token=101d932a7747326d2ad6695891a350b4&refToken=b51b2dd576644023e519485fb3a443b4

执行语句查询成功