$使用的场景解析

$和#的区别是是原封不动的取出,#会做预编译。会导致sql注入,产生sql安全的问题。但是#就不会。 那么$的使用场景有哪些呢?

动态排序和动态字段查询以及动态sql,比如字段名是动态的或者表名是动态的或者整个sql语句都是动态的!

下面以$做动态字段名查询为例讲解如何使用$。

动态字段名查询

@Override
@Transactional(rollbackFor = Exception.class)
public Boolean delete(PriceTemplateSearchDTO dto) {
    // 查找当前模板是否存在
    LambdaUpdateWrapper<PriceTemplate> wrapper = Wrappers.lambdaUpdate(PriceTemplate.class)
            .eq(PriceTemplate::getId, dto.getId())
            .eq(PriceTemplate::getIsDeleted, IsDeletedTypeEnum.NOT_DELETE);
    PriceTemplate template = getOne(wrapper, false);

    if (EmptyUtil.isEmpty(template)) {
        throw new BusinessException(BusinessErrorEnum.PRICE_TEMPLATE_NOT_EXIST);
    }

    //查询是否有快递柜设置关联了此模板
    String priceIdField = null;
    if (PriceTypeEnum.DISPATCH.equals(template.getType())) {
        priceIdField = "DISPATCH_PRICE_ID";
    } else if (PriceTypeEnum.SEND.equals(template.getType())) {
        priceIdField = "SEND_EXPRESS_PRICE_ID";
    } else if (PriceTypeEnum.STORAGE.equals(template.getType())) {
        priceIdField = "SHORT_STORE_PRICE_ID";
    } else if (PriceTypeEnum.THE_GATE_IS_OVERDUE.equals(template.getType())) {
        priceIdField = "PICK_TIMEOUT_PRICE_ID";
    } else {
        throw new BusinessException(BusinessErrorEnum.UNKNOWN_PRICE_TEMPLATE);
    }
    //查询关联了此模板的快递柜设置
    List<BoxBizSetting> relateSettingList = boxBizSettingService.queryByPriceId(template.getId(), priceIdField);
    if (EmptyUtil.isNotEmpty(relateSettingList)) {
        throw new BusinessException(BusinessErrorEnum.PRICE_TEMPLATE_RELATE_BOX_BIZ_SETTING);
    }
    //删除模板
    wrapper.set(PriceTemplate::getIsDeleted, IsDeletedTypeEnum.DELETED);
    update(wrapper);
    if (PriceTypeEnum.SEND != template.getType()) {
        priceAllocationService.updateReaDeal(dto.getId());
    } else {
        priceAreaCorrelationService.updateReaDeal(dto.getId());
    }
    return true;
}

 select id from box_biz_setting b where ${priceIdField} = #{id} 

使用$符号做动态排序,如何防止sql注入?

package com.yl.jmsth.express.annotation;

import com.baomidou.mybatisplus.annotation.TableField;
import com.yl.tmp.whitecloud.core.api.exception.BusinessException;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.util.concurrent.ConcurrentHashMap;

/**
 *
 * @date: 2022/10/31 17:27
 * @description: order by 排序过滤 防注入
 * @version: 1.0
 */
@Slf4j
public class OrderByColumnCheckUtil {

    public static final String ASC = "ASC";
    public static final String DESC = "DESC";
    public static final String KEY_SPLIT = "-";

    static ConcurrentHashMap<String, Boolean> cacheMap = new ConcurrentHashMap();

    /***
     * 排序规则是否符合ASC或者DESC 不符合抛出异常
     * 返回列是否存在  不存在抛出异常
     * @param clazz
     * @param columnName
     * @return
     */
    public static void checkOrderBy(Class clazz, String columnName, String orderDir) {
        checkOrderDir(orderDir);
        if (EmptyUtil.isEmpty(columnName)) {
            return;
        }
        String key = clazz.getCanonicalName() + KEY_SPLIT + columnName;
        log.info(key);
        Boolean exist = cacheMap.get(key);
        if (EmptyUtil.isNotEmpty(exist)) {
            if (!exist) {
                throw new BusinessException("排序的列不存在!");
            }
        }
        exist = checkColumn(clazz, columnName);
        cacheMap.put(key, exist);
        if (!exist) {
            throw new BusinessException("排序的列不存在!");
        }
    }

    /***
     * 排序规则是否符合ASC或者DESC 不符合抛出异常
     * @param orderDir
     */
    public static void checkOrderDir(String orderDir) {
        if (EmptyUtil.isNotEmpty(orderDir)) {
            if (!DESC.equalsIgnoreCase(orderDir) && !ASC.equalsIgnoreCase(orderDir)) {
                throw new BusinessException("排序规则不存在!");
            }
        }
    }

    /***
     * 返回列是否存在  不存在抛出异常
     * @param clazz
     * @param columnName
     * @return
     */
    public static Boolean checkColumn(Class clazz, String columnName) {
        try {
            Field declaredField = clazz.getDeclaredField(columnName);
            if (EmptyUtil.isNotEmpty(declaredField)) {
                //存在
                return true;
            }
        } catch (NoSuchFieldException e) {
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                if (field.getName().equalsIgnoreCase(columnName)) {
                    return true;
                }
                field.setAccessible(true);
                TableField annotation = field.getAnnotation(TableField.class);
                if (EmptyUtil.isNotEmpty(annotation)) {
                    String value = annotation.value();
                    if (columnName.equalsIgnoreCase(value)) {
                        return true;
                    }
                }
            }
        }

        Class superclass = clazz.getSuperclass();
        if (superclass.equals(Object.class)) {
            return false;
        }
        return checkColumn(superclass, columnName);
    }

    public static void main(String[] args) {
        checkOrderBy(Student.class, "name", null);
    }
}

class Student {
    private String NAME;
}

假设有1个Student的实体,有1个name属性。假如需要根据name动态排序,如果想防止sql注入。

调用checkOrderBy(Student.class, "name", null);即可。

此时会递归查询Student实体以及Student的父类以及父类的父类中是否有name字段,如果没有抛出异常。