使用ArgumentPreparedStatementSetter 防止SQL注入實(shí)測(cè)

代碼展示

Controller

    @ApiOperation(value = "查詢機(jī)構(gòu)列表",notes = "查詢機(jī)構(gòu)列表")
    @RequestMapping(value = "/getList",method = RequestMethod.POST)
    public Map<String,Object> getSpeciqlistInfo(@RequestBody TrainingHospital trainingHospital){
            return speciqlistLeagueSerivce.getSpeciqlistInfo(trainingHospital);
    }

Service接口

public interface ISpeciqlistLeagueSerivce {

    
    /**
     * 獲取機(jī)構(gòu)列表
     * @return
     */
     Map<String,Object> getSpeciqlistInfo(TrainingHospital trainingHospital);
}

Service實(shí)現(xiàn)類(lèi)

@Service//實(shí)現(xiàn)類(lèi)加
public class SpeciqlistLeagueServiceImpl extends BaseService<Object> implements ISpeciqlistLeagueSerivce {
    private Logger logger = LoggerFactory.getLogger(this.getClass());
 /**
     * 查詢醫(yī)院實(shí)現(xiàn)
     */
    @Override
    public Map<String,Object> getSpeciqlistInfo(TrainingHospital trainingHospital) {
        Page<?> pages = null;
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select t.*,count(u.id) - 1 nums  from training_hospital t" +
                    " left JOIN platform_user u on t.id = u.trainingId " +
//                    " left join users u2 on t.id = u2.trainingId" +
                    " where 1=1 and t.hosType = 1 and t.isDel = 0 ");

            if(trainingHospital.getTrainingType() != null){
                sql.append(" and trainingType = ").append(trainingHospital.getTrainingType());
            }
            if(StringUtil.isNotBlank(trainingHospital.getHospitalName())){
                sql.append(" and t.hospitalName like '%"+trainingHospital.getHospitalName()+"%' ");
            }
            if(StringUtil.isNotBlank(trainingHospital.getHospitalNo())){
                sql.append(" and t.hospitalNo = "+trainingHospital.getHospitalNo());
            }
            sql.append(" group by t.id");
            if(trainingHospital.getQueryType() == 0){
                return ReturnUtils.returnOkMsg(jdbcTemplate.queryForList(sql.toString()));
            }else{
                pages = queryMapPage(sql.toString(), trainingHospital.getPageNumber(), trainingHospital.getPageSize());
                return ReturnUtils.returnOkMsg(pages);
            }
        }catch (BusinessException e){
            return ReturnUtils.returnFailMsg(e.getMessage());
        }catch (Exception e){
            logger.info(e.getMessage(),e);
            return ReturnUtils.FAIL();
        }
    }
}

Service基類(lèi)

/***
 * JDBC 基類(lèi)接口
 * @author mxk
 *
 */
@Component
public class BaseService<T>{
    @Autowired
    public JdbcTemplate jdbcTemplate;

    /**
     * 查詢某一頁(yè)數(shù)據(jù)
     * @param sql 帶參數(shù)的SQL 必須包含排序
     * @param PageNum
     * @param pageSize
     * @return
     */
    public Page<?> queryMapPage2(String sql,int PageNum,int pageSize,Object... params) {
        // TODO Auto-generated method stub
        // TODO Auto-generated method stub
        PageNum=(PageNum-1)*pageSize;
        List<Map<String,Object>> objs = new ArrayList<>();
        Page<?> page = null;
        try {
            page = getPageInfo(sql, pageSize,params);
            if (page.getTotal() > 0) {
                sql += " LIMIT "+PageNum+","+pageSize;
                objs = queryForList(sql,params);
            }
            page.setMaps(objs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return page;
    }
    /***
     * 翻頁(yè)信息
     * @param sql
     * @param pageSize
     * @return
     */
    private Page<T> getPageInfo(String sql, int pageSize,Object... params) throws Exception {
        Page<T> page = new Page<T>();
        try {
            int count =  selectCount("select count(1) as count from (" + sql +") t",params);

            if (count > 0) {
                page.setTotal(count);
                page.setSize(pageSize);
                Double p = MathUtil.divide(count, pageSize);

                int pages = 0;
                if (p < 1 && p > 0) {
                    pages = 1;
                } else {
                    //向上取整
                    pages = (int) Math.ceil(p);
                }
                page.setPages(pages);
            }
        } catch (Exception e) {
            throw e;
        }
        return page;
    }

    /***
     * 查詢數(shù)據(jù)條目
     * @param sql
     * @return
     */
    public int selectCount(String sql,Object... params) throws Exception {
        // TODO Auto-generated method stub
        int count = 0;
        SqlRowSet set;
        try {
            set = jdbcTemplate.queryForRowSet(sql,params);
            if (set.next()) {
                count = set.getInt("count");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }
 /**
     * 將查詢字段映射到實(shí)體類(lèi)
     * @param sql 查詢語(yǔ)句
     * @param params 傳入?yún)?shù)
     * @return
     */
    public  List<Map<String,Object>> queryForList(String sql, Object... params) {
        final List<Map<String,Object>> result = new ArrayList<>();
        jdbcTemplate.query(sql, params, rs -> {
            try {
                // 字段名稱
                List<String> columnNames = new ArrayList<>();
                ResultSetMetaData meta = rs.getMetaData();
                int num = meta.getColumnCount();
                for (int i = 0; i < num; i++) {
                    columnNames.add(meta.getColumnLabel(i + 1));
                }
                // 設(shè)置值
                do {
                    Map<String,Object> resultMap = new HashMap<>();
                    for (int i = 0; i < num; i++) {
                        // 獲取值
                        Object value = rs.getObject(i + 1);
                        // table.column形式的字段去掉前綴table.
                        String columnName = resolveColumn(columnNames.get(i));
                        // 下劃線轉(zhuǎn)駝峰
                        String property = CamelCaseUtils.toCamelCase(columnName);
                        // 復(fù)制值到屬性，這是spring的工具類(lèi)
                        resultMap.put(property,value);
                    }
                    result.add(resultMap);
                } while (rs.next());
            } catch (Exception e) {
                throw new QueryException(e);
            }
        });
        if (result.isEmpty()) {
            return Collections.emptyList();
        }
        return result;
    }
  
    /**
     * 用來(lái)去掉字段的表前綴，比如t.id替換成id
     * @param column 查詢的帶有前綴的返回字段
     * @return 不帶前綴的字段
     */
    private String resolveColumn(String column) {
        final int notExistIndex = -1;
        int index = column.indexOf(".");
        if (index == notExistIndex) {
            return column;
        }
        return column.substring(index + 1);
    }

}

情況一：使用參數(shù)拼接傳參

參數(shù)展示

{
"hospitalNo": "15139411539 or 1=1",
"pageNumber": 1,
"pageSize": 10
}

返回結(jié)果

{
    "data": {
        "records": [],
        "maps": [
            {
                "id": 3,
                "hospitalName": "東華社區(qū)",
                "hospitalNo": "dhsq",
                "hosType": 1,
                "trainingType": 1,
                "address": "222",
                "tel": "123",
                "level": "1",
                "hosDesc": null,
                "initTime": "2021-11-04T16:00:00.000Z",
                "isDel": 1,
                "hospitalOneLevelNo": "01",
                "hospitalTwoLevelNo": null,
                "isShow": 0,
                "miniImage": null,
                "lon": null,
                "lat": null,
                "contacts": null,
                "sysUuid": "f400d7efb48b334ee2d77b1ef448d15a",
                "province": null,
                "city": null,
                "district": null,
                "message": null,
                "title": null,
                "nums": -1
            },
            {
                "id": 4,
                "hospitalName": "測(cè)試",
                "hospitalNo": "00002",
                "hosType": 1,
                "trainingType": 1,
                "address": "",
                "tel": "13120898293",
                "level": "",
                "hosDesc": null,
                "initTime": "",
                "isDel": 1,
                "hospitalOneLevelNo": "01",
                "hospitalTwoLevelNo": null,
                "isShow": 0,
                "miniImage": null,
                "lon": null,
                "lat": null,
                "contacts": null,
                "sysUuid": "db7282901da891bea2fcd73840caad1c",
                "province": null,
                "city": null,
                "district": null,
                "message": null,
                "title": null,
                "nums": -1
            },
            {
                "id": 5,
                "hospitalName": "測(cè)試測(cè)試",
                "hospitalNo": "00003",
                "hosType": 1,
                "trainingType": 1,
                "address": "",
                "tel": "13569508142",
                "level": "1",
                "hosDesc": null,
                "initTime": "2021-11-09T16:00:00.000Z",
                "isDel": 1,
                "hospitalOneLevelNo": "01",
                "hospitalTwoLevelNo": null,
                "isShow": 0,
                "miniImage": null,
                "lon": null,
                "lat": null,
                "contacts": null,
                "sysUuid": "e08599ba76fe6afa2b5786d857b0f56e",
                "province": null,
                "city": null,
                "district": null,
                "message": null,
                "title": null,
                "nums": -1
            },
            {
                "id": 7,
                "hospitalName": "測(cè)試機(jī)構(gòu)1",
                "hospitalNo": "10000",
                "hosType": 1,
                "trainingType": 1,
                "address": "打發(fā)點(diǎn)",
                "tel": "15890997318",
                "level": "",
                "hosDesc": null,
                "initTime": "2021-10-31T16:00:00.000Z",
                "isDel": 1,
                "hospitalOneLevelNo": "01",
                "hospitalTwoLevelNo": null,
                "isShow": 0,
                "miniImage": null,
                "lon": null,
                "lat": null,
                "contacts": null,
                "sysUuid": "b7a782741f667201b54880c925faec4b",
                "province": null,
                "city": null,
                "district": null,
                "message": null,
                "title": null,
                "nums": -1
            },
            {
                "id": 8,
                "hospitalName": "test",
                "hospitalNo": "0000",
                "hosType": 1,
                "trainingType": 1,
                "address": "",
                "tel": "13120898293",
                "level": "",
                "hosDesc": null,
                "initTime": "",
                "isDel": 1,
                "hospitalOneLevelNo": "01",
                "hospitalTwoLevelNo": "12411700573582781E",
                "isShow": 0,
                "miniImage": null,
                "lon": null,
                "lat": null,
                "contacts": null,
                "sysUuid": "4a7d1ed414474e4033ac29ccb8653d9b",
                "province": null,
                "city": null,
                "district": null,
                "message": null,
                "title": null,
                "nums": -1
            },
            {
                "id": 9,
                "hospitalName": "人和社區(qū)衛(wèi)生服務(wù)中心",
                "hospitalNo": "15139411539",
                "hosType": 1,
                "trainingType": 1,
                "address": "河南省周口市川匯區(qū)小橋街道匯聚路周口市第三人民醫(yī)院",
                "tel": "15139411539",
                "level": "",
                "hosDesc": null,
                "initTime": "2021-11-07T16:00:00.000Z",
                "isDel": 0,
                "hospitalOneLevelNo": "01",
                "hospitalTwoLevelNo": null,
                "isShow": 0,
                "miniImage": "http://qrcode-1308122820.cos.ap-chengdu.myqcloud.com/1640911660156-9.png",
                "lon": "114.649887",
                "lat": "33.650224",
                "contacts": null,
                "sysUuid": "dc33c3ab197bbaf6c410a271cc0113aa",
                "province": "河南省",
                "city": "周口市",
                "district": "川匯區(qū)",
                "message": null,
                "title": null,
                "nums": 0
            },
            {
                "id": 10,
                "hospitalName": "金海社區(qū)衛(wèi)生服務(wù)中心",
                "hospitalNo": "13592276989",
                "hosType": 1,
                "trainingType": 1,
                "address": "河南省周口市川匯區(qū)建設(shè)大道文博苑東南側(cè)約80米",
                "tel": "13592276989",
                "level": "",
                "hosDesc": null,
                "initTime": "2021-11-07T16:00:00.000Z",
                "isDel": 0,
                "hospitalOneLevelNo": "01",
                "hospitalTwoLevelNo": null,
                "isShow": 0,
                "miniImage": "http://qrcode-1308122820.cos.ap-chengdu.myqcloud.com/1642240171086-10.png",
                "lon": "114.63214",
                "lat": "33.63708",
                "contacts": null,
                "sysUuid": "d4d37975a874ccb96450472cf594c275",
                "province": "河南省",
                "city": "周口市",
                "district": "川匯區(qū)",
                "message": "query ok",
                "title": "金海辦事處社區(qū)衛(wèi)生中心",
                "nums": 0
            },
            {
                "id": 11,
                "hospitalName": "陳州社區(qū)衛(wèi)生服務(wù)中心",
                "hospitalNo": "13703947929",
                "hosType": 1,
                "trainingType": 1,
                "address": "河南省周口市川匯區(qū)人民路與中州大道交叉口東北",
                "tel": "13703947929",
                "level": "",
                "hosDesc": null,
                "initTime": "2021-11-07T16:00:00.000Z",
                "isDel": 0,
                "hospitalOneLevelNo": "01",
                "hospitalTwoLevelNo": null,
                "isShow": 0,
                "miniImage": null,
                "lon": "114.645461",
                "lat": "33.621311",
                "contacts": null,
                "sysUuid": "d0185c531fb3c654636b44925376f98b",
                "province": "河南省",
                "city": "周口市",
                "district": "川匯區(qū)",
                "message": "query ok",
                "title": "陳州社區(qū)衛(wèi)生服務(wù)中心",
                "nums": 0
            },
            {
                "id": 12,
                "hospitalName": "川匯區(qū)婦幼保健院",
                "hospitalNo": "13033945353",
                "hosType": 1,
                "trainingType": 1,
                "address": "河南省周口市川匯區(qū)中州大道",
                "tel": "13033945353",
                "level": "",
                "hosDesc": null,
                "initTime": "2021-11-07T16:00:00.000Z",
                "isDel": 0,
                "hospitalOneLevelNo": "01",
                "hospitalTwoLevelNo": null,
                "isShow": 0,
                "miniImage": null,
                "lon": "114.643442",
                "lat": "33.624322",
                "contacts": null,
                "sysUuid": "479aea1593a8d392f1edfbd825616e85",
                "province": "河南省",
                "city": "周口市",
                "district": "川匯區(qū)",
                "message": "query ok",
                "title": "周口市川匯區(qū)婦幼保健院",
                "nums": 0
            },
            {
                "id": 13,
                "hospitalName": "小喬社區(qū)衛(wèi)生服務(wù)中心",
                "hospitalNo": "13592265853",
                "hosType": 1,
                "trainingType": 1,
                "address": "河南省周口市川匯區(qū)八一大道希望小區(qū)興業(yè)路西南側(cè)約280米",
                "tel": "13592265853",
                "level": "",
                "hosDesc": null,
                "initTime": "2021-11-07T16:00:00.000Z",
                "isDel": 0,
                "hospitalOneLevelNo": "01",
                "hospitalTwoLevelNo": null,
                "isShow": 0,
                "miniImage": null,
                "lon": "114.656622",
                "lat": "33.640067",
                "contacts": null,
                "sysUuid": "2cbab6389e1607371921af8f6347b69d",
                "province": "河南省",
                "city": "周口市",
                "district": "川匯區(qū)",
                "message": "query ok",
                "title": "小橋社區(qū)衛(wèi)生服務(wù)中心",
                "nums": 0
            }
        ],
        "recordArray": null,
        "statis": null,
        "total": 547,
        "pages": 55,
        "size": 10,
        "current": 1,
        "msg": null,
        "http": null,
        "ok": false
    },
    "retCode": "0",
    "timestamp": 1658641836675
}

情況二：使用可變數(shù)組傳參

修改Service實(shí)現(xiàn)類(lèi)

@Service//實(shí)現(xiàn)類(lèi)加
public class SpeciqlistLeagueServiceImpl extends BaseService<Object> implements ISpeciqlistLeagueSerivce {
    private Logger logger = LoggerFactory.getLogger(this.getClass());

/**
     * 查詢醫(yī)院實(shí)現(xiàn)
     */
    @Override
    public Map<String,Object> getSpeciqlistInfo(TrainingHospital trainingHospital) {
        Page<?> pages = null;
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select t.*,count(u.id) - 1 nums  from training_hospital t" +
                    " left JOIN platform_user u on t.id = u.trainingId " +
//                    " left join users u2 on t.id = u2.trainingId" +
                    " where 1=1 and t.hosType = 1 and t.isDel = 0 ");
            List<Object> list = new ArrayList<>();
            if(trainingHospital.getTrainingType() != null){
//                sql.append(" and trainingType = ").append(trainingHospital.getTrainingType());
                sql.append(" and trainingType = ").append("?");
                list.add(trainingHospital.getHosType());
            }
            if(StringUtil.isNotBlank(trainingHospital.getHospitalName())){
//                sql.append(" and t.hospitalName like '%"+trainingHospital.getHospitalName()+"%' ");
                sql.append(" and t.hospitalName like ? ");
                list.add("%"+trainingHospital.getHospitalName()+"%");
            }
            if(StringUtil.isNotBlank(trainingHospital.getHospitalNo())){
//                sql.append(" and t.hospitalNo = "+trainingHospital.getHospitalNo()+" ");
                sql.append(" and t.hospitalNo = ? ");
                list.add(trainingHospital.getHospitalNo());
            }
            sql.append(" group by t.id");
            if(trainingHospital.getQueryType() == 0){
                return ReturnUtils.returnOkMsg(jdbcTemplate.queryForList(sql.toString()));
            }else{
                pages = queryMapPage2(sql.toString(), trainingHospital.getPageNumber(), trainingHospital.getPageSize(),list.toArray());
                return ReturnUtils.returnOkMsg(pages);
            }
        }catch (BusinessException e){
            return ReturnUtils.returnFailMsg(e.getMessage());
        }catch (Exception e){
            logger.info(e.getMessage(),e);
            return ReturnUtils.FAIL();
        }
    }
}

參數(shù)展示

{
"hospitalNo": "15139411539 or 1=1",
"pageNumber": 1,
"pageSize": 10
}

返回結(jié)果

{
"data": {
"records": [],
"maps": [],
"recordArray": null,
"statis": null,
"total": 0,
"pages": 0,
"size": 10,
"current": 1,
"msg": null,
"http": null,
"ok": false
},
"retCode": "0",
"timestamp": 1658640578006
}

總結(jié)

使用可變數(shù)組傳參可以防止sql注入，使用拼接傳參不會(huì)防止sql注入！

原因分析詳見(jiàn)：https://blog.csdn.net/Acx77/article/details/121856595

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容