SpringBoot 开发过程中遇到的坑0001 -- syntax error at or near “$1“

org.postgresql.util.PSQLException: ERROR: syntax error at or near “$1“的解决办法

在mapper.xml的SQL语句中，通过使用动态变量的方式传入参数,如下所示，通果参数type、threshold来控制不同的SQL语句条件

<select id="count" resultType="java.lang.Integer">
        select count(1) from tpd_alert ta
        <where>
            del_flag = '0'
            <if test="date != '' and date != null">
                and to_char( occur_time, 'yyyy-mm' ) = #{date}
            </if>
            <if test="type == '完成'">
                and finish_time is not null
            </if>
            <if test="type == '误报'">
                and is_fake = '1'
            </if>
            <if test="type == '及时完成'">
                and to_char( occur_time,'yyyy-mm-dd hh24:mm:ss') >= to_char(finish_time - interval  #{threshold},'yyyy-mm-dd hh24:mm:ss')
            </if>
            <if test="type == '逾期'">
                and to_char( finish_time -  interval  #{threshold},'yyyy-mm-dd hh24:mm:ss') > to_char(occur_time ,'yyyy-mm-dd hh24:mm:ss')
            </if>
        </where>
    </select>

但是当运行的时候,当type = ”及时完成“时，虽然控制台打印的SQL如下，可以在SQL查询中运行

SELECT count(1) FROM tpd_alert WHERE del_flag = '0' AND to_char(finish_time - INTERVAL '3' hour, 'yyyy-mm-dd hh24:mm:ss') > to_char(occur_time, 'yyyy-mm-dd hh24:mm:ss') AND tenant_id = '000000'

但是仍然会出下如下的错误告警

org.postgresql.util.PSQLException: ERROR: syntax error at or near “$1“

通过各种排查，发现是SQL在注入过程中，Integer和String之间的转换出现了问题

解决方法有两种

1、不安全的解决方法： 将#{threshold} 替换为  ${threshold} ，这样虽然可以解决当前的问题，但是$无法防止SQL注入的问题
2、更安全的解决方法：将 interval  #{threshold}  替换为  #{threshold}::interval  
替换后的SQL	语句如下
<select id="count" resultType="java.lang.Integer">
        select count(1) from tpd_alert ta
        <where>
            del_flag = '0'
            <if test="date != '' and date != null">
                and to_char( occur_time, 'yyyy-mm' ) = #{date}
            </if>
            <if test="type == '完成'">
                and finish_time is not null
            </if>
            <if test="type == '误报'">
                and is_fake = '1'
            </if>
            <if test="type == '及时完成'">
                and to_char( occur_time,'yyyy-mm-dd hh24:mm:ss') >= to_char(finish_time - #{threshold}::interval,'yyyy-mm-dd hh24:mm:ss')
            </if>
            <if test="type == '逾期'">
                and to_char( finish_time -  #{threshold}::interval,'yyyy-mm-dd hh24:mm:ss') > to_char(occur_time ,'yyyy-mm-dd hh24:mm:ss')
            </if>
        </where>
    </select>

问题解决

Tips：

1、yyyy-mm-dd hh24:mm:ss 与 yyyy-mm-dd hh:mm:ss 是有差别的，前者描述的小时是24小时制的时间，后者就是12小时制的，在SQL中需要对时间进行范围计算的时候需要注意这点
或者也可以不使用泛型模板，直接使用 finish_time -3 hour  也是正确的。