preface

In our JavaWeb development process, there will be more or less just a few lines of SQL service methods or HTTP requests, always repeatedly write Controller layer, service layer, dao layer. So, I made a bold attempt to encapsulate a public method for this kind of method. Instead of writing Java code, I could write a few lines of SQL statements to generate various interfaces and methods.

Table design

We select

1. idIs the method name of the URL request
2. descriptionTo describe
3. sqlIs a specific SQL statement
4. founder
5. Create time and modify time

The first edition

The url request

Tentative method name at http://a.com/common/sqls/

Note: The method name is the ID in the table.

There is no limit to how many entries can be entered. The controller layer will be passed to the service layer. Of course, if you want to limit, you can also make a whitelist list of entries.

The Controller layer:

    @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;
    }
Copy the code

The Controller layer converts all variables to a paramsMap, verifies the user token, and obtains the user No through the token. If there is No token or the token cannot obtain the user No, an exception is thrown. Get user No, put the value of user No into map, and finally pass the method name (ID) and map to the service layer.

CommonService:

/** * find the specific SQL statement * @param id * @return sql
     */
    public String getSqlById(String id) {
        returncommonDao.getSqlById(id); } /** * returns the general SQL execution result * @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);
    }
Copy the code

In the first version, we replaced the SQL obtained from the method ID by iterating through all the parameters in the map, and then executed.

Interface:

public interface CommonDao {
    String getSqlById(String id);

    List<Map<String,Object>> querySql(String sql);
}

Copy the code

Mapper:

Mybatis version

<? 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>
Copy the code

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;
    }
Copy the code

Well here, our first edition came out, let’s try the effect first!

Internal calls

Call commonService.querySql(ID,params) return (id,params).

The new request

When we want to add an interface to get users’ friends, we just add one to the database

call

http://localhost:8080/*/common/sql/friends?token=cc4771aebb444d6c928a61ba5fe1153e

Reference:

{” data “: [{” id” : “1”, “name” : “* *”}], “code” : 200, the “message” : “success”}

Such an interface to get friends would be fine. Of course, the actual requirement SQL may be complicated, but this will not affect our project execution.

The second edition

Although the first version is ok, there is obviously a fatal bug that can be injected, so we need to fix the injection problem in this version.

Thinking a

Filtering parameters:

 public List<Map<String, Object>> querySql(String id,Map<String, Object> params) {
        String sql=getSqlById(id);
        if (params.entrySet().size()>5) {
            throw new CommonException("Too many parameters, please delete some.");
        }
        for (Map.Entry<String, Object> stringObjectEntry : params.entrySet()) {
            if (checksql((String)stringObjectEntry.getValue())) {
                throw new CommonException("Unsafe request!");
            }
            sql=sql.replace("{"+stringObjectEntry.getKey()+"}"."'"+(String)stringObjectEntry.getValue()+"'");
        }

        if (checksqlSecond(sql)) {
            throw new CommonException("SQL parameter is invalid! Cannot contain update, delete, etc.");
        }
        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|//|/|%|#"; String[] badStrs = badstr.split ("\ \ |");
        for (int i = 0; i < badStrs.length; i++) {
            if (str.indexOf(badStrs[i]) >= 0) {
                return true; }}return false;
    }
Copy the code

We use re to filter sensitive characters. In order to prevent too many input parameters from affecting our re matching, we limit 5 input parameters and limit the value of each parameter not to exceed 50.

This is done immediately to all kinds of abuse, good water code, why not precompile? So let’s keep exploring

Idea 2

Precompile parameters:

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());
}

Copy the code

We start with the regular substitution

select * from notices where reciever={userNo} and isRead={isRead}
Copy the code

Replace with

select * from notices where reciever=? and isRead=?
Copy the code

Add {userNo} and {isRead} to the list, and finally pass the userNo and isRead values of the list elements into preparedStatement.

There are also other solutions, such as using mybatis SQL constructor; Use other SQL precompilation frameworks, etc.

The third version:

Now that our security problem is solved, let’s append some public methods, such as the map containing the user ID, but not the user name, and we need to display the user name. If SQL associations are used, associations make SQL more complex. Here, we encapsulate some common methods, such as user ID to name and group ID to groupName.

Table transformation

Add two fields: input method and output method

Here we support comma-separated methods, identifying methods and appending them to params as follows:

        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; }}Copy the code

Switch can maintain your own company’s internal common methods to reduce SQL writing.

As for the reference, I think we all understand, so I won’t introduce it here.

conclusion

At this point, a framework for SQL programming is written, write a section of SQL, write a few public methods (optional), you can complete an HTTP interface or ordinary Java method, is not very convenient, there is no impulse to try.

This paper is a new attempt on development, but also a exploration of SQL programming. (Actually, I don’t know if I can call it SQL-oriented programming, so I’m just going to call it that.) If you have any new ideas and ideas, welcome to leave a message.

Keep an eye on me!