Anyframe Jdbc Support Plugin Version 1.1.0 저작권 2007-2014 삼성 SDS 본문서의저작권은삼성 SDS 에있으며 Anyframe 오픈소스커뮤니티활동의목적하에서자유로운이용이가능합니다. 본문서를복제, 배포할경우에는저작권자를명시하여주시기바라며본문서를변경하실경우에는원문과변경된내용을표시하여주시기바랍니다. 원문과변경된문서에대한상업적용도의활용은허용되지않습니다. 본문서에오류가있다고판단될경우이슈로등록해주시면적절한조치를취하도록하겠습니다.
I. Introduction... 1 II. Jdbc Support... 2 1. 구현배경... 3 1.1. 사용자요구사항... 3 1.2. p6spy... 3 2. Architecture... 4 2.1. Architecture... 4 2.2. InjectionPatternPostProcessor... 4 2.3. CompleteQueryPostProcessor... 4 3. Configuration... 5 3.1. JdbcAspect... 5 3.2. injectionpatternpostprocessor 및 completequerypostprocessor 설정... 5 3.3. log4j 설정... 6 4. 기본구현및사용자확장구현방안... 7 4.1. DefaultInjectionPatternPostProcessor... 7 4.2. DefaultCompleteQueryPostProcessor... 7 4.3. 확장구현체 Sample - ThreadLocalCompleteQueryPostProcessor... 7 5. 기타고려사항... 9 5.1. DBMS Vendor specific 기능사용을위한 P6spyNativeJdbcExtractor 적용... 9 5.2. 적용가능영역... 9 5.3. 유의사항... 9 6. Resources... 11 ii
I.Introduction jdbc-support 에서는오픈소스 p6spy 를확장하여 SQL Injection 보안위험을방어할수있는기능및최종실행쿼리에대한로깅 ( 재처리 ) 기능을제공한다. jdbc-support plugin 은이를사용하는데필요한라이브러리및기본설정을포함하고있다. Installation Command 창에서다음과같이명령어를입력하여 jdbc-support plugin 을설치한다. mvn anyframe:install -Dname=jdbc-support installed(mvn anyframe:installed) 혹은 jetty:run(mvn clean jetty:run) command 를이용하여설치결과를확인해볼수있다. Plugin Name core [http://dev.anyframejava.org/ docs/anyframe/plugin/essential/ core/1.6.0/reference/htmlsingle/core.html] Version Range 2.0.0 > * > 1.4.0
II.Jdbc Support 최종실행된 SQL 문을로깅 ( 또는재처리 ) 하거나쿼리실행시 SQL Injection 패턴을판별하고이에대해 Warning 또는 Replace 처리를제공하여보안위협을경감시킬수있는기능을제공하는 anyframe-jdbc-support 에대해설명한다. sql logging 기능을제공하는유사한오픈소스 (log4jdbc) 와의차이점과 jdbc-support 의설정및유의사항등에대해아래의항목별로나누어설명하고자한다. 구현배경 Architecture Configuration 기본구현및사용자확장구현방안 기타고려사항
1. 구현배경 1.1. 사용자요구사항 SQL Injection 보안위험을방어할수있는기능에대한사용자요구사항구현방안 최종실행 SQL 문 (preparedstatement 의바인드변수까지반영된 ) 을확인및재처리할수있는기능에대한사용자요구사항구현방안 SQL Logging 관련기존재하는오픈소스기능 (ex. log4jdbc) 등이있으나 SQL Formatting 알아보기어렵게변경되고, logging 외추가적인처리가불가한문제점등이존재한다. 위 InjectionPattern / CompleteQuery 는서로관련성이있다. InjectionPattern 의판별후 replace 처리등을거친 SQL 문을최종실행할때확인 / 재처리할수있어야한다. 위의두가지요구사항은 queryservice 등특정구현에서많이아니라 jdbc 기반의일반적인 persistence 처리에동일하게적용할수있다면더좋을것이다. 결론적으로 log4jdbc 등의오픈소스 DataSource Spy 와같은형태로 DataSource 기반의 Connection 을 wrapping 하여실제실행되는 sql 을변경및재처리하는것이바람직하지만 log4jdbc 를사용하는것은불가능하였다. cf.) log4jdbc 는 SQL Logging 만을위한구현으로 Logging 을위한바인드변수데이터등을 Wrapping 한 Statement 모듈에미리가지고있다가 jdbc.sqlonly 등의 logger 설정에따라로그로출력하는기능만을고려하여만들어졌으며, 확장한다하더라도 SQL 문의변경등은불가능함을확인하였음 1.2.p6spy P6Spy 도마찬가지로유사한 JDBC proxy 이다. CompleteQuery 처리를위한 preparedstatement 의바인드변수로깅등이가능하면서 JDBC call 을 delegate 하므로이를적절히확장하면쿼리에대한재처리를적용하여 JDBC 를실행하는것도가능하다. cf.) P6Spy 의 spy.properties 설정은번거롭고해당기능을사용한 Logging 도사용치않을것이므로 anyframe-jdbc-support 에서는 P6Factory 확장과 AOP 형식으로사용한다. (ex. infrared-agent 와동일한방식 ) 3
2.Architecture 2.1.Architecture datasource - AOP Method Interceptor - JdbcAspect (p6spy connection wrapping) InjectionPatternPostProcessor / CompleteQueryPostProcessor - Spring bean 으로등록하여 JdbcAspect 에 DI 한다. P6Factory 는사용자 jdbc app. 내에서발생하는 getconnection, getpreparedstatement 에대해 InjectionPatternPostProcessor / CompleteQueryPostProcessor 기능이적용된 P6Connection, P6PreparedStatement 등을제공하게된다. JDBC 사용유형에따라 connection.preparestatement(sql), preparedstatement.execute(sql) 등의 point 에서 InjectionPattern 처리, CompleteQuery 처리 ( 로깅또는기타의방법으로사용자에게전달 ) 할수있다. P6Spy Connection Wrapping P6Factory 확장 P6Connection 확장 P6 Statement/PreparedStatement/CallableStatement 확장 2.2.InjectionPatternPostProcessor SQL Injection 패턴이존재하는지 detect 하고이에대한 warning 을처리한다. return void public void warningpattern(string sql) Injection 패턴에대해 replace 처리후변경된 sql 문을리턴한다. public String replacepattern(string sql) Default 구현은멀티건의 warningpatterns, replacepatterns regex( 정규표현식 ) 패턴을 bean 설정파일의 property 로정의하여이에대한매칭시 warn 로깅및 sql replace 를처리한다. cf.) 위의 Interface 만맞추면 Implementation 은사이트특성에맞게자유롭게구현해도된다. 2.3.CompleteQueryPostProcessor 최종 SQL 문에대해재처리 (ex. SQL Logging) 한다. public void processcompletequery(string sql) Default 구현은최종실행 SQL 문을 commons logging 을통한 Logger 로출력한다. cf.) 위의 Interface 만맞추면 Implementation 은사이트특성에맞게자유롭게구현해도된다. 4
3.Configuration 3.1.JdbcAspect Spring 의 MethodInterceptor 를구현하고있는 JdbcAspect 를등록한다. 이때 injectionpatternpostprocessor 와 completequerypostprocessor 를 dependency 로등록해줘야한다. <bean id="jdbcaspect" class="org.anyframe.jdbc.support.aspect.jdbcaspect"> <property name="injectionpatternpostprocessor" ref="injectionpatternpostprocessor" /> <property name="completequerypostprocessor" ref="completequerypostprocessor" /> </bean> AOP 를통한 DataSource interrupt 를처리하기위해 Spring 의 aop pointcut 표현식을지정하여위에서등록한 jdbcaspect 를연결해준다. <aop:config> <aop:pointcut id="jdbcpointcut" expression="execution(* *..*DataSource.*(..))" /> <aop:advisor advice-ref="jdbcaspect" pointcut-ref="jdbcpointcut" /> </aop:config> 3.2.injectionPatternPostProcessor 및 completequerypostprocessor 설정 기본구현으로제공되는 injectionpatternpostprocessor <bean id="injectionpatternpostprocessor" class="org.anyframe.jdbc.support.impl.defaultinjectionpatternpostprocessor"> <property name="warningpatterns"> <list> <value>-{2,}</value> <!-- check sql comment pattern --> <value>'?1'?\s*=\s*'?1'?</value> <!-- check 1 = 1 pattern - ex. '1' = '1', 1= 1, '1'='1' --> <!-- etc.. your own patterns --> </list> </property> <property name="replacepatterns"> <map> <entry key=";" value="" /> <!-- delete query statement delimiter --> <entry key="-{2,}" value="-" /> <!-- ex. sql comment (dash) changing - (one dash) --> <entry key="(?:or OR)\s+'?1'?\s*=\s*'?1'?" value="" /> <!-- ex. delete always true text pattern - or '1'='1' --> <!-- etc.. your own patterns --> </map> </property> </bean> 기본구현으로제공되는 completequerypostprocessor <bean id="completequerypostprocessor" class="org.anyframe.jdbc.support.impl.defaultcompletequerypostprocessor" /> 5
Configuration 위에서 warningpatterns 와 replacepatterns 에대한설정은정규표현식 (regular expressions) 으로작성한다. warningpatterns 나 replacepatterns 를생략하면해당기능은 skip 할수있다. (injectionpatternpostprocessor bean 설정자체를없애면안되며 property 태그영역을제거 ) 조직의보안부서에서보안점검리스트를정해특정한패턴을방어가능한지확인하는경우가많고이러한요구사항으로 jdbc-support 가구현되었다. 정규표현식의작성은일반적으로어렵게느끼는경우가많지만정규식을사용하면강력한패턴체크가가능하므로이를잘활용하면생산성을높일수있다. 정규식에대해더알고싶은경우참조링크가도움이될것이다. 3.3.log4j 설정 기본구현으로제공되는 injectionpatternpostprocessor 및 completequerypostprocessor 에대한 log4j logger 정의는다음과같다. <logger name="org.anyframe.jdbc.support.completequerypostprocessor" additivity="false"> <level value="info" /> <appender-ref ref="console" /> </logger> <logger name="org.anyframe.jdbc.support.injectionpatternpostprocessor" additivity="false"> <level value="warn" /> <appender-ref ref="console" /> </logger> 6
4. 기본구현및사용자확장구현방안 4.1.DefaultInjectionPatternPostProcessor warningpattern p6spy 를사용하면실행 SQL 이넘어오므로 default 구현은 SQL Injection 패턴을 regex 로정의 (Spring 설정파일에 warningpatterns 로사이트에특화된패턴들을마음대로정의가능 ) 하고해당패턴을순차적으로 matching 비교하여 match 되면 detect 로판단한다음이에대해 org.anyframe.jdbc.support.injectionpatternpostprocessor Logger 로 match 된패턴문자열및실행 sql 를 WARN Level 로그로남기는로직으로 warning 처리를구현하였다. replacepattern default 구현은최종 SQL 에대해 Spring 설정파일에 replacepatterns 로정의한 regex 패턴및 replacement 패턴을순차적으로 String.replaceAll 로변경한다. replacepattern 적용시유의사항 최종 query 에대한전체일괄변경이기때문에잘못된패턴설정시 SQL Syntax Error 를유발할수있다. cf.) InjectionPatternPostProcessor 는 Spring Bean 으로등록하므로 Application 의어떤영역에서든지 DI 하여활용가능함 ex1. ServletFilter 로모든 request parameter 에대해일괄 InjectionPatternPostProcessor 의 warning/replace 적용가능. ex2. PreparedStatement 문의 bind 변수처리가어려운사용자전달 query parameter 에대해사용자 DAO 영역에서선별적으로 InjectionPatternPostProcessor 의 replace 적용가능. 4.2.DefaultCompleteQueryPostProcessor default 구현은최종실행 SQL 에대해 org.anyframe.jdbc.support.completequerypostprocessor Logger 로 INFO Level 로로그를남긴다. logger 의 appender 등을별도로지정하면 Pattern Layout 이나 target(file, DB..) 을자유롭게조절할수있다. 4.3. 확장구현체 Sample - ThreadLocalCompleteQueryPostProcessor jdbc 실행을전 (flag 설정 ) / 후 (executedquery 추출및 ThreadLocal clear) 하여 ThreadLocal 처리에신경써야한다. 실제 JDBC 실행이목적이아니고 Query 만추출하고싶은경우 Exception 을 throw 하는것이필요할것이다. (cf. queryservice 를사용하는경우 stacktrace 가남는문제존재 (queryservice Logger OFF 가능 ) -- > QueryLogException 등을별도로정의하여일괄 AOP 로 Exception 처리하는영역등에서사용자 UI 에해당 Query 를되돌려주기위한로직을공통으로적용하는것이바람직함. Sample Source 7
기본구현및사용자확장구현방안 public class ThreadLocalCompleteQueryPostProcessor extends DefaultCompleteQueryPostProcessor { @Override public void processcompletequery(string sql) { super.processcompletequery(sql); } if ("Q".equals(SharedInfoHolder.getJobType())) { SharedInfoHolder.setExecutedQuery(sql); // throw new QueryLogException(sql); } } @Test public void testcompletequerypostprocessor() { NamedParameterJdbcTemplate jdbctemplate = new NamedParameterJdbcTemplate(dataSource); StringBuffer testsql = new StringBuffer(); testsql.append("select LOGON_ID, NAME, PASSWORD FROM TB_USER \n"); testsql.append("where LOGON_ID = :logonid AND PASSWORD = :password \n"); Map<String, Object> parammap = new HashMap<String, Object>(); parammap.put("logonid", "admin"); parammap.put("password", "adminpw"); // if ThreadLocal flag set - jobtype = "Q" SharedInfoHolder.setJobType("Q"); // execute jdbc - cf.) in ThreadLocalCompleteQueryPostProcessor, // executes query actually cause it does not throw Exception Map<String, Object> resultmap = jdbctemplate.queryformap(testsql.tostring(), parammap); assertequals("admin", resultmap.get("logon_id")); assertequals("adminpw", resultmap.get("password")); // check the last executed query (CompleteQuery) in ThreadLocal assertequals( "SELECT LOGON_ID, NAME, PASSWORD FROM TB_USER \nwhere LOGON_ID = 'admin' AND PASSWORD = 'adminpw' \n", SharedInfoHolder.getExecutedQuery()); } // ThreadLocal must be cleared! SharedInfoHolder.clearSharedInfo(); configuration <!-- some ThreadLocal processing added sample --> <bean id="completequerypostprocessor" class="org.anyframe.jdbc.support.ext.threadlocalcompletequerypostprocessor" /> 8
5. 기타고려사항 5.1.DBMS Vendor specific 기능사용을위한 P6spyNativeJdbcExtractor 적용 OracleLobHandler 사용시 Oracle 인경우 OracleLobHandler 등을사용하게되면내부적으로 native connection 객체로 casting 하다가 exception 나는문제가있다. org.springframework.dao.invaliddataaccessapiusageexception: OracleLobCreator needs to work on [oracle.jdbc.oracleconnection], not on [org.anyframe.jdbc.support.p6spy.p6ilconnection]: specify a corresponding NativeJdbcExtractor; nested exception is java.lang.classcastexception: org.anyframe.jdbc.support.p6spy.p6ilconnection 이를회피할수있는 P6spyNativeJdbcExtractor 를추가로적용해야한다. configuration <!-- NativeJdbcExtractor for P6Spy --> <bean id="nativejdbcextractor" class="org.anyframe.jdbc.support.p6spy.p6spynativejdbcextractor" lazy-init="true"> <!-- original nativejdbcextractor --> <property name="nextnativejdbcextractor" ref="commonsdbcpnativejdbcextractor" /> </bean> <bean id="commonsdbcpnativejdbcextractor" class="org.springframework.jdbc.support.nativejdbc.commonsdbcpnativejdbcextractor" lazy-init="true" /> <bean id="lobhandler" class="org.springframework.jdbc.support.lob.oraclelobhandler" lazy-init="true"> <property name="nativejdbcextractor" ref="nativejdbcextractor" /> </bean> 5.2. 적용가능영역 datasource 기반의 persistence 처리기술 (ex. Spring jdbctemplate, Anyframe queryservice, ibatis, Hibernate 등 ) 전반에모두활용가능하다. (ORM 인경우도실제로 jdbc 를통해수행되는내부 sql 문은확인가능함.) 5.3. 유의사항 log4jdbc 를활용한 SQL Logging 과는기능적으로중복되는영역이므로 resultset logging 등을사용하지않는다면 log4jdbc 를중복으로적용할필요가없다. p6spy 에대한 library dependency 충돌우려가있는데, 현재 p6spy-1.3.jar 를 pom dependency 로최종정의하였다. cf.) Anyframe Monitoring Tool 에대한에이전트인 infrared-agent-servlet-all-xxx.jar 를함께사용하는것은추천하지않는다. infrared-agent 가 p6spy 관련모듈을그대로 copy 하여포함하고있음 ( 일부구현변경 ) 9
기타고려사항 Oracle 특화기능을사용하는경우 (OracleLobHandler 및 P6spyNativeJdbcExtractor) commons-dbcp 에대한기본 dependency 라이브러리가존재해야한다. 10
6.Resources 참고자료 Download p6spy [http://sourceforge.net/projects/p6spy/] regular expression reference - basic [http://www.regular-expressions.info/reference.html] regular expression reference - advanced [http://www.regular-expressions.info/refadv.html] 11