在PostgreSQL中通过MyBatis添加自定义列遇SQL生成异常求助

我的Mapper XML代码 #

<select id = "getVideoOrgTreeChannels" resultMap="orgTreeChannels">
    select so.id id, so.company_name, so.company_code,
    ${sysCode} as sysCode,
    ${plateNo} as plateNo
    from sys_organization so
    where so.id=#{companyId}
    and so.company_status='0'
</select>

错误日志 #

SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2a7ed399] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@700c3c28] will not be managed by Spring
==>  Preparing: select so.id id, so.company_name, so.company_code, as sysCode, as plateNo from sys_organization so where so.id=? and so.company_status='0' 
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2a7ed399]
2023-01-31 15:59:20.549 [http-nio-8081-exec-4] ERROR c.g.boot.common.exception.GciBootExceptionHandler:55 - ### Error querying database.  Cause: java.sql.SQLException: sql injection violation, syntax error: ERROR. pos 62, line 1, column 51, token AS : select so.id id, so.company_name, so.company_code,         as sysCode,         as plateNo        from sys_organization so        where so.id=?        and so.company_status='0'
### The error may exist in file [D:\workProject\gci_video_3\webSite\src\gci-video-web-service\gci-video-web\gci-boot-video-manage\target\classes\com\gci\boot\modules\video\mapper\xml\VideoBusinessDataMapper.xml]
### The error may involve com.gci.boot.modules.video.mapper.VideoBusinessDataMapper.getVideoOrgTreeChannels
### The error occurred while executing a query
### SQL: select so.id id, so.company_name, so.company_code,          as sysCode,          as plateNo         from sys_organization so         where so.id=?         and so.company_status='0'
### Cause: java.sql.SQLException: sql injection violation, syntax error: ERROR. pos 62, line 1, column 51, token AS : select so.id id, so.company_name, so.company_code,         as sysCode,         as plateNo        from sys_organization so        where so.id=?        and so.company_status='0'; uncategorized SQLException; SQL state [null]; error code [0]; sql injection violation, syntax error: ERROR. pos 62, line 1, column 51, token AS : select so.id id, so.company_name, so.company_code,         as sysCode,         as plateNo        from sys_organization so        where so.id=?        and so.company_status='0'; nested exception is java.sql.SQLException: sql injection violation, syntax error: ERROR. pos 62, line 1, column 51, token AS : select so.id id, so.company_name, so.company_code,         as sysCode,         as plateNo        from sys_organization so        where so.id=?        and so.company_status='0'

数据库直接执行成功的示例 #

示例SQL #

select so.id as isd, so.company_name, so.company_code ,'syscode' as title from sys_organization so;

执行结果 #

isd                |company_name|company_code|title  |
-------------------+------------+------------+-------+
1613079881513832449|测试公司        |A01         |syscode|
1613079972102410241|开发一部        |A01A01      |syscode|
1613080029140750337|开发二部        |A01A02      |syscode|
1613080075039019009|交通开发公司      |A02         |syscode|
1613089530539556865|技术一部        |A02A01      |syscode|
1613089567768199170|技术二部        |A02A02      |syscode|
1613089659170471937|技术三部        |A02A03      |syscode|

问题原因及解决办法 #

原因 #

从错误日志生成的SQL可以看到，${sysCode}和${plateNo}被替换为空值，导致出现, as sysCode这种非法语法。对比成功的示例SQL，自定义列是带单引号的常量值，说明要么传入的参数为空，要么没有给字符串常量添加单引号。

解决办法 #

1. 给字符串常量添加单引号
   如果sysCode和plateNo是字符串常量，修改Mapper XML，在${}外层加上单引号，确保生成合法的SQL：

   <select id="getVideoOrgTreeChannels" resultMap="orgTreeChannels">
       select so.id id, so.company_name, so.company_code,
       '${sysCode}' as sysCode,
       '${plateNo}' as plateNo
       from sys_organization so
       where so.id=#{companyId}
       and so.company_status='0'
   </select>
   

2. 处理参数为空的情况
   如果参数可能为空，使用MyBatis的<if>标签判断，避免生成多余的逗号和无效列：

   <select id="getVideoOrgTreeChannels" resultMap="orgTreeChannels">
       select so.id id, so.company_name, so.company_code
       <if test="sysCode != null and sysCode != ''">
           , '${sysCode}' as sysCode
       </if>
       <if test="plateNo != null and plateNo != ''">
           , '${plateNo}' as plateNo
       </if>
       from sys_organization so
       where so.id=#{companyId}
       and so.company_status='0'
   </select>
   

3. 规避SQL注入风险
   使用${}存在SQL注入风险，如果参数来自用户输入，建议改用#{}结合SQL常量处理，或者对参数进行严格的合法性校验。

内容的提问来源于stack exchange，提问作者qian