Druid技巧之使用PrepareStatement时输出完整SQL语句

为了防止SQL注入，我们通过采用PrepareStatement代替Statement。使用Mybatis的情况下就是使用 #{} 来代替 ${} 。

凡事有利必有弊，这样带来了安全性，但随之而来的是调试阶段的检测SQL正确性的繁琐。因为我们需要一个个将？替换为原始的值才能放到诸如plsql里去执行。

本文介绍如何在Druid中粗略解决这个问题。

1. 前言

在现在的开发工作中，我们一般采用数据库连接池的方式来协助我们进行数据库的操作。而Druid作为国内非常知名的数据库连接池。其设计理念决定了解决我们上面提到的需求应该是一件非常轻松的事情。

2. 原理

通常我们想要Druid输出相关执行的SQL语句，我们一般会进行如下配置：

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        .....
        <property name="filters" value="slf4j"/>
        ......
</bean>

跟随其配置，我们可以最终发现Slf4jLogFilter类。而其基类LogFilter的logExecutableSql正承担了我们所关心的任务。 而控制这个方法真正执行的正是 statementExecutableSqlLogEnable 字段的值，其默认为false， 所以我们需要启用它。

    <bean id="log-filter" class="com.alibaba.druid.filter.logging.Slf4jLogFilter">
        <property name="connectionLogEnabled" value="false"/>
        <property name="statementLogEnabled" value="false"/>
        <property name="resultSetLogEnabled" value="true"/>
	    <!-- 启用 -->
        <property name="statementExecutableSqlLogEnable" value="true"/>
    </bean>

 <!-- 数据连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        .....
        <property name="filters" value="stat,wall"/>
        ......
        <property name="proxyFilters">
            <list>
		        <!-- 载入 -->
                <ref bean="log-filter"/>
            </list>
        </property>
    </bean>

3. 疑难问题

发现一个问题就是，当SQL语句出错时Druid是不会进行 ？ 替换的。 所以我们需要进行一些魔改。

// 使用如下类代替默认的Slf4jLogFilter 进行Filter注册.
/*
	datasource.setProxyFilters(new ArrayList<Filter>() {
		private static final long serialVersionUID = 1L;

		{
			add(constructFilter());
		}
	});
*/
public class DruidSlf4jLoggerFilterEx extends Slf4jLogFilter {

	private static final Logger LOG = LoggerFactory
			.getLogger(DruidSlf4jLoggerFilterEx.class);

	// 将日志信息导入到我们要求的位置
	@Override
	protected void statementLogError(String message, Throwable error) {
		LOG.error(message, error);
		// super.statementLogError(message, error);
	}

	// 在SQL语句执行出错时, 依然进行 ? 替换
	@Override
	protected void statement_executeErrorAfter(StatementProxy statement,
			String sql, Throwable error) {
		if (!this.isStatementLogErrorEnabled()) {
			return;
		}

		if (!isStatementExecutableSqlLogEnable()) {
			statementLogError("{conn-" + statement.getConnectionProxy().getId()
					+ ", " + stmtId(statement) + "} execute error. " + sql,
					error);
			return;
		}

		int parametersSize = statement.getParametersSize();
		if (parametersSize <= 0) {
			statementLogError("{conn-" + statement.getConnectionProxy().getId()
					+ ", " + stmtId(statement) + "} execute error. " + sql,
					error);
		}

		final List<Object> parameters = new ArrayList<Object>(parametersSize);
		for (int i = 0; i < parametersSize; ++i) {
			JdbcParameter jdbcParam = statement.getParameter(i);
			parameters.add(jdbcParam != null ? jdbcParam.getValue() : null);
		}

		/*	Druid源码	
				String dbType = statement.getConnectionProxy().getDirectDataSource()
						.getDbType();
				String formattedSql = SQLUtils.format(sql, dbType, parameters,
						this.getStatementSqlFormatOption());
				statementLogError(
						"{conn-" + statement.getConnectionProxy().getId() + ", "
								+ stmtId(statement) + "} execute error. "
								+ formattedSql, error);
		*/

		final String formattedSql = transSql(parameters, sql);
		statementLogError(
				"{conn-" + statement.getConnectionProxy().getId() + ", "
						+ stmtId(statement) + "} execute error. "
						+ formattedSql, error);
	}
	
	private String transSql(List<Object> parameters, String sql) {

		if (sql.indexOf("?") < 0) {

			return sql;

		}

		for (int i = 0; i < parameters.size(); i++) {
			sql = sql.replaceFirst("\\?", parameters.get(i) != null ? "\'"
					+ parameters.get(i).toString() + "\'" : "NULL");
		}
		return sql;
	}	

	private String stmtId(StatementProxy statement) {
		StringBuffer buf = new StringBuffer();
		if (statement instanceof CallableStatementProxy) {
			buf.append("cstmt-");
		} else if (statement instanceof PreparedStatementProxy) {
			buf.append("pstmt-");
		} else {
			buf.append("stmt-");
		}
		buf.append(statement.getId());

		return buf.toString();
	}


}

4. 兼容 highgoDb （2018/11/1补充）

随着毛衣战的持续，国产化软件越来越被重视，所在公司也是响应号召要求支持highgoDb ；但在实际的使用过程中发现Druid在输出执行SQL时会报错，经过一番查找发现是因为Druid在格式化SQL语句时无法确定dbType导致的，具体源码如下：

// 被LogFilter.logExecutableSql()所调用
//	SQLUtils.format()
public static String format(String sql, String dbType, List<Object> parameters, FormatOption option) {
	try {
		// 截止Druid1.1.3, 提供了主流数据库db2, oracle, mysql, sqlserver, postgresql等的支持; highgoDb肯定就不在其中了
		SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType, true);
		parser.setKeepComments(true);

		List<SQLStatement> statementList = parser.parseStatementList();

		return toSQLString(statementList, dbType, parameters, option);
	} catch (ParserException ex) {
		LOG.warn("format error", ex);
		return sql;
	}
}

跟踪dbType的来源，最终的解决方案如下：

<bean id="mainDataSource" class="com.alibaba.druid.pool.DruidDataSource">
	...
	<property name="dbType" value="postgresql" />		
</bean>

5. Links

1. Druid搭配log4j2输出SQL语句和结果