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

我就是我 2022-05-15 07:05 329阅读 0赞

**细节决定成败**

今天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

![70][]

这是报错的链接：
    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语句

![70 1][]

运行成功

![70 2][]

使用成功链接访问

![70 3][]

于是想到比对两个链接：

上为成功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

执行语句查询成功

[70]: /images/20220515/861ea5105beb4e299f53d63eba15ef0d.png
[70 1]: /images/20220515/0c53730d1abf4c39b9e757e543d32ad3.png
[70 2]: /images/20220515/9e0c050a3a234936a7afd66aefb37440.png
[70 3]: /images/20220515/134c6d0b36d642e2b0d74bb01958da14.png