前言
在我们JavaWeb开发过程中,或多或少会有些只是几行sql语句的service方法或是http请求,总是要反复写Controller层、service层、dao层。于是,我做了一个大胆的尝试,对于此类方法,封装出一个公共方法,不需要写java代码,写几行sql语句就能出各种接口及方法。
表设计
id | description | SQL | creator | creattime | updatetime |
---|---|---|---|---|---|
notices | 获取通知列表 | select * from notices where reciever ={userNo} | admin | 2018-07-06 14:07:48 | 2018-07-06 14:07:53 |
我们选取
id
为url请求的方法名description
为描述sql
为具体sql语句- 创建人
- 创建时间、修改时间
第一版
url请求
暂定 http://a.com/common/sqls/方法名
注:方法名为表中的id。
入参不限,这里controller
层都会传到service
层,当然啦,如果你想限制,也可以做个入参白名单列表。
Controller层:
@RequestMapping(value = "/sql/{id}")
public ResultObject getRules(@PathVariable(value = "id") String id) {
ResultObject resultObject = new ResultObject();
Map<String, Object> params=getRequestParams();
validateParams(params, "token");
User user = loginService.findByToken(params.get("token").toString());
params.put("userNo",user.getUserNo());
List<Map<String,Object>> mapList=commonService.querySql(id,params);
resultObject.setData(mapList);
return resultObject;
}
复制代码
controller层主要是将所有变量接收转为一个paramsMap,然后校验用户token,通过token获取用户No。当没有token或者token无法获取到用户No时,抛异常。 得到用户No,将用户No的值 put进map,最后将方法名(id)和map传入到service层。
CommonService:
/**
* 通过id找到具体sql语句
* @param id
* @return sql
*/
public String getSqlById(String id) {
return commonDao.getSqlById(id);
}
/**
* 返回通用sql执行结果
* @param id
* @param params
* @return list
*/
public List<Map<String, Object>> querySql(String id,Map<String, Object> params) {
String sql=getSqlById(id);
for (Map.Entry<String, Object> stringObjectEntry : params.entrySet()) {
sql=sql.replace("{"+stringObjectEntry.getKey()+"}","'"+(String)stringObjectEntry.getValue()+"'");
}
return commonDao.querySql(sql);
}
复制代码
第一版,我们先通过遍历map里的所有参数替换通过方法id获得到的sql,然后执行。
interface:
public interface CommonDao {
String getSqlById(String id);
List<Map<String,Object>> querySql(String sql);
}
复制代码
mapper:
mybatis版
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="*.basic.dao.CommonDao" >
<select id="getSqlById" resultType="string">
SELECT sql from m_sql WHERE id =#{value}
</select>
<select id="querySql" resultType="map">
${value}
</select>
</mapper>
复制代码
getRequestParams:
public Map<String, Object> getRequestParams() {
HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
try {
request.setCharacterEncoding("UTF-8");
} catch (UnsupportedEncodingException var5) {
var5.printStackTrace();
}
Map<String, Object> params = new HashMap();
Enumeration names = request.getParameterNames();
while(names.hasMoreElements()) {
String name = (String)names.nextElement();
params.put(name, request.getParameter(name));
}
return params;
}
复制代码
好了到这里,我们第一版就出来了,咱先试试效果!
内部调用
java代码内部调用的时候,先实例化一个CommonService
,然后调用commonService.querySql(id,params)
返回即可,无论是普通调用,还是再次封装暴露rpc接口都没有问题。
新增请求
当我们想新增一个,获取用户好友的接口,我们只需在数据库里增加一条
id | description | SQL | creator | creattime | updatetime |
---|---|---|---|---|---|
friends | 获取好友列表 | select * from freindss where userNo ={userNo} | admin | 2018-07-06 14:07:48 | 2018-07-06 14:07:53 |
调用
http://localhost:8080/*/common/sql/friends?token=cc4771aebb444d6c928a61ba5fe1153e
出参:
{"data":[{"id":"1","name":"张三"}] ,"code":200,"message":"success"}
这样一个获取friends
的接口就好了,当然啦,实际需求sql可能很复杂,但这个不会影响我们项目执行。
第二版
虽然说第一版ok了,但是显然有个致命bug,那就是会被注入,所以,这个版本我们要解决注入问题。
思路一
过滤入参:
public List<Map<String, Object>> querySql(String id,Map<String, Object> params) {
String sql=getSqlById(id);
if (params.entrySet().size()>5) {
throw new CommonException("参数太多了,请删除一些");
}
for (Map.Entry<String, Object> stringObjectEntry : params.entrySet()) {
if (checksql((String)stringObjectEntry.getValue())) {
throw new CommonException("不安全的请求!");
}
sql=sql.replace("{"+stringObjectEntry.getKey()+"}","'"+(String)stringObjectEntry.getValue()+"'");
}
if (checksqlSecond(sql)) {
throw new CommonException("sql参数不合法!不能包含update、delete等");
}
return commonDao.querySql(sql);
}
private boolean checksql(String sql) {
if (sql.length()>50) {
return true;
}
if (!sql.equals(transactSQLInjection(sql))) {
return true;
}
if (sqlValidate(sql)) {
return true;
}
return false;
}
private boolean checksqlSecond(String sql) {
String temp_sql=sql.toLowerCase();
if (temp_sql.contains("delete")||temp_sql.contains("update")||temp_sql.contains("truncate")||temp_sql.contains("drop")) {
return true;
}
return false;
}
private String transactSQLInjection(String str)
{
return str.replaceAll(".*([';]+|(--)+).*", " ");
}
private 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;
}
复制代码
我们使用正则去过滤敏感字符,为了防止入参过多影响我们正则匹配,所有限定5个入参,限定每个参数值不超过50。
这样一做马上就遭来各种辱骂,好水的代码,为什么不用预编译?那我们接下来继续探索
思路二
预编译参数:
String patt = "\\{.+?}";
String querySql=sql.replaceAll(patt,"?");
Pattern r = Pattern.compile(patt);
Matcher m = r.matcher(sql);
List<String> list= new ArrayList<String>();
while(m.find()){
list.add(m.group());
}
try {
PreparedStatement preparedStatement = conn.prepareStatement(querySql);
for (int i = 0; i < list.size(); i++) {
preparedStatement.setString(i+1,params.get(list.get(i).substring(1,list.get(i).length()-1));
}
preparedStatement.executeUpdate(sql_update);
}catch(Exception e){
//e.printStackTrace();
logger.error(e.message());
}
复制代码
先通过正则替换将
select * from notices where reciever={userNo} and isRead={isRead}
复制代码
替换为
select * from notices where reciever=? and isRead=?
复制代码
再将{userNo}、{isRead}加入list,最后遍历list,将list的元素userNo
、isRead
值传入preparedStatement
。
思路二的方案可完美解决sql注入问题,当然还有其它方案,比如利用mybatis
的sql构造器;利用其它sql预编译框架等。
第三版:
这下我们的安全问题也解决了,我们来追加一些公共方法,比如出参map中包含用户id,不包含用户姓名,而我们需要显示用户姓名。如果使用sql关联的话,各种关联使得sql越来越复杂。这里,我们封装一些公共方法,比如用户id转name、群组id转groupname。
表改造
增加 入参方法、出参方法 两个字段
id | description | SQL | inmethod | outmethod | creator |
---|---|---|---|---|---|
notices | 获取通知列表 | select * from notices where reciever =#{userNo} | usertoken2id | userid2name,groupid2name | admin |
这里我们支持逗号隔开方法,入参识别方法并追加到params里,代码如下:
String[] inMethodsplit = inMethod.split(",");
for (String s : inMethodsplit) {
switch (s){
case "usertoken2id":
params.add("userName",usertoken2id(params.get("userid")));
break;
case "xxxx":
params.add("xx",xxmethod(params.get("userxx")));
break;
}
}
复制代码
switch内可以维护自己公司的内部公用方法,来减少sql书写量。
至于出参,我想大家都懂了,这里就不做介绍。
总结
至此,面向sql编程的一个框架就写好了,写一段sql、写几个公共方法(可选),即可完成一个http接口
或者 普通java方法
,是不是很便捷,有没有要试一下的冲动。
本文是一种开发上的新尝试,也是一种面向sql
编程的探索。(其实,我不知道能不能称它是面向sql编程,这里索性大胆这样称呼。)如果您有什么新的想法和思路,欢迎留言。
记得关注我哦!