结果
填坑失败，并没有看懂是如何检测sql攻击的。
只能说的是： 建议都使用参数化传递sql语句参数。（所以，用hibernate、mybatis等框架的真不用太担心sql攻击问题。）
前言
本文主要是为了填坑：【spring】jdbcTemplate之sql参数注入
在上文中的demo是：
正确sql：
select c.child_id childId,c.child_name,c.child_age childAge,c.parent_id parentId from child c where c.child_id= '1'
攻击sql:
select c.child_id childId,c.child_name,c.child_age childAge,c.parent_id parentId from child c where c.child_id= '1';delete from child where child_id='1';--'
执行抛出的异常：
java.sql.SQLException: ORA-00911: 无效字符 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1203) at com.vergilyn.test.sh.dao.JdbcTemplateDao.injectionAttack(JdbcTemplateDao.java:49) at com.lyn.Junit.TestJdbc.testInjectionAttack(TestJdbc.java:35) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
最后给出的错误结论：oracle驱动jar中处理对sql注入攻击进行了处理。
为什么会得出错误结论：
1、肯定是技术不过关、分析不仔细。
2、没有严格去验证，单纯的测试了jdbc、jdbcTemplate、Hibernate就得出了此结论。
异常原因：
根据异常提示，我去看了下ojdbc5.jar中的源码。 即核心是oracle/jdbc/driver/T4CMAREngine.class中的unmarshalUB2()、buffer2Value(byte var)。
主要是在buffer2Value()方法返回long的值是911，然后ojdbc在后面根据此值抛出以上异常。
表示实在看不懂buffer2Value()及其内部含义，但简单理解就是在攻击sql中出现了”;”分号，导致抛出此异常。
一、验证非参数话会存在sql注入攻击（jdbcTemplate测试）
@Test
@Rollback()
public void testSqlInjectionAttack(){ //结论: jdbcTemplate不用参数化会存在sql攻击问题。
String id = "1"; //正确参数
// id = "1' or 1=1 --"; //sql攻击参数
Object rs = jdbcDao.sqlInjectionAttack(id);
System.out.println(JSON.toJSONString(rs));
}public Object sqlInjectionAttack(String id){ String sql = "select c.child_id childId,c.child_name,c.child_age childAge,c.parent_id parentId from child c"; sql += " where c.child_id= '"+id+"'"; return this.jdbcTemplate.query(sql,new BeanPropertyRowMapper<Child>(Child.class)); }
说明：1、正确的是返回一个Child对象。攻击sql则返回List<Child>对象。
2、用的方法是query(…)。如果是queryForXXX(…)，可能会抛异常：org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 10
sql攻击结果：
二、验证参数化查询防止sql注入攻击
@Test
@Rollback()
public void solveSqlInjectionAttack(){ //参数化sql防止sql注入攻击
String id = "1"; //正确参数
// id = "1' or 1=1 --"; //sql攻击参数
Object rs = jdbcTemplateDao.solveSqlInjectionAttack(id);
System.out.println(JSON.toJSONString(rs));
}public Object solveSqlInjectionAttack(String id){ String sql = "select c.child_id childId,c.child_name,c.child_age childAge,c.parent_id parentId from child c"; sql += " where c.child_id= ?"; return this.jdbcTemplate.query(sql,new Object[]{id},new BeanPropertyRowMapper<Child>(Child.class)); }
正确sql结果：
攻击sql结果：
信息: Rolled back transaction for test context [DefaultTestContext@1cfddcd testClass = JdbcTemplateTest, testInstance = com.vergilyn.persistence.test.jdbcTemplate.JdbcTemplateTest@1ead226, testMethod = solveSqlInjectionAttack@JdbcTemplateTest, testException = org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [select c.child_id childId,c.child_name,c.child_age childAge,c.parent_id parentId from child c where c.child_id= ?]; ORA-01722: 无效数字 ; nested exception is java.sql.SQLException: ORA-01722: 无效数字 , mergedContextConfiguration = [MergedContextConfiguration@18add92 testClass = JdbcTemplateTest, locations = '{classpath:jdbcTemplate/jdbcTemplate-context.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]]. 十二月 31, 2016 12:42:03 上午 org.springframework.context.support.AbstractApplicationContext doClose 信息: Closing org.springframework.context.support.GenericApplicationContext@1f12030: startup date [Sat Dec 31 00:41:44 CST 2016]; root of context hierarchy org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [select c.child_id childId,c.child_name,c.child_age childAge,c.parent_id parentId from child c where c.child_id= ?]; ORA-01722: 无效数字 ; nested exception is java.sql.SQLException: ORA-01722: 无效数字 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:243) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:727) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:737) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:787) at com.vergilyn.persistence.jdbcTemplate.JdbcTemplateDao.solveSqlInjectionAttack(JdbcTemplateDao.java:42) at com.vergilyn.persistence.test.jdbcTemplate.JdbcTemplateTest.solveSqlInjectionAttack(JdbcTemplateTest.java:30) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:73) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:224) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163) at org.junit.runner.JUnitCore.run(JUnitCore.java:137) at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117) at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42) at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262) at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84) Caused by: java.sql.SQLException: ORA-01722: 无效数字 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1203) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:703) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644) ... 34 more
以上结果是因为，数据库中的CHILD_ID的类型是NUMBER，传入的参数却是String。
当我把CHILD_ID的类型修改为：varchar2时，sql可正常执行。但sql查询结果是null。因为，数据库记录中并没有一个CHILD_ID = ”1' or 1=1 --“
三、部分源码 （填坑失败，后面的代码逻辑实在难看懂。）
/** * Query using a prepared statement, allowing for a PreparedStatementCreator * and a PreparedStatementSetter. Most other query methods use this method, * but application code will always work with either a creator or a setter. * @param psc Callback handler that can create a PreparedStatement given a * Connection * @param pss object that knows how to set values on the prepared statement. * If this is null, the SQL will be assumed to contain no bind parameters. * @param rse object that will extract results. * @return an arbitrary result object, as returned by the ResultSetExtractor * @throws DataAccessException if there is any problem */ public <T> T query( PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor<T> rse) throws DataAccessException { Assert.notNull(rse, "ResultSetExtractor must not be null"); logger.debug("Executing prepared SQL query"); return execute(psc, new PreparedStatementCallback<T>() { //line:695 @Override public T doInPreparedStatement(PreparedStatement ps) throws SQLException { ResultSet rs = null; try { if (pss != null) { pss.setValues(ps); } rs = ps.executeQuery(); // 抛出异常方法 - 02
ResultSet rsToUse = rs; if (nativeJdbcExtractor != null) { rsToUse = nativeJdbcExtractor.getNativeResultSet(rs); } return rse.extractData(rsToUse); } finally { JdbcUtils.closeResultSet(rs); if (pss instanceof ParameterDisposer) { ((ParameterDisposer) pss).cleanupParameters(); } } } }); }
//------------------------------------------------------------------------- // Methods dealing with prepared statements //------------------------------------------------------------------------- @Override public <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action) throws DataAccessException { Assert.notNull(psc, "PreparedStatementCreator must not be null"); Assert.notNull(action, "Callback object must not be null"); if (logger.isDebugEnabled()) { String sql = getSql(psc); logger.debug("Executing prepared SQL statement" + (sql != null ? " [" + sql + "]" : "")); } Connection con = DataSourceUtils.getConnection(getDataSource()); PreparedStatement ps = null; try { Connection conToUse = con; if (this.nativeJdbcExtractor != null && this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativePreparedStatements()) { conToUse = this.nativeJdbcExtractor.getNativeConnection(con); } ps = psc.createPreparedStatement(conToUse); applyStatementSettings(ps); PreparedStatement psToUse = ps; if (this.nativeJdbcExtractor != null) { psToUse = this.nativeJdbcExtractor.getNativePreparedStatement(ps); } T result = action.doInPreparedStatement(psToUse); // 抛出异常方法 - 01 handleWarnings(ps); return result; } catch (SQLException ex) { // Release Connection early, to avoid potential connection pool deadlock // in the case when the exception translator hasn't been initialized yet. if (psc instanceof ParameterDisposer) { ((ParameterDisposer) psc).cleanupParameters(); } String sql = getSql(psc); psc = null; JdbcUtils.closeStatement(ps); ps = null; DataSourceUtils.releaseConnection(con, getDataSource()); con = null; throw getExceptionTranslator().translate("PreparedStatementCallback", sql, ex); //line: 660 } finally { if (psc instanceof ParameterDisposer) { ((ParameterDisposer) psc).cleanupParameters(); } JdbcUtils.closeStatement(ps); DataSourceUtils.releaseConnection(con, getDataSource()); } }
其中抛异常的方法是： at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
方法中的>>>> T result = action.doInPreparedStatement(psToUse);
此处的action.doInPreparedStatement(psToUse)实际调用的就是上面 - 匿名内部类中定义的。
// 抛出异常 - 02 代码 public ResultSet executeQuery() throws SQLException { this.checkOpen(); try { return DelegatingResultSet.wrapResultSet(this, ((PreparedStatement)this._stmt).executeQuery()); } catch (SQLException var2) { this.handleException(var2); throw new AssertionError(); } }
出现异常方法：((PreparedStatement)this._stmt).executeQuery()。 this._stmt = OraclePreparedStatementWrapper.class
// OraclePreparedStatementWrapper.class public ResultSet executeQuery() throws SQLException { return this.preparedStatement.executeQuery(); //this.preparedStatement = T4CPreparedStatement.class }
class T4CPreparedStatement extends OraclePreparedStatement;
// OraclePreparedStatement.classpublic ResultSet executeQuery() throws SQLException { PhysicalConnection var1 = this.connection; synchronized(this.connection) { this.executionType = 1; this.executeInternal(); // 异常方法 - 03 if(this.userRsetType == 1) { this.currentResultSet = new OracleResultSetImpl(this.connection, this); return this.currentResultSet; } else { if(this.scrollRset == null) { this.currentResultSet = new OracleResultSetImpl(this.connection, this); this.scrollRset = this.currentResultSet; } return this.scrollRset; } } } int executeInternal() throws SQLException { this.noMoreUpdateCounts = false; this.ensureOpen(); if(this.currentRank > 0 && this.m_batchStyle == 2) { SQLException var3 = DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), 81, "batch must be either executed or cleared"); var3.fillInStackTrace(); throw var3; } else { boolean var1 = this.userRsetType == 1; this.prepareForNewResults(true, false); this.processCompletedBindRow(this.sqlKind == 0?1:this.batch, false); if(!var1 && !this.scrollRsetTypeSolved) { return this.doScrollPstmtExecuteUpdate() + this.prematureBatchCount; } else { this.doExecuteWithTimeout(); // 异常方法 - 04 boolean var2 = this.prematureBatchCount != 0 && this.validRows > 0; if(!var1) { this.currentResultSet = new OracleResultSetImpl(this.connection, this); this.scrollRset = ResultSetUtil.createScrollResultSet(this, this.currentResultSet, this.realRsetType); if(!this.connection.accumulateBatchResult) { var2 = false; } } if(var2) { this.validRows += this.prematureBatchCount; this.prematureBatchCount = 0; } return this.validRows; } } }
void doExecuteWithTimeout() throws SQLException { try { this.cleanOldTempLobs(); this.connection.registerHeartbeat(); this.rowsProcessed = 0; SQLException var1; if(this.sqlKind == 0) { if(this.connection.j2ee13Compliant && this.executionType == 2) { var1 = DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), 129); var1.fillInStackTrace(); throw var1; } this.connection.needLine(); if(!this.isOpen) { this.connection.open(this); this.isOpen = true; } if(this.queryTimeout != 0) { try { this.connection.getTimeout().setTimeout((long)(this.queryTimeout * 1000), this); this.executeMaybeDescribe(); } finally { this.connection.getTimeout().cancelTimeout(); } } else { this.executeMaybeDescribe(); // 异常方法 - 05 } this.checkValidRowsStatus(); if(this.serverCursor) { this.adjustGotLastBatch(); } } else { if(this.connection.j2ee13Compliant && this.sqlKind != 1 && this.sqlKind != 4 && this.executionType == 1) { var1 = DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), 128); var1.fillInStackTrace(); throw var1; } ++this.currentRank; if(this.currentRank >= this.batch) { try { this.connection.needLine(); if(!this.isOpen) { this.connection.open(this); this.isOpen = true; } if(this.queryTimeout != 0) { this.connection.getTimeout().setTimeout((long)(this.queryTimeout * 1000), this); } this.isExecuting = true; this.executeForRows(false); } catch (SQLException var14) { this.needToParse = true; if(this.batch > 1) { this.clearBatch(); int[] var2; int var3; if(this.numberOfExecutedElementsInBatch != -1 && this.numberOfExecutedElementsInBatch < this.batch) { var2 = new int[this.numberOfExecutedElementsInBatch]; for(var3 = 0; var3 < var2.length; ++var3) { var2[var3] = -2; } } else { var2 = new int[this.batch]; for(var3 = 0; var3 < var2.length; ++var3) { var2[var3] = -3; } } BatchUpdateException var17 = DatabaseError.createBatchUpdateException(var14, var2.length, var2); var17.fillInStackTrace(); throw var17; } this.resetCurrentRowBinders(); throw var14; } finally { if(this.queryTimeout != 0) { this.connection.getTimeout().cancelTimeout(); } this.currentRank = 0; this.isExecuting = false; this.checkValidRowsStatus(); } } } } catch (SQLException var16) { this.resetOnExceptionDuringExecute(); throw var16; } this.connection.registerHeartbeat(); }
void executeMaybeDescribe() throws SQLException { int i = 1; if (this.rowPrefetchChanged) { if ((this.streamList == null) && (this.rowPrefetch != this.definesBatchSize)) { this.needToPrepareDefineBuffer = true; } this.rowPrefetchChanged = false; } if (!this.needToPrepareDefineBuffer) { if (this.accessors == null) { this.needToPrepareDefineBuffer = true; } else if (this.columnsDefinedByUser) { this.needToPrepareDefineBuffer = (!checkAccessorsUsable()); } } boolean bool = false; try { this.isExecuting = true; if (this.needToPrepareDefineBuffer) { if (!this.columnsDefinedByUser) { executeForDescribe();// 异常方法 – 06 实际是调用T4CPreparedStatement.class中的实现方法 bool = true; if (this.aFetchWasDoneDuringDescribe) { i = 0; } } if (this.needToPrepareDefineBuffer) { prepareAccessors(); } } int j = this.accessors.length; for (int k = this.numberOfDefinePositions; k < j; k++) { Accessor localAccessor = this.accessors[k]; if (localAccessor != null) localAccessor.rowSpaceIndicator = null; } if (i != 0) { executeForRows(bool); } } catch (SQLException localSQLException) { this.needToParse = true; throw localSQLException; } finally { this.isExecuting = false; } }
// T4CPreparedStatement.class void executeForDescribe() throws SQLException { this.t4Connection.assertLoggedOn("oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe"); this.cleanOldTempLobs(); try { if(this.t4Connection.useFetchSizeWithLongColumn) { this.doOall8(true, true, true, true, false); } else { this.doOall8(true, true, false, true, this.definedColumnType != null);// 异常 - 07 } } catch (SQLException var8) { throw var8; // ORA-01722: 无效数字
} catch (IOException var9) {
((T4CConnection)this.connection).handleIOException(var9);
SQLException var2 = DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), var9);
var2.fillInStackTrace();
throw var2;
} finally {
this.rowsProcessed = this.t4Connection.all8.rowsProcessed;
this.validRows = this.t4Connection.all8.getNumRows();
}
this.needToParse = false;
this.implicitDefineForLobPrefetchDone = false;
this.aFetchWasDoneDuringDescribe = false;
if(this.t4Connection.all8.aFetchWasDone) {
this.aFetchWasDoneDuringDescribe = true;
this.rowPrefetchInLastFetch = this.rowPrefetch;
}
for(int var1 = 0; var1 < this.numberOfDefinePositions; ++var1) {
this.accessors[var1].initMetadata();
}
this.needToPrepareDefineBuffer = false;
}