面向sql程式設計的探索之路

邵磊發表於2018-07-06
SQL程式設計

前言

在我們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

我們選取

  1. id為url請求的方法名
  2. description為描述
  3. sql為具體sql語句
  4. 建立人
  5. 建立時間、修改時間

第一版

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;
    }
複製程式碼

好了到這裡，我們第一版就出來了，我們先試試效果！

面向sql程式設計的探索之路

內部呼叫

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程式設計，這裡索性大膽這樣稱呼。）如果您有什麼新的想法和思路，歡迎留言。

記得關注我哦！

相關文章