項(xiàng)目場(chǎng)景

項(xiàng)目需求需要在一個(gè)框架上二開，原框架有權(quán)限校驗(yàn)會(huì)返回對(duì)應(yīng)的權(quán)限sql語句。

例如：

在獲取用戶當(dāng)前組織及下級(jí)組織后拼接生成的SQL：

AND (
    settlement_company_id IN ( 
        '06eb1b4ce4d24e368319188bb4fc6390', 
        '9ff034739e984f188612c671219ddfed', 
        'afe69288c0b84828bd2abcb1c5eec28a' 
    ) 
)

因?yàn)橛惺褂玫疥P(guān)聯(lián)查詢，無法直接使用QueryWrapper的apply來拼接語句，只能在xml中拼接。 

問題描述

查詢傳入實(shí)體：

@Data
public class YygkBankAccountQueryForm {
        /**
         * 數(shù)據(jù)權(quán)限Sql
         */
        @ApiModelProperty(value = "數(shù)據(jù)權(quán)限Sql")
        private String extraSql;
}

Mapper類：

public interface YygkBankAccountMapper extends BaseMapper<YygkBankAccountEntity> {
    /**
     * 分頁查詢銀行賬戶
     *
     * @param iPage                 分頁數(shù)據(jù)
     * @param yygkBankInfoQueryForm 查詢參數(shù)
     * @return IPage<YygkBankAccountEntity>
     */
    IPage<YygkBankAccountEntity> queryPage(IPage<YygkBankAccountEntity> iPage, @Param("query") YygkBankAccountQueryForm yygkBankInfoQueryForm);
}

XML的查詢SQL語句：

<select id="queryPage" resultMap="BaseMap">
    SELECT
    <include refid="YygkBankAccountSql"/>
    FROM yygk_bank_account ba
    LEFT JOIN yygk_currency_info ci ON ba.currency_id = ci.id
    <where>
        ba.delete_mark != 1
        <if test="query.extraSql != null and query.extraSql != ''">
            AND #{query.extraSql}
        </if>
    </where>
    ORDER BY ba.sort DESC
</select>

query.extraSql為我需要拼接的SQL語句。

執(zhí)行查詢的打印日志：

2022-06-17 18:59:55.981 DEBUG 16768 --- [io-30601-exec-1] c.g.y.m.Y.queryPage_mpCount              : ==>  Preparing: SELECT COUNT(1) FROM yygk_bank_account ba WHERE ba.delete_mark != 1 AND ?
2022-06-17 18:59:56.088 DEBUG 16768 --- [io-30601-exec-1] c.g.y.m.Y.queryPage_mpCount              : ==> Parameters:  (settlement_company_id in( '06eb1b4ce4d24e368319188bb4fc6390','9ff034739e984f188612c671219ddfed','afe69288c0b84828bd2abcb1c5eec28a' ) )(String)
2022-06-17 18:59:56.174 DEBUG 16768 --- [io-30601-exec-1] c.g.y.m.Y.queryPage_mpCount              : <==      Total: 1

可以看到在執(zhí)行了mybatis-plus的count查詢后并沒有再執(zhí)行查詢數(shù)據(jù)

原因分析

懷疑是要用SQL注入才能拼接SQL語句

解決方案

xml的SQL語句改為使用SQL注入：

<select id="queryPage" resultMap="BaseMap">
    SELECT
    <include refid="YygkBankAccountSql"/>
    FROM yygk_bank_account ba
    LEFT JOIN yygk_currency_info ci ON ba.currency_id = ci.id
    <where>
        ba.delete_mark != 1
        <if test="query.extraSql != null and query.extraSql != ''">
            AND ${query.extraSql}
        </if>
    </where>
    ORDER BY ba.sort DESC
</select>

將#{}替換成${}

2022-06-17 19:21:05.437 DEBUG 9008 --- [io-30601-exec-2] c.g.y.m.Y.queryPage_mpCount              : ==>  Preparing: SELECT COUNT(1) FROM yygk_bank_account ba WHERE ba.delete_mark != 1 AND (settlement_company_id IN ('06eb1b4ce4d24e368319188bb4fc6390', '9ff034739e984f188612c671219ddfed', 'afe69288c0b84828bd2abcb1c5eec28a'))
2022-06-17 19:21:05.523 DEBUG 9008 --- [io-30601-exec-2] c.g.y.m.Y.queryPage_mpCount              : ==> Parameters: 
2022-06-17 19:21:05.598 DEBUG 9008 --- [io-30601-exec-2] c.g.y.m.Y.queryPage_mpCount              : <==      Total: 1
2022-06-17 19:21:05.630 DEBUG 9008 --- [io-30601-exec-2] c.g.y.m.YygkBankAccountMapper.queryPage  : ==>  Preparing: SELECT ci.currency_code as currency_code, ba.id, ba.bank_id, ba.bank_name, ba.bank_short_name, ba.bank_english_name, ba.swift_code, ba.account_name, ba.sort, ba.account_number, ba.receipts_payment_type, ba.internal_account, ba.settlement_type, ba.account_type, ba.deposit_type, ba.settlement_company_id, ba.settlement_company_name, ba.default_mark, ba.currency_id, ba.currency_code, ba.account_identification, ba.opening_bank_identification, ba.billing_account, ba.remark, ba.creator_time, ba.creator_user_id, ba.creator_user, ba.last_modify_time, ba.last_modify_user_id, ba.last_modify_user, ba.enable_mark, ba.delete_time, ba.delete_user_id, ba.delete_mark FROM yygk_bank_account ba LEFT JOIN yygk_currency_info ci ON ba.currency_id = ci.id WHERE ba.delete_mark != 1 AND (settlement_company_id in( '06eb1b4ce4d24e368319188bb4fc6390','9ff034739e984f188612c671219ddfed','afe69288c0b84828bd2abcb1c5eec28a' ) ) ORDER BY ba.sort DESC LIMIT ?
2022-06-17 19:21:05.635 DEBUG 9008 --- [io-30601-exec-2] c.g.y.m.YygkBankAccountMapper.queryPage  : ==> Parameters: 20(Long)
2022-06-17 19:21:05.651 DEBUG 9008 --- [io-30601-exec-2] c.g.y.m.YygkBankAccountMapper.queryPage  : <==      Total: 2

可以看到在執(zhí)行了mybatis-plus的count查詢后，繼續(xù)執(zhí)行了查詢數(shù)據(jù)語句。

雖然問題解決，但我還是不理解為何將#{}換成${}就能成功。。。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論