注意:需要手动校验sql中参数,防止sql注入
1. 注解方式
Mapper接口
@Mapper
public interface PublicSqlMapper {
/**
* 通用查询
* @return
*/
@Select("${sql}")
List<LinkedHashMap<String, Object>> select(Map<String, Object> map);
/**
* 新增
* @param map
* @return
*/
@Insert("${sql}")
int insert(Map<String, Object> map);
/**
* 修改
* @param map
* @return
*/
@Update("${sql}")
int update(Map<String, Object> map);
/**
* 删除
* @param map
* @return
*/
@Delete("${sql}")
int delete(Map<String, Object> map);
}
Controller
@Validated
@RequestMapping("/action")
@RestController
public class ActionController {
@Autowired
private PublicSqlMapper publicSqlMapper;
/**
* 执行sql查询
* @return
*/
@PostMapping("/selectBySql")
public RetResult<List<Map<String, Object>>> selectBySql(@RequestBody Map<String, Object> map){
return RetModel.ok().setData(publicSqlMapper.select(map));
}
}
数据
上送
{
"sql": "select * from article where author=#{author} and title like concat('%', #{title}, '%')",
"author": "zs",
"title": "领导"
}
返回
{
"retCode": "0000",
"message": "请求成功",
"result": [
{
"id": 2,
"title": "分区领导视察",
"sub_title": null,
"author": "zs",
"content": "慰问员工",
"department_id": 1,
"check_status": "0",
"create_time": "2021-04-30 12:00:00",
"update_time": "2021-04-21 22:04:44"
}
]
}
2. 防止sql注入处理
sql注入是指web应用程序对用户输入数据的合法性没有判断或过滤不严,攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句,在管理员不知情的情况下实现非法操作,
以此来实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据信息
//方法一
public static boolean sqlValidate(String str) {
str = str.toLowerCase();//统一转为小写
String badStr = "'|and|exec|execute|insert|select|delete|update|count|drop|*|%|chr|mid|master|truncate|" +
"char|declare|sitename|net user|xp_cmdshell|;|or|-|+|,|like'|and|exec|execute|insert|create|drop|" +
"table|from|grant|use|group_concat|column_name|" +
"information_schema.columns|table_schema|union|where|select|delete|update|order|by|count|*|" +
"chr|mid|master|truncate|char|declare|or|;|-|--|+|,|like|//|/|%|#";//过滤掉的sql关键字,可以手动添加
String[] badStrs = badStr.split("\\|");
for (int i = 0; i < badStrs.length; i++) {
if (str.indexOf(badStrs[i]) >= 0) {
return true;
}
}
return false;
}
//方法二
public static String checkPageSafeFilter(String str) {
// 过大不处理
if (StringUtils.isEmpty(str) || str.length() > 1024) {
return str;
}
return str.replaceAll("(?:[sS][eE][lL][eE][cC][tT] |[uU][pP][dD][aA][tT][eE] |[dD][eE][lL][eE][tT][eE] |<\\s*[sS][cC][rR][iI][pP][tT]\\s*>)", "");
}