如何避免mysql被内网扫描工具拦截 mysql拦截器

转载

编程梦想翱翔者 2023-08-25 19:52:34

文章标签 mybatis 拦截器完整可执行sql语句 sql 文章分类 MySQL 数据库

打印mybatis日志的方式

方法一：用下面的Java方法，每次手动粘贴sql语句和参数替换

方法二：用form程序，也需要手动粘贴sql语句和参数

方法三：使用自定义拦截器MybatisInterceptor，参照下面的博文

方法四： IDEA下使用插件Mybatis Log Plugin, 顺便推荐一下好用的Free Mybatis Plugin插件

因为业务需求，软件每次是打印出sql语句和参数，sql语句中参数都是问号，参数显示是值(类型)以逗号隔开连接，为啥不写个程序把完整的sql语句打印出来，每次都手动去填充一遍太麻烦了，本来想写一个Java的Form程序，奈何没基础，只能写个控制台程序输出。

应用场景如下：

sql语句：SELECT * FROM test where param1=? and param2=?

参数字符串：1(Integer),2(String)

期望输出字符串：SELECT * FROM test where param1=1 and param2='2'

如果只有三四个参数手动填充无所谓，但是如果是10个参数，手动填充试试

public class FillSQL {

	public static void main(String[] args) {
		String sqlNew="";
		String sqlNoParamString="SELECT * from test where param1=? and param2=?";
		String paramString="1(Integer), 2(String)";
        String[] sqlArray=sqlNoParamString.trim().split("[?]");//如果sql语句最后一个为？数组和没有？是一样的结果
        String[] paramArray=paramString.split(",");
        String paramType="integer";//integer string
        int count=sqlArray.length;
        for(int i=0;i<count;i++){
        	sqlNew+=sqlArray[i];
        	if(i==(count-1)&&!sqlNoParamString.trim().endsWith("?")){//如果最后一个？不在末尾，则已经没有参数了
        	  break;
        	}
                if(paramArray[i].trim().equals("null")){//null操作
                        sqlNew=sqlNew+paramArray[i];
                        continue;
                }
        	paramType=paramArray[i].substring(paramArray[i].indexOf('(')+1, paramArray[i].indexOf(')')).toLowerCase();
        	if(paramType.equals("string")){
                        sqlNew=sqlNew+"'"+paramArray[i].substring(0,paramArray[i].indexOf('(')).trim()+"'";
        	}else {
        	       sqlNew=sqlNew+paramArray[i].substring(0,paramArray[i].indexOf('('));
        	}
        }
        System.out.println(sqlNew);
	}

}

根据这个原理，写了一个桌面程序：，注意目前只适用于mysql数据库，对oracle数据库支持也在相应的改进，SQL填充类会相应的改变，该类代码不会再更新到博客中。

实际上，我们可以自己写一个拦截器Interceptor，打印出完整的可执行的sql语句，不需要再另外使用工具了

下面的代码抄自

package mybatis;
 
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
 
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
 
@Intercepts({
        @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }),
        @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
                RowBounds.class, ResultHandler.class }) })
public class MybatisInterceptor implements Interceptor {
 
    private Properties properties;
 
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameter = null;
        if (invocation.getArgs().length > 1) {
            parameter = invocation.getArgs()[1];
        }
        String sqlId = mappedStatement.getId();
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        Configuration configuration = mappedStatement.getConfiguration();
        Object returnValue = null;
        long start = System.currentTimeMillis();
        returnValue = invocation.proceed();
        long end = System.currentTimeMillis();
        long time = (end - start);
        if (time > 1) {
            String sql = getSql(configuration, boundSql, sqlId, time);
            System.err.println(sql);
        }
        return returnValue;
    }
 
    public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId, long time) {
        String sql = showSql(configuration, boundSql);
        StringBuilder str = new StringBuilder(100);
        str.append(sqlId);
        str.append(":");
        str.append(sql);
        str.append(":");
        str.append(time);
        str.append("ms");
        return str.toString();
    }
 
    private static String getParameterValue(Object obj) {
        String value = null;
        if (obj instanceof String) {
            value = "'" + obj.toString() + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            value = "'" + formatter.format(new Date()) + "'";
        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "";
            }
 
        }
        return value;
    }
 
    public static String showSql(Configuration configuration, BoundSql boundSql) {
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        if (parameterMappings.size() > 0 && parameterObject != null) {
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
 
            } else {
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    }
                }
            }
        }
        return sql;
    }
 
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }
 
    public void setProperties(Properties properties0) {
        this.properties = properties0;
    }
}

配置拦截器

<!-- mybatis-config.xml -->
<plugins>
  <plugin interceptor="mybatis.MybatisInterceptor"></plugin>
</plugins>

配置输出日志，我这里使用的是LOG4J2

<!-- mybatis-config.xml -->
<settings>
    <setting name="logImpl" value="LOG4J2"/>
</settings>

具体配置可以参考http://www.mybatis.org/mybatis-3/zh/logging.html

如果是在JavaBean中配置

sessionFactory.setPlugins(new Interceptor[]{new MybatisInterceptor()});

如果是在applicationContext.xml中配置

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="defaultDatasource" />
        <property name="mapperLocations" value="classpath:mybatis/com/**/*.xml" />
        <property name="plugins">
            <array>
                <bean class="com...MybatisInterceptor">
                </bean>
            </array>
        </property>
    </bean>

但是在使用的时候发现某些情况下会报错

java.lang.IndexOutOfBoundsException: No group 2

at java.util.regex.Matcher.start(Matcher.java:375)
at java.util.regex.Matcher.appendReplacement(Matcher.java:880)
at java.util.regex.Matcher.replaceFirst(Matcher.java:1004)
at java.lang.String.replaceFirst(String.java:2166)

经过调试，发现是密码加密字符串造成的，密码字符串中会有$2等字符串，会被replaceFirst正则匹配导致错误

在调用replaceFirst函数时加上sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));

如果参数没有传递的话，原作者的写法把sql语句打印出来会错位

我使用的mybatis打印可执行sql语句拦截器的完整代码如下

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;

import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import java.util.regex.Matcher;


@Intercepts({
		@Signature(type = Executor.class, method = "update", args = {
				MappedStatement.class, Object.class }),
		@Signature(type = Executor.class, method = "query", args = {
				MappedStatement.class, Object.class, RowBounds.class,
				ResultHandler.class }) })
@Slf4j
public class MybatisInterceptor implements Interceptor {

	private Properties properties;

	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		try{
			MappedStatement mappedStatement = (MappedStatement) invocation
					.getArgs()[0];
			Object parameter = null;
			if (invocation.getArgs().length > 1) {
				parameter = invocation.getArgs()[1];
			}
			String sqlId = mappedStatement.getId();
			BoundSql boundSql = mappedStatement.getBoundSql(parameter);
			Configuration configuration = mappedStatement.getConfiguration();
			String sql = getSql(configuration, boundSql, sqlId, 0);
			log.debug(sql);
		}catch(Exception e){
			log.error(e.getMessage(), e);
		}
		//}
		return invocation.proceed();
	}

	public static String getSql(Configuration configuration, BoundSql boundSql,
			String sqlId, long time) {
		String sql = showSql(configuration, boundSql);
		StringBuilder str = new StringBuilder(100);
		str.append(sqlId);
		str.append(":");
		str.append(sql);
		return str.toString();
	}

	private static String getParameterValue(Object obj) {
		String value = null;
		if (obj instanceof String) {
			value = "'" + obj.toString() + "'";
		} else if (obj instanceof Date) {
			DateFormat formatter = DateFormat.getDateTimeInstance(
					DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
			value = "'" + formatter.format(new Date()) + "'";
		} else {
			if (obj != null) {
				value = obj.toString();
			} else {
				value = "";
			}

		}
		return value;
	}

	public static String showSql(Configuration configuration, BoundSql boundSql) {
		Object parameterObject = boundSql.getParameterObject();
		List<ParameterMapping> parameterMappings = boundSql
				.getParameterMappings();
		String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
		if (CollectionUtils.isNotEmpty(parameterMappings) && parameterObject != null) {
			TypeHandlerRegistry typeHandlerRegistry = configuration
					.getTypeHandlerRegistry();
			if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
				sql = sql.replaceFirst("\\?",
						Matcher.quoteReplacement(getParameterValue(parameterObject)));

			} else {
				MetaObject metaObject = configuration
						.newMetaObject(parameterObject);
				for (ParameterMapping parameterMapping : parameterMappings) {
					String propertyName = parameterMapping.getProperty();
					if (metaObject.hasGetter(propertyName)) {
						Object obj = metaObject.getValue(propertyName);
						sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
					} else if (boundSql.hasAdditionalParameter(propertyName)) {
						Object obj = boundSql
								.getAdditionalParameter(propertyName);
						sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
					}else{sql=sql.replaceFirst("\\?","缺失");}//打印出缺失，提醒该参数缺失并防止错位
				}
			}
		}
		return sql;
	}

	@Override
	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}

	@Override
	public void setProperties(Properties properties0) {
		this.properties = properties0;
	}
}

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。