This is the second day of my participation in Gwen Challenge

background

Before the project goes online, it is often used for vulnerability scanning, which is inevitable. Especially, The father of Party A will strongly require the result of vulnerability scanning to go online. Provide solutions for vulnerability scanning encountered in this online project, and solve SQL injection problems.

SQL InjectIon: MyBatis Mapper

BoundSql is used for text substitution:

${orderField. FieldName} ${orderField. Order} = ${orderField. FieldName} = ${orderField ORDER_FIELD_PLACE_HOLDER_STR * 2, plug-in check SQL in the final execution to replace ORDER_FIELD_PLACE_HOLDER_STR for real data in SQL statements * fields to replace standard: * sort field: ORDER_FIELD_PLACE_HOLDER_STR * ORDER_TYPE_PLACE_HOLDER_STR * order_place_holder_str * ORDER_FIELD_TYPE_PLACE_HOLDER_STR * select field :FIELDS_PLACE_HOLDER_STR */ @intercepts ({@signature (type = executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}), }) public class ReplaceFiledsAndOrderPlaceholderPlugin implements Interceptor { private static final Field ADDITIONAL_PARAMETERS_FIELD; private static final String ORDER_FIELD_TYPE_PLACE_HOLDER = "ORDER_FIELD_TYPE_PLACE_HOLDER_STR"; private static final String ORDER_FIELD_PLACE_HOLDER = "ORDER_FIELD_PLACE_HOLDER_STR"; private static final String ORDER_TYPE_PLACE_HOLDER = "ORDER_TYPE_PLACE_HOLDER_STR"; private static final String FILEDS_PLACE_HOLDER = "FIELDS_PLACE_HOLDER_STR"; private static final Pattern ORDER_FIELD_TYPE_PLACE_HOLDER_PATTERN = Pattern.compile(ORDER_FIELD_TYPE_PLACE_HOLDER + "|"  + ORDER_FIELD_PLACE_HOLDER + "|" + ORDER_TYPE_PLACE_HOLDER); static { try { ADDITIONAL_PARAMETERS_FIELD = BoundSql.class.getDeclaredField("additionalParameters"); ReflectionUtils.makeAccessible(ADDITIONAL_PARAMETERS_FIELD); } catch (NoSuchFieldException e) { throw new HippoRuntimeException("Cannot read the field of additionalParameters from BoundSql."); } } @Override public Object intercept(Invocation invocation) throws Throwable { Executor executor = (Executor) invocation.getTarget(); Object[] args = invocation.getArgs(); MappedStatement statement = (MappedStatement) args[0]; Object param = args[1]; RowBounds rowBounds = (RowBounds) args[2]; ResultHandler<? > resultHandler = (ResultHandler<? >) args[3]; BoundSql boundSql = statement.getBoundSql(param); if (! needReplace(boundSql.getSql())) { return invocation.proceed(); } / / create a new BoundSql and set inside the SQL for the replacement of SQL BoundSql newBoundSql = new BoundSql (statement. GetConfiguration (), replaceSql(boundSql, param), boundSql.getParameterMappings(), param); for (Map.Entry<String, Object> entry : ((Map<String, Object>) ADDITIONAL_PARAMETERS_FIELD.get(boundSql)).entrySet()) { newBoundSql.setAdditionalParameter(entry.getKey(), entry.getValue()); } CacheKey cacheKey = executor.createCacheKey(statement, param, rowBounds, newBoundSql); return executor.query(statement, param, rowBounds, resultHandler, cacheKey, newBoundSql); } private boolean needReplace(String sql) { return ORDER_FIELD_TYPE_PLACE_HOLDER_PATTERN.matcher(sql).find() || sql.contains(FILEDS_PLACE_HOLDER); } private String replaceSql(BoundSql boundSql, Object param) { String sql = boundSql.getSql(); if (param instanceof BaseQuery) { sql = replaceSql(sql, (BaseQuery) param); } if (param instanceof List) { for (Object o : ((List<? >) param)) { if (o instanceof BaseQuery) { sql = replaceSql(sql, (BaseQuery) o); break; } } } if (param instanceof MapperMethod.ParamMap) { for (Object value : ((MapperMethod.ParamMap) param).values()) { if (value instanceof BaseQuery) { sql = replaceSql(sql, (BaseQuery) value); break; } } } return sql; } private String replaceSql(String sql, BaseQuery query) { String resultSql = replaceFieldsSql(sql, query.getFields()); List<Query.OrderField> orderFields = query.getOrderFields(); return replaceOrderSql(resultSql, orderFields); } private String replaceOrderSql(String sql, List<Query.OrderField> orderFields) { Matcher matcher = ORDER_FIELD_TYPE_PLACE_HOLDER_PATTERN.matcher(sql); int index = 0, preIndex = index; StringBuffer buffer = new StringBuffer(); while (matcher.find()) { String group = matcher.group(); switch (group) { case ORDER_FIELD_TYPE_PLACE_HOLDER: matcher.appendReplacement(buffer, orderFields.get(index).getFieldName() + " " + orderFields.get(index).getOrder()); index++; break; case ORDER_FIELD_PLACE_HOLDER: matcher.appendReplacement(buffer, orderFields.get(index).getFieldName()); preIndex = index; index++; break; case ORDER_TYPE_PLACE_HOLDER: matcher.appendReplacement(buffer, orderFields.get(preIndex).getOrder()); default: break; } // If (index >= orderfields.size ()) {index = 0; } } matcher.appendTail(buffer); return buffer.toString(); } private String replaceFieldsSql(String sql, String fields) { if (sql.contains(FILEDS_PLACE_HOLDER)) { return sql.replace(FILEDS_PLACE_HOLDER, fields == null ? "*" : fields); } return sql; } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { } }Copy the code

2. Introduce the plugin to mybatis configuration file

<plugin interceptor="xxx.xxx.. ReplaceFiledsAndOrderPlaceholderPlugin"> </plugin>Copy the code

3. Replace the corresponding fields in SQL

<sql id="listOrder"> <if test='orderFields ! = null and orderFields.size >0'> order by <foreach collection="orderFields" separator="," item="orderField"> <! -- ${orderField.fieldName} ${orderField.order} --> ORDER_FIELD_TYPE_PLACE_HOLDER_STR </foreach> </if> </sql>Copy the code

conclusion

We all know SQL injection problem, project delivery always need to scan for security vulnerabilities, headache. ${} is replaced with #{}, but this is not always the case, such as when the query fails. So another way is used to implement some cases where you can’t replace a field with #{}. So if we have a problem like this, we can use boundSql to replace it.