Shulou(Shulou.com)06/02 Report--

What this article shares with you is an example analysis of exceptions caused by Sql Firewall under Druid multiple data sources. The editor thinks it is quite practical, so I share it with you for study. I hope you can get something after reading this article. Let's take a look at it with the editor.

Cause of the problem

Because the application component uses two data sources, Oracle and Mysql, and it has always been Oracle, Mysql has been introduced recently.

The Sql firewall configuration of Druid is enabled for the application component production environment configuration. The configuration item is spring.datasource.druid.filter.wall.enabled=true. Since the test environment is not enabled, the problem is brought to production.

When Sql Firewall is enabled, a WallFilter is initialized when the data source is initialized.

@ Bean @ ConfigurationProperties ("spring.datasource.druid.filter.wall") @ ConditionalOnProperty (prefix = "spring.datasource.druid.filter.wall", name = {"enabled"}) @ ConditionalOnMissingBean public WallFilter wallFilter (WallConfig wallConfig) {WallFilter filter = new WallFilter (); filter.setConfig (wallConfig); return filter;} problem appearance

After the recent version was launched, some sql who had been running for several years suddenly reported an error overnight. after the brothers who followed the version encountered this problem, they were inevitably shocked and had no choice but to roll back the version that night.

Let's take a look at what mistakes sql reported at that time, which had been running for several years:

At com.alibaba.druid.wall.WallFilter.checkInternal (WallFilter.java:798) at com.alibaba.druid.wall.WallFilter.connection_prepareStatement (WallFilter.java:251) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement (FilterChainImpl.java:473) at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement (FilterAdapter.java:929) at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement (FilterEventAdapter.java:122) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement (FilterChainImpl. Java:473) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement (ConnectionProxyImpl.java:342) at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement (DruidPooledConnection.java:350) at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement (PreparedStatementHandler.java:87) at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare (BaseStatementHandler.java:88) at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare (RoutingStatementHandler.java:59) at org.apache.ibatis.executor.SimpleExecutor.prepareStatement ( SimpleExecutor.java:85) at org.apache.ibatis.executor.SimpleExecutor.doUpdate (SimpleExecutor.java:49) at org.apache.ibatis.executor.BaseExecutor.update (BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update (CachingExecutor.java:76) at org.apache.ibatis.session.defaults.DefaultSqlSession.update (DefaultSqlSession.java:198) at org.apache.ibatis.session.defaults.DefaultSqlSession.insert (DefaultSqlSession.java:185) at sun.reflect.GeneratedMethodAccessor408.invoke (Unknown Source) at sun. Reflect.DelegatingMethodAccessorImpl.invoke (DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke (Method.java:498) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke (SqlSessionTemplate.java:433)... 121common frames omittedCaused by: com.alibaba.druid.sql.parser.ParserException: syntax error, error in: 'USING (select? As o', expect USING, actual IDENTIFIER pos 61, line 2, column 39 Token IDENTIFIER USINGat com.alibaba.druid.sql.parser.SQLParser.printError (SQLParser.java:284) at com.alibaba.druid.sql.parser.SQLParser.accept (SQLParser.java:292) at com.alibaba.druid.sql.parser.SQLStatementParser.parseMerge (SQLStatementParser.java:2879) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList (SQLStatementParser.java:225) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList (SQLStatementParser.java:81) at com.alibaba.druid.wall.WallProvider.checkInternal ( WallProvider.java:622) at com.alibaba.druid.wall.WallProvider.check (WallProvider.java:576) at com.alibaba.druid.wall.WallFilter.checkInternal (WallFilter.java:785)... 141 common frames omitted

Seeing the exception stack above, the exception thrown means: the USING keyword expected to be accepted when parsing the SQL, but in fact, the USING keyword is not found in the keyword library, but the USING is regarded as the identifier IDENTIFIER.

Analysis of problems

Open the source code of SQLStatementParser and SQLParser and find the following information:

Com.alibaba.druid.sql.parser.SQLStatementParser#parseMerge

The key question is here. Why is the USING keyword not found in the keyword? Through debug, it is found that the com.alibaba.druid.pool.DruidDataSource#init method is called the first time the data is accessed, and there is this sentence in this method:

Public void init () throws SQLException {if (inited) {return;} final ReentrantLock lock = this.lock; try {lock.lockInterruptibly ();} catch (InterruptedException e) {throw new SQLException ("interrupt", e);} boolean init = false; try {if (inited) {return } initStackTrace = Utils.toString (Thread.currentThread (). GetStackTrace ()); this.id = DruidDriver.createDataSourceId (); if (this.id > 1) {long delta = (this.id-1) * 1000000; this.connectionIdSeed.addAndGet (delta); this.statementIdSeed.addAndGet (delta) This.resultSetIdSeed.addAndGet (delta); this.transactionIdSeed.addAndGet (delta);} if (this.jdbcUrl! = null) {this.jdbcUrl = this.jdbcUrl.trim (); initFromWrapDriverUrl () } / / focus on for (Filter filter: filters) {filter.init (this);}

This means initializing the configured Filter, where the WallFilter we enabled was initialized.

Next, let's go to the init method of WallFilter:

@ Override public synchronized void init (DataSourceProxy dataSource) {if (null = = dataSource) {LOG.error ("dataSource should not be null"); return } if (this.dbType = = null | | this.dbType.trim () .length () = 0) {/ / dbType depends on the type of data source that first called this method, if (dataSource.getDbType ()! = null) {this.dbType = dataSource.getDbType () } else {this.dbType = JdbcUtils.getDbType (dataSource.getRawJdbcUrl (), "");}} if (dbType = = null) {dbType = JdbcUtils.getDbType (dataSource.getUrl (), null) } if (JdbcUtils.MYSQL.equals (dbType) | | / JdbcUtils.MARIADB.equals (dbType) | | / / JdbcUtils.H2.equals (dbType)) {if (config = = null) {config = new WallConfig (MySqlWallProvider.DEFAULT_CONFIG_DIR) } / / initialize firewall of Mysql type provider = new MySqlWallProvider (config);} else if (JdbcUtils.ORACLE.equals (dbType) | | JdbcUtils.ALI_ORACLE.equals (dbType)) {if (config = = null) {config = new WallConfig (OracleWallProvider.DEFAULT_CONFIG_DIR);}

We find that this method adds synchronized and has a key attribute dbType in it.

Since there is only one instance of WallFilter, the dbType here is a member variable, so it is obtained from the current dataSource when it is judged to be null in the code.

So here's the problem: we now have two instances of DruidDataSource, so initialization of WallFilter is called during initialization, but although the init method of WallFilter is called multiple times, we know from the above code that dbType only depends on the value of dataSource.getDbType () when DruidDataSource first calls the init method of WallFilter.

At the end of the analysis, what problem do you see?

Yes, if the first data source we access is Mysql, then dbType will never change no matter how many times subsequent DruidDataSource calls WallFilter's init method.

So, as a result, we always use MySqlWallProvider, that is to say, the SQL firewall we use is of the Mysql type, so the subsequent syntax parsing of SQL also uses the MySqlLexer lexical parser, and there is no USING keyword in the keyword library of the parser, so there will be an error USING syntax error.

Solution

As I said at the beginning, this problem is that some examples report sql exceptions. Why?

This is because it has something to do with the data you access for the first time, and if you access the Mysql library for the first time, you will have this problem later when you access Oracle.

How to solve this problem? Here are two options.

You can set the configuration of spring.datasource.druid.filter.wall.enabled=true to false or remove it, which is also the easiest and quickest way

The expansion and related logic optimization of WallFilter and StatFilter classes require programming and have not been implemented.

The above is an example analysis of exceptions caused by Sql firewall under Druid multi-data sources. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.