Web security Combat (i) SQL blinds

Objective
For a long time did not write anything, not do not want to write, but is always quiet not mind to write something. Of course, it takes so long to write something about it. Recently, just getting started on the safety side, as a rookie, but also for the purpose of learning, to talk about the recent exposure to the security aspects of it.

Background
Since the reference to the background, that we simply take, the recent Internet bursts of various amazing events, all kinds of doors, all kinds of photos, in the final analysis, are network security problems, some networks are monitored by others, the masses of life and work are in the eyes of others, every move can not escape the "discernment" of others There are some business servers are hacked, usernames, passwords stolen, credit card stolen, private file leaks and so on. These are safety issues. Of course, a big company like Apple would have such an event, not to mention a small, ordinary company.

Problem
SQL blinds, the word I think we should be very unfamiliar. If you do not know, then "SQL injection" you must have heard. What seems to be the link between these two words, like twins? Yes, SQL blinds is a security vulnerability of a web system, and it is a more serious one, which is a way of SQL injection. In other words, SQL injection exists in many ways, and SQL blinds are one of them.
At the security level, SQL blinds are a highly-threatening security vulnerability in which a service provider's server database can be compromised to steal, tamper with, or even delete user data. Of course, under normal circumstances, these are not allowed to occur, before the system went live, these are strictly tested, and the data in the database are also non-directional encryption, will not expose user data.

Technical description
Using IBM's AppScan, to test the system, because in the early development, the use of a lot of nonstandard code, so this test is a "big harvest", the site security vulnerability exposure undoubtedly, and in the Security test report, the most serious vulnerability is not SQL blind.


Solution Solutions
The dangers of SQL blinds are all clear, so how do we prevent this from happening? The simple point is that the request is filtered, the parameters are validated, the illegal characters are intercepted and replaced, the dangerous characters of the user input are cleaned, and the SQL can be compiled and executed correctly in the database. This is the simple idea to solve. However, what I want to say today is not these, but on the shoulders of giants to find problems and solve problems.
MyBatis, a pretty good persistence framework, with it, we saved a lot of time, a lot of repetitive work. At the same time, it is also necessary to realize that in the framework of the application, there are some security issues, of course, some of the framework has given a good solution, but some still need our own to solve. For example, the SQL blind problem, mybatis a good solution, in the Mapper.xml file using #{name} to provide placeholders, so that when SQL is compiled in the database, these placeholders are replaced with the correct parameter values of the user input, This avoids a part of the problem.
The code is as follows

<span style= "Font-family:comic Sans ms;font-size:12px;" ><?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= "Com.test.dao.TAcntMapper" > <resultmap id= "baseresultmap" type= "com.test.entity.TAcnt" > & Lt;id column= "sys_id" jdbctype= "DECIMAL" property= "Sysid"/> <result column= "obj_id" jdbctype= "VARCHAR" property = "ObjId"/> <result column= "obj_name" jdbctype= "VARCHAR" property= "objname"/> <result column= "OBJ_DESCR Iption "jdbctype=" varchar "property=" objdescription "/> <result column=" CREATOR "jdbctype=" varchar "property=" Creator "/> <result column=" create_time "jdbctype=" TIMESTAMP "property=" Createtime "/> <result column=" U Pdate_operator "jdbctype=" VARCHAR "property=" Updateoperator "/> <result column=" Update_time "jdbcType=" TIMESTAMP "property=" UpdateTime "/> <result column="Client_chk_flag "jdbctype=" DECIMAL "property=" Clientchkflag "/> <result column=" Cell_phone "jdbcType=" VARCHAR " property= "CellPhone"/> <result column= "Reg_email" jdbctype= "VARCHAR" property= "Regemail"/> <result col Umn= "GENDER" jdbctype= "DECIMAL" property= "GENDER"/> <result column= "qq_no" jdbctype= "VARCHAR" property= "qqno"/ > <result column= "web_url" jdbctype= "VARCHAR" property= "WebUrl"/> </resultMap> <sql id= "Example_ Where_clause "> <where> <foreach collection=" Oredcriteria "item=" Criteria "separator=" or "> &L T;if test= "Criteria.valid" > <trim prefix= "(" prefixoverrides= "and" suffix= ")" > <foreach Co llection= "Criteria.criteria" item= "criterion" > <choose> <when test= "Criterion.nova Lue "> ${criterion.condition} </when> <when test=" criterion.           Singlevalue ">       and ${criterion.condition} #{criterion.value} </when> <when test= "criterion.                Betweenvalue "> ${criterion.condition} #{criterion.value} and #{criterion.secondvalue}                  </when> <when test= "Criterion.listvalue" > and ${criterion.condition}                    <foreach close= ")" collection= "Criterion.value" item= "ListItem" open= "(" separator= "," > #{listitem} </foreach> </when> </choose> </ foreach> </trim> </if> </foreach> </where> </sql> <sql id= "U Pdate_by_example_where_clause "> <where> <foreach collection=" Example.oredcriteria "item=" Criteria "Sep            Arator= "or" > <if test= "criteria.valid" > <trim prefix= "(" prefixoverrides= "and" suffix= ")" > <foreach Collection= "Criteria.criteria" item= "criterion" > <choose> <when test= "Criterion.nov Alue "> ${criterion.condition} </when> <when test=" criterion                . Singlevalue "> and ${criterion.condition} #{criterion.value} </when> <when test= "Criterion.betweenvalue" > and ${criterion.condition} #{criterion.value} and #{criteri On.secondvalue} </when> <when test= "Criterion.listvalue" > and $ {criterion.condition} <foreach close= ")" collection= "Criterion.value" item= "ListItem" open= "(" Separat Or= "," > #{listitem} </foreach> </when> <  /choose> </foreach> </trim> </if> </foreach> </where> </sql> <sql Id= "Base_column_list" > sys_id, obj_id, Obj_name, Obj_description, CREATOR, Create_time     , Update_operator, Update_time, Client_chk_flag, Cell_phone, Reg_email, GENDER, Qq_no , Web_url </sql> <sql id= "From_join" > from t_acnt </sql> <select id= "Selectbyexample" p Arametertype= "Com.test.entity.TAcntCriteria" resultmap= "Baseresultmap" > select <if test= "distinct" > di Stinct </if> <include refid= "base_column_list"/> <include refid= "from_join"/> <if test  = "_parameter! = null" > <include refid= "example_where_clause"/> </if> <if test= "Orderbyclause ! = NULL "> ORDER by ${orderbyclause} </if> </select> <select id=" Selectbyprimarykey "Paramet Ertype= "BigDecimal" resultmap= "Baseresultmap" > select <include refid= "Base_column_list"/> <include Refid= "From_join"/>    where sys_id = #{sysid,jdbctype=decimal} </select> <delete id= "deletebyexample" parametertype= "com.test.e Ntity. Tacntcriteria "> Delete from t_acnt <if test=" _parameter! = null "> <include refid=" Example_where_cla Use "/> </if> </delete> <insert id=" Insert "parametertype=" com.test.entity.TAcnt "> in  Sert into t_acnt (obj_id, Obj_name, Obj_description, CREATOR, Create_time, Update_operator,        Update_time, Client_chk_flag, Cell_phone, Reg_email, GENDER, Qq_no, Web_url) VALUES ( #{objid,jdbctype=varchar}, #{objname,jdbctype=varchar}, #{objdescription,jdbctype=varchar}, #{crea Tor,jdbctype=varchar}, #{createtime,jdbctype=timestamp}, #{updateoperator,jdbctype=varchar}, #{updateTim E,jdbctype=timestamp}, #{clientchkflag,jdbctype=decimal}, #{cellphone,jdbctype=varchar}, #{regEmail,jdbc     Type=varchar} , #{gender,jdbctype=decimal}, #{qqno,jdbctype=varchar}, #{weburl,jdbctype=varchar}) </insert>    <select id= "Countbyexample" parametertype= "Com.test.entity.TAcntCriteria" resulttype= "Java.lang.Integer" > Select COUNT (*) <include refid= "From_join"/> <if test= "_parameter! = null" > <include refid= "E Xample_where_clause "/> </if> </select> <update id=" updatebyexample "parametertype=" map "> U Pdate t_acnt setobj_id = #{record.objid,jdbctype=varchar}, Obj_name = #{record.objname,jdbctype=varchar }, obj_description = #{record.objdescription,jdbctype=varchar}, CREATOR = #{record.creator  , Jdbctype=varchar}, Create_time = #{record.createtime,jdbctype=timestamp}, Update_operator                = #{record.updateoperator,jdbctype=varchar}, Update_time = #{record.updatetime,jdbctype=timestamp} , Client_chk_flag = #{Record.clientchkflag,jdbctype=decimal}, Cell_phone = #{record.cellphone,jdbctype=varchar}                , Reg_email = #{record.regemail,jdbctype=varchar}, GENDER = #{record.gender,jdbctype=decimal} , Qq_no = #{record.qqno,jdbctype=varchar}, Web_url = #{record.weburl,jdbctype=varchar} < If test= "_parameter! = null" > <include refid= "update_by_example_where_clause"/> </if> </update > <update id= "updatebyprimarykeyselective" parametertype= "com.test.entity.TAcnt" > Update t_acnt <set&      Gt <if test= "ObjId! = null" > obj_id = #{objid,jdbctype=varchar}, </if> <if test= "objname! = N        Ull "> obj_name = #{objname,jdbctype=varchar}, </if> <if test=" Objdescription! = null "> Obj_description = #{objdescription,jdbctype=varchar}, </if> <if test= "Creator! = null" > C Reator = #{creaTor,jdbctype=varchar}, </if> <if test= "Createtime! = null" > create_time = #{createtime,jdbcty Pe=timestamp}, </if> <if test= "Updateoperator! = null" > update_operator = #{updateoperator,jd Bctype=varchar}, </if> <if test= "UpdateTime! = null" > update_time = #{updatetime,jdbctype=tim Estamp}, </if> <if test= "Clientchkflag! = null" > Client_chk_flag = #{clientchkflag,jdbctype=d      Ecimal}, </if> <if test= "CellPhone! = null" > Cell_phone = #{cellphone,jdbctype=varchar},      </if> <if test= "Regemail! = null" > Reg_email = #{regemail,jdbctype=varchar}, </if> <if test= "Gender! = NULL" > Gender = #{gender,jdbctype=decimal}, </if> <if test= "Qqno! =  Null "> Qq_no = #{qqno,jdbctype=varchar}, </if> <if test=" WEBURL! = null "> Web_url = #{weburl,jdbctype=varchAR}, </if> </set> where sys_id = #{sysid,jdbctype=decimal} </update></mapper></span >

Of course, this is only the first step if you want to write a system that completely prevents the SQL blinds. It is still necessary to filter the requests made by the user, and to include the parameter values in the request, most likely including some dangerous characters, which are the problems we need to filter and handle. About solving the dangerous character of this piece, I leave it behind to elaborate, because it involves a lot of loopholes, including: XSS,CSRF and so on.

Conclusion
As the saying goes, security is fierce in the tiger! Safety is no small matter! On the Internet, these are the same. I was just beginning to come into contact with safety, and perhaps it was, just beginning to delve into the safety of me, is drawing on this nutrient, by testing out the security holes, 1.1 points to solve the potential crisis. This is just the beginning, the following days there is a long way to go, everyone together to refuel it. Of course, if there is any new feeling, I will also write to share with you, progress together.

Web security Combat (i) SQL blinds