有效防sql注入的两种方式

落日映苍穹つ 2022-12-09 05:20 242阅读 0赞

## 1 Mybatis ##

我们都知道：MyBatis使用`#{}`和`${}`来进行参数值替换；  
使用\#\{\}语法时，MyBatis会自动生成PreparedStatement，使用参数绑定（?）的方式来设置值，`#{}`可以有效防止SQL注入；  
而使用`${}`语法时，MyBatis会直接注入原始字符串，即相当于分段字符串，因此会导致SQL注入，如：

<select id="getByName" resultType="org.example.User">
     SELECT * FROM user WHERE name = '${name}' limit 1
    </select>

name为`' or '1'='1`，实际执行的语句为

SELECT * FROM user WHERE name = '' or '1'='1' limit 1

因此建议尽量使用`#{}`，但有些时候，如按语句排序，使用`#{}`会导致错误，如：

ORDER BY #{sortBy}

sortBy参数估计name，替换后会成为：

ORDER BY "name"

即以字符串`“name”`来排序，而不是按名称排序。这种情况就需要使用 `${}`，

ORDER BY ${sortBy}

使用了`${}`后，使用者需要自行过滤输入，方法有：

*  代码层使用白名单的方式，限制sortBy允许的值，如只能为name，create\_time变量，异常情况则设置为替换值name。
 *  在XML配置文件中，使用if标签来进行判断  
    Mapper接口方法：
    
        List<User> getUserListSortBy(@Param("sortBy") String sortBy);
    
    xml配置文件：
    
        <select id="getUserListSortBy" resultType="org.example.User">
          SELECT * FROM user
          <if test="sortBy == 'create_time desc' or sortBy == 'create_time asc'">
         order by ${sortBy}
          </if>
        </select>
    
    因为Mybatis不支持else，需要替换值的情况，可以使用 choose (when, otherwise)
    
        <select id="getUserListSortBy" resultType="org.example.User">
          SELECT * FROM user
          <choose>
            <when test="sortBy == 'create_time desc' or sortBy == 'create_time asc'">
              order by ${sortBy}
            </when>
            <otherwise>
              order by create_time desc
            </otherwise>
         </choose>
        </select>

## 2 Filter ##

CrosXssFilter：

@WebFilter(filterName = "CrosXssFilter ", urlPatterns = {  "/*" })
    public class CrosXssFilter implements Filter { 
        private static final Logger logger = LoggerFactory.getLogger(CrosXssFilter.class);
    @Override
        public void init(FilterConfig filterConfig) throws ServletException { 
        }
        @Override
        public void doFilter(ServletRequest request, ServletResponse response,
                FilterChain chain) throws IOException, ServletException { 
        	//sql,xss过滤
            HttpServletRequest httpServletRequest=(HttpServletRequest)request;
            logger.info("CrosXssFilter.......orignal url:{},ParameterMap:{}",httpServletRequest.getRequestURI(), JSONObject.toJSONString(httpServletRequest.getParameterMap()));
            XssHttpServletRequestWrapper xssHttpServletRequestWrapper=new XssHttpServletRequestWrapper(
                    httpServletRequest);
            chain.doFilter(xssHttpServletRequestWrapper, response);
            logger.info("CrosXssFilter..........doFilter url:{},ParameterMap:{}",xssHttpServletRequestWrapper.getRequestURI(), JSONObject.toJSONString(xssHttpServletRequestWrapper.getParameterMap()));
        }
        @Override
        public void destroy() { 
    
        }
    }

XssHttpServletRequestWrapper：

/** * 防止sql注入,xss攻击 * 前端可以对输入信息做预处理，后端也可以做处理。 */
    public class XssHttpServletRequestWrapper extends HttpServletRequestWrapper { 
        private final Logger log = LoggerFactory.getLogger(getClass());
        private static String key = "and|exec|insert|select|delete|update|count|*|%|chr|mid|master|truncate|char|declare|;|or|-|+";
        private static Set<String> notAllowedKeyWords = new HashSet<String>(0);
        private static String replacedString="INVALID";
        static { 
            String keyStr[] = key.split("\\|");
            for (String str : keyStr) { 
                notAllowedKeyWords.add(str);
            }
        }
    
        private String currentUrl;
    
        public XssHttpServletRequestWrapper(HttpServletRequest servletRequest) { 
            super(servletRequest);
            currentUrl = servletRequest.getRequestURI();
        }
    
    
        /**覆盖getParameter方法，将参数名和参数值都做xss过滤。 * 如果需要获得原始的值，则通过super.getParameterValues(name)来获取 * getParameterNames,getParameterValues和getParameterMap也可能需要覆盖 */
        @Override
        public String getParameter(String parameter) { 
            String value = super.getParameter(parameter);
            if (value == null) { 
                return null;
            }
            return cleanXSS(value);
        }
        @Override
        public String[] getParameterValues(String parameter) { 
            String[] values = super.getParameterValues(parameter);
            if (values == null) { 
                return null;
            }
            int count = values.length;
            String[] encodedValues = new String[count];
            for (int i = 0; i < count; i++) { 
                encodedValues[i] = cleanXSS(values[i]);
            }
            return encodedValues;
        }
        @Override
        public Map<String, String[]> getParameterMap(){ 
            Map<String, String[]> values=super.getParameterMap();
            if (values == null) { 
                return null;
            }
            Map<String, String[]> result=new HashMap<>();
            for(String key:values.keySet()){ 
                String encodedKey=cleanXSS(key);
                int count=values.get(key).length;
                String[] encodedValues = new String[count];
                for (int i = 0; i < count; i++){ 
                    encodedValues[i]=cleanXSS(values.get(key)[i]);
                }
                result.put(encodedKey,encodedValues);
            }
            return result;
        }
        /** * 覆盖getHeader方法，将参数名和参数值都做xss过滤。 * 如果需要获得原始的值，则通过super.getHeaders(name)来获取 * getHeaderNames 也可能需要覆盖 */
          /*@Override public String getHeader(String name) { String value = super.getHeader(name); if (value == null) { return null; } return cleanXSS(value); }*/
    
        private String cleanXSS(String valueP) { 
            // You'll need to remove the spaces from the html entities below
            String value = valueP.replaceAll("<", "<").replaceAll(">", ">");
            value = value.replaceAll("<", "& lt;").replaceAll(">", "& gt;");
            value = value.replaceAll("\$", "& #40;").replaceAll("\$", "& #41;");
            value = value.replaceAll("'", "& #39;");
            value = value.replaceAll("eval\$(.*)\$", "");
            value = value.replaceAll("[\\\"\\\'][\\s]*javascript:(.*)[\\\"\\\']", "\"\"");
            value = value.replaceAll("script", "");
            value = cleanSqlKeyWords(value);
            return value;
        }
    
        private String cleanSqlKeyWords(String value) { 
            String paramValue = value.toLowerCase();
            for (String keyword : notAllowedKeyWords) { 
                if (paramValue.length() > keyword.length() + 4
                        && (paramValue.contains(" "+keyword)||paramValue.contains(keyword+" ")||paramValue.contains(" "+keyword+" "))) { 
                    paramValue = StringUtils.replace(paramValue, keyword, replacedString);
                    log.error(this.currentUrl + "已被过滤，因为参数中包含不允许sql的关键词(" + keyword
                            + ")"+";参数："+value+";过滤后的参数："+paramValue);
                }
            }
            return paramValue;
        }
    }

## 参考 ##

1 [彻底干掉恶心的 SQL 注入漏洞， 一网打尽！][SQL _]  
2 [springboot-防止sql注入，xss攻击，cros恶意访问][springboot-_sql_xss_cros]

[SQL _]: https://blog.csdn.net/weixin_38405253/article/details/108722305
[springboot-_sql_xss_cros]: https://blog.csdn.net/yhhyhhyhhyhh/article/details/84504487