1、概述

制作一个SQL外部过滤器，即把系统将要执行的任何SQL发至外部服务器，外部服务器解析SQL并返回结果标识（如：A. 执行、B. 执行修改过后的SQL、C. 不执行直接报错) ，再进行下一步操作。

2、实现方案

如下图所示，建立一个起中转作用的JDBC驱动（com.demo.jdbc.MSDriver），在这个驱动中执行上面的过滤逻辑后再转发到原始数据库驱动中执行，为了做到通用，将链接字符串限定为（其中demo可以自己定义）：jdbc:demo:原数据库驱动:原数据库连接字符串，譬如：

原数据库驱动： com.mysql.jdbc.Driver

原数据库连接字符串：jdbc:mysql://localhost:6688，

新的驱动连接字符串：jdbc:demo:com.mysql.jdbc.Driver:jdbc:mysql://localhost:6688......

配置数据源时，驱动程序类必须使用插件代码中编写的com.demo.jdbc.MSDriver，这个驱动类对上面的复合驱动连接字符串进行解析，并生成对应的原来的驱动（com.mysql.jdbc.Driver），这样的话就能达到SQL在访问数据库之前先经过自己定义的JDBC驱动，再走原驱动，从而可以达到中间过滤的功能。

3、实现方法

新建一个扩展包，参考插件开发架构
新建一个Module类，见自定义Module
com.demo.jdbc.SQLFilterModule 实现 smartbi.framework.IModule
该类主要提供实现将当前sql发送给外部服务器，并返回结果的方法，具体代码如下

SQLFilterModule

public class SQLFilterModule implements IModule {
	private static final Logger log = Logger.getLogger(SQLFilterModule.class);
	private static SQLFilterModule instance = new SQLFilterModule();
	public static SQLFilterModule getInstance() {
		return instance;
	}
	/**
	 * module类初始化方法
	 */
	@Override
	public void activate() {
	}
	/**
	 * sql过滤拦截
	 * 
	 * @param sql
	 *            目前访问的sql语句
	 * @param driverInfo
	 *            驱动信息
	 *            {"jdbc":"com.mysql.jdbc.Driver","url":"jdbc:mysql//localhost:6688/....","user":"admin","password":"psd"}
	 * @return
	 */
	public JSONObject isSQLFilter(String sql, HashMap<String, String> driverInfo) {
		// 传递参数，例如SQL或者JDBC驱动信息等
		HashMap<String, String> map = new HashMap<String, String>();
		map.put("sql", sql);
		String url = driverInfo.get("url");
		map.put("jdbcInfo", "{\'" + url + "\'}");
		log.info("访问sql过滤服务器参数:" + map.toString());
		String ret;
		try {
			// 这里指定SQL过滤服务器的URL，将${SQLFilterServerURL}替换成SQL过滤服务器的URL即可
			ret = HttpUtil.doPost("${SQLFilterServerURL}", map);
			log.error("访问sql过滤服务器返回结果:" + ret);
			if (ret != null) {
				// ret 为 "{}"
				JSONObject jsonObj = new JSONObject(ret);
				if (jsonObj != null) {
					return jsonObj;
				}
			}
		} catch (IOException e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}
}

然后在扩展包注册该module类扩展包\src\web\META-INF\applicationContext.xml

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd">
<beans>
    <bean id="framework" class="smartbi.framework.Framework" factory-method="getInstance">
        <property name="modules">
			<map>
				<entry><key><value>SqlFilterModule</value></key><ref bean="SqlFilterModule" /></entry>
			</map>
        </property>
    </bean>
    <bean id="rmi" class="smartbi.framework.rmi.RMIModule" factory-method="getInstance">
        <property name="modules">
			<map>
				<entry><key><value>SqlFilterModule</value></key><ref bean="SqlFilterModule" /></entry>
			</map>
        </property>
    </bean>
    <bean id="SqlFilterModule" class="com.demo.jdbc.SQLFilterModule" factory-method="getInstance">
	</bean>
</beans>

实现自定义驱动，主要新建四个类，分别是：
com.demo.jdbc.MSDriver 实现 java.sql.Driver 接口
com.demo.jdbc.MSConnection 实现 java.sql.Connection 接口
com.demo.jdbc.MSStatement 实现 java.sql.Statement 接口
com.demo.jdbc.MSPreparedStatement 实现 java.sql.PreparedStatement 接口
可以借助Eclipse的 Source->Generate Getters and Setters 功能来快速生成相关代码
实现驱动类，MSDriver类，代码如下：

MSDriver

package com.demo.jdbc;
import java.sql.*;
import java.util.*;
import org.apache.log4j.Logger;
/**
 * 自定定义驱动类
 * 
 * @author smartbi
 */
public class MSDriver implements Driver {
	static Logger LOG = Logger.getLogger(MSDriver.class);
	/**
	 * 生成原驱动，此后调用驱动都通过这个对象
	 */
	Driver driver;
	/**
	 * 记录驱动信息，给connection以及statemenet使用
	 */
	HashMap<String, String> driverInfo = new HashMap<String, String>();
	/**
	 * 创建连接的时候将Connection替换成 自定义的msconn
	 * 
	 * @param url
	 *            值 jdbc:demo:com.mysql.jdbc.Driver:jdbc:mysql//localhost:6688/...
	 */
	public Connection connect(String url, Properties info) throws SQLException {
		// 返回自定义的msconn类
		MSConnection msconn = new MSConnection();
		/**
		 * 根据关键字"jdbc:demo" 来作为标识符，用来判断是否走以下逻辑
		 */
		if (url.startsWith("jdbc:demo:")) {
			try {
				// 数据库连接地址 "jdbc:demo" + ":" + "数据库驱动" + ":" + "数据库连接地址"
				String msurl = url.replace("jdbc:demo:", "");
				// 拿到数据库驱动 com.mysql.jdbc.Driver
				String msdriverclass = msurl.split(":")[0];
				// 数据库链接地址 jdbc:mysql//localhost:6688/...
				String msconnurl = msurl.substring(msurl.indexOf(":") + 1, msurl.length());
				// 实例化mysql驱动类，并指向driver,这样就可以保证dirver正常
				ClassLoader clzLoader = (ClassLoader) Class.forName("smartbi.repository.DAOModule")
						.getDeclaredField("classLoader").get(null);
				Class<? extends Object> Clazz = clzLoader == null ? Class.forName(msdriverclass)
						: clzLoader.loadClass(msdriverclass);
				driver = (Driver) Clazz.newInstance();
				// 新建一个connection类，并将其赋值到自定义的MSConnection类中，实现其接口
				Connection conn = driver.connect(msconnurl, info);
				msconn.setConn(conn);
				// 记录当前jdbc驱动信息 方便访问外部sql过滤服务器时调用使用
				driverInfo.put("Driver", msdriverclass);
				driverInfo.put("url", msconnurl);
				driverInfo.put("user", info.getProperty("user"));
				driverInfo.put("password", info.getProperty("password"));
				// 传参
				msconn.setDriverInfo(driverInfo);
			} catch (Exception e) {
				LOG.error("初始化数据库连接" + e.getMessage(), e);
			}
		}
		return msconn;
	}
	public boolean acceptsURL(String url) throws SQLException {
		return driver.acceptsURL(url);
	}
	public DriverPropertyInfo[] getPropertyInfo(String url, Properties info) throws SQLException {
		return driver.getPropertyInfo(url, info);
	}
	public int getMajorVersion() {
		return driver.getMajorVersion();
	}
	public int getMinorVersion() {
		return driver.getMinorVersion();
	}
	public boolean jdbcCompliant() {
		return driver.jdbcCompliant();
	}
}

实现连接类 MSConnection类，核心代码如下：

MSConnection

package com.demo.jdbc;
import java.sql.*;
import java.util.*;
import org.apache.log4j.Logger;
public class MSConnection implements Connection {
	Logger logg = Logger.getLogger(MSConnection.class);
	/**
	 * 数据库连接 后续的链接都通过这个对象
	 */
	Connection conn;
	/**
	 * 记录驱动信息
	 */
	HashMap<String, String> driverInfo = new HashMap<String, String>();
	public Connection getConn() {
		return conn;
	}
	public void setConn(Connection conn) {
		this.conn = conn;
	}
	public <T> T unwrap(Class<T> iface) throws SQLException {
		return conn.unwrap(iface);
	}
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return conn.isWrapperFor(iface);
	}
	public Statement createStatement() throws SQLException {
		// 新建一个Statement类
		Statement state = conn.createStatement();
		MSStatement msState = new MSStatement(state);
		msState.setDriverInfo(driverInfo);
		return msState;
	}
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		// 调用外部服务器 返回结果 {"right":true|false,"sql":"XXXX","msg":"xxxx"}
		// 创建一个PreparedStatement
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		MSPreparedStatement msPreparedStatement = new MSPreparedStatement(preparedStatement);
		// 记录sql语句
		msPreparedStatement.setSqlTemplate(sql);
		msPreparedStatement.setConn(conn);
		msPreparedStatement.setDriverInfo(driverInfo);
		return msPreparedStatement;
	}
	public CallableStatement prepareCall(String sql) throws SQLException {
		return conn.prepareCall(sql);
	}
	public String nativeSQL(String sql) throws SQLException {
		return conn.nativeSQL(sql);
	}
	public void setAutoCommit(boolean autoCommit) throws SQLException {
		conn.setAutoCommit(autoCommit);
	}
	public boolean getAutoCommit() throws SQLException {
		return conn.getAutoCommit();
	}
	public void commit() throws SQLException {
		conn.commit();
	}
	public void rollback() throws SQLException {
		conn.rollback();
	}
	public void close() throws SQLException {
		conn.close();
	}
	public boolean isClosed() throws SQLException {
		return conn.isClosed();
	}
	public DatabaseMetaData getMetaData() throws SQLException {
		return conn.getMetaData();
	}
	public void setReadOnly(boolean readOnly) throws SQLException {
		conn.setReadOnly(readOnly);
	}
	public boolean isReadOnly() throws SQLException {
		return conn.isReadOnly();
	}
	public void setCatalog(String catalog) throws SQLException {
		conn.setCatalog(catalog);
	}
	public String getCatalog() throws SQLException {
		return conn.getCatalog();
	}
	public void setTransactionIsolation(int level) throws SQLException {
		conn.setTransactionIsolation(level);
	}
	public int getTransactionIsolation() throws SQLException {
		return conn.getTransactionIsolation();
	}
	public SQLWarning getWarnings() throws SQLException {
		return conn.getWarnings();
	}
	public void clearWarnings() throws SQLException {
		conn.clearWarnings();
	}
	public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
		Statement state = conn.createStatement(resultSetType, resultSetConcurrency);
		MSStatement msState = new MSStatement(state);
		msState.setDriverInfo(driverInfo);
		return msState;
	}
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
			throws SQLException {
		// 有初始化perpareStatement的方法都要修改
		PreparedStatement preparedStatement = conn.prepareStatement(sql, resultSetType, resultSetConcurrency);
		MSPreparedStatement msPreparedStatement = new MSPreparedStatement(preparedStatement);
		// 记录sql语句
		msPreparedStatement.setSqlTemplate(sql);
		msPreparedStatement.setConn(conn);
		msPreparedStatement.setDriverInfo(driverInfo);
		return msPreparedStatement;
	}
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
		return conn.prepareCall(sql, resultSetType, resultSetConcurrency);
	}
	public Map<String, Class<?>> getTypeMap() throws SQLException {
		return conn.getTypeMap();
	}
	public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
		conn.setTypeMap(map);
	}
	public void setHoldability(int holdability) throws SQLException {
		conn.setHoldability(holdability);
	}
	public int getHoldability() throws SQLException {
		return conn.getHoldability();
	}
	public Savepoint setSavepoint() throws SQLException {
		return conn.setSavepoint();
	}
	public Savepoint setSavepoint(String name) throws SQLException {
		return conn.setSavepoint(name);
	}
	public void rollback(Savepoint savepoint) throws SQLException {
		conn.rollback(savepoint);
	}
	public void releaseSavepoint(Savepoint savepoint) throws SQLException {
		conn.releaseSavepoint(savepoint);
	}
	public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
			throws SQLException {
		Statement state = conn.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
		MSStatement msState = new MSStatement(state);
		msState.setDriverInfo(driverInfo);
		return msState;
	}
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency,
			int resultSetHoldability) throws SQLException {
		PreparedStatement preparedStatement = conn.prepareStatement(sql, resultSetType, resultSetConcurrency,
				resultSetHoldability);
		MSPreparedStatement msPreparedStatement = new MSPreparedStatement(preparedStatement);
		// 记录sql语句
		msPreparedStatement.setSqlTemplate(sql);
		msPreparedStatement.setConn(conn);
		msPreparedStatement.setDriverInfo(driverInfo);
		return msPreparedStatement;
	}
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
			int resultSetHoldability) throws SQLException {
		return conn.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
	}
	public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
		PreparedStatement preparedStatement = conn.prepareStatement(sql, autoGeneratedKeys);
		MSPreparedStatement msPreparedStatement = new MSPreparedStatement(preparedStatement);
		// 记录sql语句
		msPreparedStatement.setSqlTemplate(sql);
		msPreparedStatement.setConn(conn);
		msPreparedStatement.setDriverInfo(driverInfo);
		return msPreparedStatement;
	}
	public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
		PreparedStatement preparedStatement = conn.prepareStatement(sql, columnIndexes);
		MSPreparedStatement msPreparedStatement = new MSPreparedStatement(preparedStatement);
		// 记录sql语句
		msPreparedStatement.setSqlTemplate(sql);
		msPreparedStatement.setConn(conn);
		msPreparedStatement.setDriverInfo(driverInfo);
		return msPreparedStatement;
	}
	public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
		PreparedStatement preparedStatement = conn.prepareStatement(sql, columnNames);
		MSPreparedStatement msPreparedStatement = new MSPreparedStatement(preparedStatement);
		// 记录sql语句
		msPreparedStatement.setSqlTemplate(sql);
		msPreparedStatement.setConn(conn);
		msPreparedStatement.setDriverInfo(driverInfo);
		return msPreparedStatement;
	}
	public Clob createClob() throws SQLException {
		return conn.createClob();
	}
	public Blob createBlob() throws SQLException {
		return conn.createBlob();
	}
	public NClob createNClob() throws SQLException {
		return conn.createNClob();
	}
	public SQLXML createSQLXML() throws SQLException {
		return conn.createSQLXML();
	}
	public boolean isValid(int timeout) throws SQLException {
		return conn.isValid(timeout);
	}
	public void setClientInfo(String name, String value) throws SQLClientInfoException {
		conn.setClientInfo(name, value);
	}
	public void setClientInfo(Properties properties) throws SQLClientInfoException {
		conn.setClientInfo(properties);
	}
	public String getClientInfo(String name) throws SQLException {
		return conn.getClientInfo(name);
	}
	public Properties getClientInfo() throws SQLException {
		return conn.getClientInfo();
	}
	public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
		return conn.createArrayOf(typeName, elements);
	}
	public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
		return conn.createStruct(typeName, attributes);
	}
	public HashMap<String, String> getDriverInfo() {
		return driverInfo;
	}
	public void setDriverInfo(HashMap<String, String> driverInfo) {
		this.driverInfo = driverInfo;
	}
}

实现MSStatement类，代码如下：

MSStatement

package com.demo.jdbc;
import java.sql.*;
import java.util.HashMap;
import smartbi.net.sf.json.JSONObject;
import com.demo.jdbc.SQLFilterModule;
public class MSStatement implements Statement {
	Statement state;
	HashMap<String, String> driverInfo = new HashMap<String, String>();
	/**
	 * 判断是否执行访问外部sql过滤服务器，主要处理当测试数据库链接的sql没有通过时返回的异常没办法拦截到前台，所以通过这个回避
	 */
	boolean flag = false;
	public MSStatement(Statement state) {
		this.state = state;
	}
	public ResultSet executeQuery(String sql) throws SQLException {
		// 此处能拿到SQL语句，然后SQLFilterModule的sql过滤方法进行过滤
		// 返回结果 {"right":true|false,"sql":"XXXX","msg":"xxxx"}
		JSONObject result = SQLFilterModule.getInstance().isSQLFilter(sql, driverInfo);
		if (result != null) {
			if (result.has("right") && result.getBoolean("right")) {
				String filterSql = null;
				if (result.has("sql")) {
					filterSql = result.getString("sql");
					if (filterSql != null && filterSql.trim().length() > 0) {
						return state.executeQuery(filterSql);
					} else {
						return state.executeQuery(sql);
					}
				} else {
					return state.executeQuery(sql);
				}
			} else {
				StringBuffer sb = new StringBuffer();
				if (result.has("msg")) {
					sb.append("SQL过滤异常:");
					String msg = result.getString("msg");
					if (msg != null && msg.trim().length() > 0) {
						sb.append(msg);
					} else {
						sb.append("该SQL查询不合法");
					}
				} else {
					sb.append("该SQL查询不合法");
				}
				throw new SQLException(sb.toString());
			}
		} else {
			throw new SQLException("SQL过滤异常：访问SQL过滤服务器失败");
		}
	}
	public <T> T unwrap(Class<T> iface) throws SQLException {
		return state.unwrap(iface);
	}
	public int executeUpdate(String sql) throws SQLException {
		return state.executeUpdate(sql);
	}
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return state.isWrapperFor(iface);
	}
	public void close() throws SQLException {
		state.close();
	}
	public int getMaxFieldSize() throws SQLException {
		return state.getMaxFieldSize();
	}
	public void setMaxFieldSize(int max) throws SQLException {
		state.setMaxFieldSize(max);
	}
	public int getMaxRows() throws SQLException {
		return state.getMaxRows();
	}
	public void setMaxRows(int max) throws SQLException {
		state.setMaxRows(max);
	}
	public void setEscapeProcessing(boolean enable) throws SQLException {
		state.setEscapeProcessing(enable);
	}
	public int getQueryTimeout() throws SQLException {
		return state.getQueryTimeout();
	}
	public void setQueryTimeout(int seconds) throws SQLException {
		state.setQueryTimeout(seconds);
	}
	public void cancel() throws SQLException {
		state.cancel();
	}
	public SQLWarning getWarnings() throws SQLException {
		return state.getWarnings();
	}
	public void clearWarnings() throws SQLException {
		state.clearWarnings();
	}
	public void setCursorName(String name) throws SQLException {
		state.setCursorName(name);
	}
	public boolean execute(String sql) throws SQLException {
		return state.execute(sql);
	}
	public ResultSet getResultSet() throws SQLException {
		return state.getResultSet();
	}
	public int getUpdateCount() throws SQLException {
		return state.getUpdateCount();
	}
	public boolean getMoreResults() throws SQLException {
		return state.getMoreResults();
	}
	public void setFetchDirection(int direction) throws SQLException {
		state.setFetchDirection(direction);
	}
	public int getFetchDirection() throws SQLException {
		return state.getFetchDirection();
	}
	public void setFetchSize(int rows) throws SQLException {
		state.setFetchSize(rows);
	}
	public int getFetchSize() throws SQLException {
		return state.getFetchSize();
	}
	public int getResultSetConcurrency() throws SQLException {
		return state.getResultSetConcurrency();
	}
	public int getResultSetType() throws SQLException {
		return state.getResultSetType();
	}
	public void addBatch(String sql) throws SQLException {
		state.addBatch(sql);
	}
	public void clearBatch() throws SQLException {
		state.clearBatch();
	}
	public int[] executeBatch() throws SQLException {
		return state.executeBatch();
	}
	public Connection getConnection() throws SQLException {
		return state.getConnection();
	}
	public boolean getMoreResults(int current) throws SQLException {
		return state.getMoreResults(current);
	}
	public ResultSet getGeneratedKeys() throws SQLException {
		return state.getGeneratedKeys();
	}
	public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException {
		return state.executeUpdate(sql, autoGeneratedKeys);
	}
	public int executeUpdate(String sql, int[] columnIndexes) throws SQLException {
		return state.executeUpdate(sql, columnIndexes);
	}
	public int executeUpdate(String sql, String[] columnNames) throws SQLException {
		return state.executeUpdate(sql, columnNames);
	}
	public boolean execute(String sql, int autoGeneratedKeys) throws SQLException {
		return state.execute(sql, autoGeneratedKeys);
	}
	public boolean execute(String sql, int[] columnIndexes) throws SQLException {
		return state.execute(sql, columnIndexes);
	}
	public boolean execute(String sql, String[] columnNames) throws SQLException {
		return state.execute(sql, columnNames);
	}
	public int getResultSetHoldability() throws SQLException {
		return state.getResultSetHoldability();
	}
	public boolean isClosed() throws SQLException {
		return state.isClosed();
	}
	public void setPoolable(boolean poolable) throws SQLException {
		state.setPoolable(poolable);
	}
	public boolean isPoolable() throws SQLException {
		return state.isPoolable();
	}
	public HashMap<String, String> getDriverInfo() {
		return driverInfo;
	}
	public void setDriverInfo(HashMap<String, String> driverInfo) {
		this.driverInfo = driverInfo;
	}
}

实现MSPreparedStatement类，代码如下：

MSPreparedStatement

package com.demo.jdbc;
import java.io.*;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.*;
import java.util.*;
import java.sql.Date;
import smartbi.net.sf.json.JSONObject;
import com.demo.jdbc.SQLFilterModule;;

public class MSPreparedStatement implements PreparedStatement {
	PreparedStatement state;
	
	@Override
	public String toString() {
		return state.toString();
	}
	HashMap<String, String> driverInfo = new HashMap<String, String>();
	
	Connection conn = null;
	
	public void setConn(Connection conn) {
		this.conn = conn;
	}
	public MSPreparedStatement(PreparedStatement state){
		this.state = state;
		this.parameterValues = new ArrayList<String>();
	}
	public String getSqlTemplate() {
		return sqlTemplate;
	}
	public void setSqlTemplate(String sqlTemplate) {
		this.sqlTemplate = sqlTemplate;
	}
	
	private void saveQueryParamValue(int position, Object obj) {    
		String strValue;    
		if (obj instanceof String || obj instanceof Date) {    
			// if we have a String, include '' in the saved value    
			strValue = "'" + obj + "'";    
		} else {    
			if (obj == null) {    
				// convert null to the string null    
				strValue = "null";    
			} else {    
				// unknown object (includes all Numbers), just call toString    
				strValue = obj.toString();    
			}    
		}    
	        // if we are setting a position larger than current size of    
	        // parameterValues, first make it larger    
		while (position >= parameterValues.size()) {    
			parameterValues.add(null);    
		}    
	    // save the parameter    
	    parameterValues.set(position, strValue);    
	}  
	
	// 这一步是对ArrayList与sql进行处理，输出完整的sql语句    
    public String getQueryString() {    
        int len = sqlTemplate.length();    
        StringBuffer t = new StringBuffer(len * 2);    
    
        if (parameterValues != null) {    
            int i = 1, limit = 0, base = 0;    
    
            while ((limit = sqlTemplate.indexOf('?', limit)) != -1) {    
                t.append(sqlTemplate.substring(base, limit));    
                t.append(parameterValues.get(i));    
                i++;    
                limit++;    
                base = limit;    
            }    
            if (base < len) {    
                t.append(sqlTemplate.substring(base));    
            }    
        }    
        return t.toString();    
    }    
	public ResultSet executeQuery(String sql) throws SQLException {
		JSONObject result = SQLFilterModule.getInstance().isSQLFilter(sql, driverInfo);
		if(result != null){
			if(result.has("right") && result.getBoolean("right")){
				String filterSql = null;
				if(result.has("sql")){
					filterSql = result.getString("sql");
					if(filterSql != null && filterSql.trim().length()>0){
						return state.executeQuery(filterSql);
					}else{
						return state.executeQuery(sql);
					}
				}else{
					return state.executeQuery(sql);
				}
			}else{
				StringBuffer sb = new StringBuffer();
				if(result.has("msg")){
					sb.append("SQL过滤异常:");
					String msg = result.getString("msg");
					if(msg != null && msg.trim().length() >0){
						sb.append(msg);
					}else{
						sb.append("该SQL查询不合法");
					}
				}else{
					sb.append("该SQL查询不合法");
				}
				throw new SQLException(sb.toString());
			}
		}else{
			throw new SQLException("SQL过滤异常：访问SQL过滤服务器失败");
		}
	}
	public <T> T unwrap(Class<T> iface) throws SQLException {
		return state.unwrap(iface);
	}
	
	/** used for storing parameter values needed for producing log */    
    private ArrayList<String> parameterValues;    
    
    /** the query string with question marks as parameter placeholders */    
    private String sqlTemplate;    
	public ResultSet executeQuery() throws SQLException {
		String sql = getQueryString();
		JSONObject result = SQLFilterModule.getInstance().isSQLFilter(sql, driverInfo);
		if(result != null){
			if(result.has("right") && result.getBoolean("right")){
				String filterSql = null;
				if(result.has("sql")){
					filterSql = result.getString("sql");
					if(filterSql != null && filterSql.trim().length() >0){
						state = conn.prepareStatement(filterSql);
						return state.executeQuery();
					}else{
						return state.executeQuery();
					}
				}else{
					return state.executeQuery();
				}
			}else{
				StringBuffer sb = new StringBuffer();
				if(result.has("msg")){
					sb.append("SQL过滤异常:");
					String msg = result.getString("msg");
					if(msg != null && msg.trim().length() >0){
						sb.append(msg);
					}else{
						sb.append("该SQL查询不合法");
					}
				}else{
					sb.append("该SQL查询不合法");
				}
				throw new SQLException(sb.toString());
			}
		}else{
			throw new SQLException("SQL过滤异常：访问SQL过滤服务器失败");
		}
	}
	public int executeUpdate(String sql) throws SQLException {
		JSONObject result = SQLFilterModule.getInstance().isSQLFilter(sql, driverInfo);
		if(result != null){
			if(result.has("right") && result.getBoolean("right")){
				String filterSql = null;
				if(result.has("sql")){
					filterSql = result.getString("sql");
					if(filterSql != null && filterSql.trim().length()>0){
						return state.executeUpdate(filterSql);
					}else{
						return state.executeUpdate(sql);
					}
				}else{
					return state.executeUpdate(sql);
				}
			}else{
				StringBuffer sb = new StringBuffer();
				if(result.has("msg")){
					sb.append("SQL过滤异常:");
					String msg = result.getString("msg");
					if(msg != null && msg.trim().length() >0){
						sb.append(msg);
					}else{
						sb.append("该SQL查询不合法");
					}
				}else{
					sb.append("该SQL查询不合法");
				}
				throw new SQLException(sb.toString());
			}
		}else{
			throw new SQLException("SQL过滤异常：访问SQL过滤服务器失败");
		}
		
	}
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return state.isWrapperFor(iface);
	}
	public int executeUpdate() throws SQLException {
		return state.executeUpdate();
	}
	public void close() throws SQLException {
		state.close();
	}
	public void setNull(int parameterIndex, int sqlType) throws SQLException {
		state.setNull(parameterIndex, sqlType);
		saveQueryParamValue(parameterIndex, new Integer(sqlType));
	}
	public int getMaxFieldSize() throws SQLException {
		return state.getMaxFieldSize();
	}
	public void setBoolean(int parameterIndex, boolean x) throws SQLException {
		state.setBoolean(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Boolean(x)); 
	}
	public void setMaxFieldSize(int max) throws SQLException {
		state.setMaxFieldSize(max);
	}
	public void setByte(int parameterIndex, byte x) throws SQLException {
		state.setByte(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Byte(x));
	}
	public void setShort(int parameterIndex, short x) throws SQLException {
		state.setShort(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Short(x));
	}
	public int getMaxRows() throws SQLException {
		return state.getMaxRows();
	}
	public void setInt(int parameterIndex, int x) throws SQLException {
		state.setInt(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Integer(x));
	}
	public void setMaxRows(int max) throws SQLException {
		state.setMaxRows(max);
	}
	public void setLong(int parameterIndex, long x) throws SQLException {
		state.setLong(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Long(x));
	}
	public void setEscapeProcessing(boolean enable) throws SQLException {
		state.setEscapeProcessing(enable);
	}
	public void setFloat(int parameterIndex, float x) throws SQLException {
		state.setFloat(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Float(x));
	}
	public int getQueryTimeout() throws SQLException {
		return state.getQueryTimeout();
	}
	public void setDouble(int parameterIndex, double x) throws SQLException {
		state.setDouble(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Double(x));
	}
	public void setQueryTimeout(int seconds) throws SQLException {
		state.setQueryTimeout(seconds);
	}
	public void setBigDecimal(int parameterIndex, BigDecimal x)
			throws SQLException {
		state.setBigDecimal(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x); 
	}
	public void cancel() throws SQLException {
		state.cancel();
	}
	public void setString(int parameterIndex, String x) throws SQLException {
		state.setString(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}
	public SQLWarning getWarnings() throws SQLException {
		return state.getWarnings();
	}
	public void setBytes(int parameterIndex, byte[] x) throws SQLException {
		state.setBytes(parameterIndex, x);
	}
	public void clearWarnings() throws SQLException {
		state.clearWarnings();
	}
	public void setDate(int parameterIndex, Date x) throws SQLException {
		state.setDate(parameterIndex, x);
	}
	public void setCursorName(String name) throws SQLException {
		state.setCursorName(name);
	}
	public void setTime(int parameterIndex, Time x) throws SQLException {
		state.setTime(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x); 
	}
	public void setTimestamp(int parameterIndex, Timestamp x)
			throws SQLException {
		state.setTimestamp(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}
	public boolean execute(String sql) throws SQLException {
		JSONObject result = SQLFilterModule.getInstance().isSQLFilter(sql, driverInfo);
		if(result != null){
			if(result.has("right") && result.getBoolean("right")){
				String filterSql = null;
				if(result.has("sql")){
					filterSql = result.getString("sql");
					if(filterSql != null && filterSql.trim().length() >0){
						return state.execute(filterSql);
					}else{
						return state.execute(sql);
					}
				}else{
					return state.execute(sql);
				}
			}else{
				StringBuffer sb = new StringBuffer();
				if(result.has("msg")){
					sb.append("SQL过滤异常:");
					String msg = result.getString("msg");
					if(msg != null && msg.trim().length() >0){
						sb.append(msg);
					}else{
						sb.append("该SQL查询不合法");
					}
				}else{
					sb.append("该SQL查询不合法");
				}
				throw new SQLException(sb.toString());
			}
		}else{
			throw new SQLException("SQL过滤异常：访问SQL过滤服务器失败");
		}
	}
	public void setAsciiStream(int parameterIndex, InputStream x, int length)
			throws SQLException {
		state.setAsciiStream(parameterIndex, x, length);
	}
	public ResultSet getResultSet() throws SQLException {
		return state.getResultSet();
	}
	@SuppressWarnings("deprecation")
	public void setUnicodeStream(int parameterIndex, InputStream x, int length)
			throws SQLException {
		state.setUnicodeStream(parameterIndex, x, length);
		saveQueryParamValue(parameterIndex, x);
	}
	public int getUpdateCount() throws SQLException {
		return state.getUpdateCount();
	}
	public boolean getMoreResults() throws SQLException {
		return state.getMoreResults();
	}
	public void setBinaryStream(int parameterIndex, InputStream x, int length)
			throws SQLException {
		state.setBinaryStream(parameterIndex, x, length);
		saveQueryParamValue(parameterIndex, x); 
	}
	public void setFetchDirection(int direction) throws SQLException {
		state.setFetchDirection(direction);
	}
	public void clearParameters() throws SQLException {
		state.clearParameters();
	}
	public int getFetchDirection() throws SQLException {
		return state.getFetchDirection();
	}
	public void setObject(int parameterIndex, Object x, int targetSqlType)
			throws SQLException {
		state.setObject(parameterIndex, x, targetSqlType);
		saveQueryParamValue(parameterIndex, x);
	}
	public void setFetchSize(int rows) throws SQLException {
		state.setFetchSize(rows);
	}
	public int getFetchSize() throws SQLException {
		return state.getFetchSize();
	}
	public void setObject(int parameterIndex, Object x) throws SQLException {
		state.setObject(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);  
	}
	public int getResultSetConcurrency() throws SQLException {
		return state.getResultSetConcurrency();
	}
	public int getResultSetType() throws SQLException {
		return state.getResultSetType();
	}
	public void addBatch(String sql) throws SQLException {
		state.addBatch(sql);
	}
	public void clearBatch() throws SQLException {
		state.clearBatch();
	}
	public boolean execute() throws SQLException {
		return state.execute();
	}
	public int[] executeBatch() throws SQLException {
		return state.executeBatch();
	}
	public void addBatch() throws SQLException {
		state.addBatch();
	}
	public void setCharacterStream(int parameterIndex, Reader reader, int length)
			throws SQLException {
		state.setCharacterStream(parameterIndex, reader, length);
	}
	public void setRef(int parameterIndex, Ref x) throws SQLException {
		state.setRef(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);  
	}
	public Connection getConnection() throws SQLException {
		return state.getConnection();
	}
	public void setBlob(int parameterIndex, Blob x) throws SQLException {
		state.setBlob(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x); 
	}
	public void setClob(int parameterIndex, Clob x) throws SQLException {
		state.setClob(parameterIndex, x);
	}
	public boolean getMoreResults(int current) throws SQLException {
		return state.getMoreResults(current);
	}
	public void setArray(int parameterIndex, Array x) throws SQLException {
		state.setArray(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x); 
	}
	public ResultSetMetaData getMetaData() throws SQLException {
		return state.getMetaData();
	}
	public ResultSet getGeneratedKeys() throws SQLException {
		return state.getGeneratedKeys();
	}
	public void setDate(int parameterIndex, Date x, Calendar cal)
			throws SQLException {
		state.setDate(parameterIndex, x, cal);
	}
	public int executeUpdate(String sql, int autoGeneratedKeys)
			throws SQLException {
		return state.executeUpdate(sql, autoGeneratedKeys);
	}
	public void setTime(int parameterIndex, Time x, Calendar cal)
			throws SQLException {
		state.setTime(parameterIndex, x, cal);
		saveQueryParamValue(parameterIndex, x);
	}
	public int executeUpdate(String sql, int[] columnIndexes)
			throws SQLException {
		return state.executeUpdate(sql, columnIndexes);
	}
	public void setTimestamp(int parameterIndex, Timestamp x, Calendar cal)
			throws SQLException {
		state.setTimestamp(parameterIndex, x, cal);
		saveQueryParamValue(parameterIndex, x);
	}
	public void setNull(int parameterIndex, int sqlType, String typeName)
			throws SQLException {
		state.setNull(parameterIndex, sqlType, typeName);
		saveQueryParamValue(parameterIndex, new Integer(sqlType));
	}
	public int executeUpdate(String sql, String[] columnNames)
			throws SQLException {
		return state.executeUpdate(sql, columnNames);
	}
	public boolean execute(String sql, int autoGeneratedKeys)
			throws SQLException {
		return state.execute(sql, autoGeneratedKeys);
	}
	public void setURL(int parameterIndex, URL x) throws SQLException {
		state.setURL(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}
	public ParameterMetaData getParameterMetaData() throws SQLException {
		return state.getParameterMetaData();
	}
	public void setRowId(int parameterIndex, RowId x) throws SQLException {
		state.setRowId(parameterIndex, x);
	}
	public boolean execute(String sql, int[] columnIndexes) throws SQLException {
		return state.execute(sql, columnIndexes);
	}
	public void setNString(int parameterIndex, String value)
			throws SQLException {
		state.setNString(parameterIndex, value);
	}
	public void setNCharacterStream(int parameterIndex, Reader value,
			long length) throws SQLException {
		state.setNCharacterStream(parameterIndex, value, length);
	}
	public boolean execute(String sql, String[] columnNames)
			throws SQLException {
		return state.execute(sql, columnNames);
	}
	public void setNClob(int parameterIndex, NClob value) throws SQLException {
		state.setNClob(parameterIndex, value);
	}
	public void setClob(int parameterIndex, Reader reader, long length)
			throws SQLException {
		state.setClob(parameterIndex, reader, length);
	}
	public int getResultSetHoldability() throws SQLException {
		return state.getResultSetHoldability();
	}
	public void setBlob(int parameterIndex, InputStream inputStream, long length)
			throws SQLException {
		state.setBlob(parameterIndex, inputStream, length);
	}
	public boolean isClosed() throws SQLException {
		return state.isClosed();
	}
	public void setPoolable(boolean poolable) throws SQLException {
		state.setPoolable(poolable);
	}
	public void setNClob(int parameterIndex, Reader reader, long length)
			throws SQLException {
		state.setNClob(parameterIndex, reader, length);
	}
	public boolean isPoolable() throws SQLException {
		return state.isPoolable();
	}
	public void setSQLXML(int parameterIndex, SQLXML xmlObject)
			throws SQLException {
		state.setSQLXML(parameterIndex, xmlObject);
	}
	public void setObject(int parameterIndex, Object x, int targetSqlType,
			int scaleOrLength) throws SQLException {
		state.setObject(parameterIndex, x, targetSqlType, scaleOrLength);
		saveQueryParamValue(parameterIndex, x); 
	}
	public void setAsciiStream(int parameterIndex, InputStream x, long length)
			throws SQLException {
		state.setAsciiStream(parameterIndex, x, length);
		 saveQueryParamValue(parameterIndex, x);  
	}
	public void setBinaryStream(int parameterIndex, InputStream x, long length)
			throws SQLException {
		state.setBinaryStream(parameterIndex, x, length);
		 saveQueryParamValue(parameterIndex, x);  
	}
	public void setCharacterStream(int parameterIndex, Reader reader,
			long length) throws SQLException {
		state.setCharacterStream(parameterIndex, reader, length);
	}
	public void setAsciiStream(int parameterIndex, InputStream x)
			throws SQLException {
		state.setAsciiStream(parameterIndex, x);
	}
	public void setBinaryStream(int parameterIndex, InputStream x)
			throws SQLException {
		state.setBinaryStream(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}
	public void setCharacterStream(int parameterIndex, Reader reader)
			throws SQLException {
		state.setCharacterStream(parameterIndex, reader);
		saveQueryParamValue(parameterIndex, reader);
	}
	public void setNCharacterStream(int parameterIndex, Reader value)
			throws SQLException {
		state.setNCharacterStream(parameterIndex, value);
	}
	public void setClob(int parameterIndex, Reader reader) throws SQLException {
		state.setClob(parameterIndex, reader);
	}
	public void setBlob(int parameterIndex, InputStream inputStream)
			throws SQLException {
		state.setBlob(parameterIndex, inputStream);
	}
	public void setNClob(int parameterIndex, Reader reader) throws SQLException {
		state.setNClob(parameterIndex, reader);
	}
	public HashMap<String, String> getDriverInfo() {
		return driverInfo;
	}
	public void setDriverInfo(HashMap<String, String> driverInfo) {
		this.driverInfo = driverInfo;
	}
}

4、相关资源

该示例来源于实际项目，需要配合一个SQL过滤服务器才能正常工作。需要将SQLFilterModule.java中的${SQLFilterServerURL}替换成自己的SQL过滤服务器URL，且该过滤服务器的工作方式与该插件兼容方可。

SQLFilterModule

try {
	// 这里指定SQL过滤服务器的URL，将${SQLFilterServerURL}替换成SQL过滤服务器的URL即可
	ret = HttpUtil.doPost("${SQLFilterServerURL}", map);
	log.error("访问sql过滤服务器返回结果:" + ret);
	if (ret != null) {
		// ret 为 "{}"
		JSONObject jsonObj = new JSONObject(ret);
		if (jsonObj != null) {
			return jsonObj;
		}
	}
} catch (IOException e) {
	log.error(e.getMessage(), e);
}

示例代码：SQLFilterExt.zip

该示例代码来源于实际项目(EPPR-8642)，下面为基于V6的原始代码，在V7上已经不能正常工作。修正版本见上方附件。

SQLFilterExt_OLD.zip

关注我们

服务支持

页面树结构

SQL外部过滤功能

1、概述

2、实现方案

3、实现方法

4、相关资源