Java学习-066-Mybatis + druid 报错： com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'code LIKE "%"?"%"

 查询数据库时，报错信息如下所示： 

 1 ### Error querying database.  Cause: java.sql.SQLException: sql injection violation, syntax error: syntax error, error in :'code LIKE "%"?"%"
 2          
 3        ', expect RPAREN, actual QUES pos 103, line 3, column 31, token QUES : select count(0) from (SELECT * FROM api_case WHERE delflag = 0
 4          
 5             AND code LIKE "%"?"%"
 6          
 7          
 8          
 9          
10             AND valid = ?) tmp_count
11 ### The error may exist in file [E:\office\script\jcca-dtops\jcca-dtops-admin\target\classes\mybatis\mapper\api\ApiCaseMapper.xml]
12 ### The error may involve com.jcca.mapper.api.ApiCaseMapper.findAllByConditions_COUNT
13 ### The error occurred while executing a query
14 ### SQL: select count(0) from (SELECT * FROM api_case WHERE delflag = 0                       AND code LIKE "%"?"%"                                                     AND valid = ?) tmp_count
15 ### Cause: java.sql.SQLException: sql injection violation, syntax error: syntax error, error in :'code LIKE "%"?"%"
16          
17        ', expect RPAREN, actual QUES pos 103, line 3, column 31, token QUES : select count(0) from (SELECT * FROM api_case WHERE delflag = 0
18          
19             AND code LIKE "%"?"%"
20          
21          
22          
23          
24             AND valid = ?) tmp_count
25 ; uncategorized SQLException; SQL state [null]; error code [0]; sql injection violation, syntax error: syntax error, error in :'code LIKE "%"?"%"
26          
27        ', expect RPAREN, actual QUES pos 103, line 3, column 31, token QUES : select count(0) from (SELECT * FROM api_case WHERE delflag = 0
28          
29             AND code LIKE "%"?"%"
30          
31          
32          
33          
34             AND valid = ?) tmp_count; nested exception is java.sql.SQLException: sql injection violation, syntax error: syntax error, error in :'code LIKE "%"?"%"
35          
36        ', expect RPAREN, actual QUES pos 103, line 3, column 31, token QUES : select count(0) from (SELECT * FROM api_case WHERE delflag = 0
37          
38             AND code LIKE "%"?"%"
39          
40          
41          
42          
43             AND valid = ?) tmp_count] with root cause
44 com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'code LIKE "%"?"%"
45          
46        ', expect RPAREN, actual QUES pos 103, line 3, column 31, token QUES
47     at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:287)
48     at com.alibaba.druid.sql.parser.SQLParser.accept(SQLParser.java:295)
49     at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.parseTableSource(MySqlSelectParser.java:248)
50     at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.parseFrom(MySqlSelectParser.java:89)
51     at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:193)
52     at com.alibaba.druid.sql.parser.SQLSelectParser.query(SQLSelectParser.java:236)
53     at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:88)
54     at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseSelect(MySqlStatementParser.java:284)
55     at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:248)
56     at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:182)
57     at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:624)
58     at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:578)
59     at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:793)
60     at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:259)
61     at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
62     at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930)
63     at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122)
64     at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
65     at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341)
66     at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:349)

 

查看相应的Mapper，相应的查询语句如下所示：

    <select id="findAllByConditions" resultType="com.example.TestCase">
        SELECT * FROM test_case WHERE delflag = 0
        <if test="code != '' and code != null">
            AND code LIKE "%"#{code}"%"
        </if>
        <if test="name != '' and name != null">
            AND name LIKE "%"#{name}"%"
        </if>
        <if test="url != '' and url != null">
            AND url LIKE "%"#{url}"%"
        </if>
        <if test="valid != '' and valid != null">
            AND valid = #{valid}
        </if>
    </select>

 

发现是参数变量引用有误，修改mapper信息，改为如下所示的引用方式即可。

    <select id="findAllByConditions" resultType="com.example.TestCase">
        SELECT * FROM test_case WHERE delflag = 0
        <if test="code != '' and code != null">
            AND code LIKE '%${code}%'
        </if>
        <if test="name != '' and name != null">
            AND name LIKE '%${name}%'
        </if>
        <if test="url != '' and url != null">
            AND url LIKE '%${url}%'
        </if>
        <if test="valid != '' and valid != null">
            AND valid = #{valid}
        </if>
    </select>

 

修改后的方式为'%${code}%'，或者使用concat函数拼接参数也可以，示例：CONCAT(CONCAT('%',#{code}), '%')

 

posted @ 2020-11-29 16:37  范丰平  Views(6220)  Comments(0)  Edit  收藏  举报