cjml1982 opened a new issue #3308: the shardingsphere desensitization not works URL: https://github.com/apache/incubator-shardingsphere/issues/3308 ## Question Before asking a question, make sure you have: ### Which version of ShardingSphere did you use? I use dev 4.0.0-RC3-SNAPSHOT ### Which project did you use? Sharding-JDBC or Sharding-Proxy? sharding-spring ### Expected behavior I want to implement the data desensitization , do follow this guid "https://www.mycitymemory.com/articles/2019/08/30/1567132582484.html"; , I will discribe the configuration bellow. ### Actual behavior the desensitization not worked ### Reason analyze (If you can) maybe my configuration is not correct ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. the log : 14:32:10.231 INFO [http-nio-9999-exec-2] (SQLLogger.java:99) - Rule Type: sharding **_14:32:10.232 INFO [http-nio-9999-exec-2] (SQLLogger.java:99) - Logic SQL: insert into t_order (pwd, id) values (?, ?) 14:32:10.232 INFO [http-nio-9999-exec-2] (SQLLogger.java:99) - SQLStatement: InsertSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.core.parse.sql.statement.dml.InsertStatement@36b602a6, tablesContext=TablesContext(tables=[Table(name=t_order, alias=Optional.absent())], schema=Optional.absent())), columnNames=[pwd, id], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=38, stopIndex=38, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=41, stopIndex=41, parameterMarkerIndex=1)], parameters=[aaa, 0])]) 14:32:10.232 INFO [http-nio-9999-exec-2] (SQLLogger.java:99) - Actual SQL: ds ::: insert into t_order (pwd, id) values (?, ?) ::: [aaa, 0] 14:32:10.454 WARN [http-nio-9999-exec-2] (SqlExceptionHelper.java:129) - SQL Error: 1054, SQLState: 42S22 14:32:10.455 ERROR [http-nio-9999-exec-2] (SqlExceptionHelper.java:131) - Unknown column 'pwd' in 'field list'_** 14:32:10.459 ERROR [http-nio-9999-exec-2] (ExceptionMapperStandardImpl.java:39) - HHH000346: Error during managed flush [org.hibernate.exception.SQLGrammarException: could not execute statement] 14:32:10.497 ERROR [http-nio-9999-exec-2] (DirectJDKLog.java:182) - Servlet.service() for servlet [dispatcherServlet] in context with path [/shardingsphere] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement] with root cause com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'pwd' in 'field list' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:3201) at com.alibaba.druid.wall.WallFilter.preparedStatement_executeUpdate(WallFilter.java:654) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:3199) at com.alibaba.druid.filter.FilterAdapter.preparedStatement_executeUpdate(FilterAdapter.java:1091) at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeUpdate(FilterEventAdapter.java:491) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:3199) at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeUpdate(PreparedStatementProxyImpl.java:194) at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeUpdate(DruidPooledPreparedStatement.java:256) at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$1.executeSQL(SQLExecuteCallbackFactory.java:48) at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$1.executeSQL(SQLExecuteCallbackFactory.java:44) at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:68) at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:57) at org.apache.shardingsphere.core.execute.ShardingExecuteEngine.syncGroupExecute(ShardingExecuteEngine.java:125) at org.apache.shardingsphere.core.execute.ShardingExecuteEngine.serialExecute(ShardingExecuteEngine.java:90) at org.apache.shardingsphere.core.execute.ShardingExecuteEngine.groupExecute(ShardingExecuteEngine.java:83) at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:73) at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:56) at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:134) at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.executeUpdate(PreparedStatementExecutor.java:133) at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.executeUpdate(ShardingPreparedStatement.java:153) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3032) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3547) at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:600) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:474) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39) at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1437) at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:494) at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3245) at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2451) at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473) at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:156) at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38) at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:231) at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:68) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:536) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:532) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) at com.sun.proxy.$Proxy102.save(Unknown Source) at com.defang.demo.web.DesensitaveController.put(DesensitaveController.java:49) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:877) at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851) at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:90) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:155) at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:123) at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:108) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:800) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1471) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) steps: 1) maven config <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.0.0-RC3-SNAPSHOT</version> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-namespace</artifactId> <version>4.0.0-RC3-SNAPSHOT</version> </dependency> 2) mysql table CREATE TABLE `t_order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pwd_cipher` varchar(2000) DEFAULT NULL COMMENT '密文数据列', `pwd_plain` varchar(255) DEFAULT NULL COMMENT '明文数据列', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4; 3) application.yaml shardingsphere: datasource: name: ds ds: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://192.168.20.162:3060/kms?useSSL=false&allowPublicKeyRetrieval=true username: root password: defang#root#2018 poolPingConnectionsNotUsedFor: 60000 removeAbandoned: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 minIdle: 1 validationQuery: SELECT 1 FROM DUAL initialSize: 5 maxWait: 60000 poolPreparedStatements: false filters: stat,wall testOnBorrow: false testWhileIdle: true minEvictableIdleTimeMillis: 300000 timeBetweenEvictionRunsMillis: 60000 testOnReturn: false maxActive: 50 druid: user: admin password: admin encryptRule: encryptors: #定义加密算法 encryptor_aes: #加密算法1 AES，名字可以自定义 type: aes props: aes.key.value: 123456abc tables: #定义脱敏规则 t_order: #目标表 columns: pwd: #目标表逻辑列 plainColumn: pwd_plain #目标表明文列 cipherColumn: pwd_cipher #目标表密文列 encryptor: encryptor_aes #采用的加密算法 props: sql.show: true #打印SQL query.with.cipher.comlum: false #使用密文搜索 jpa: hibernate: ddl-auto: create 4) code I wanna put "pwd" into the database. @Autowired private DesensitiveRepository repository; @ApiOperation(value = "存储隐私数据", notes = "存储隐私数据--") @RequestMapping(value = "/put", method = { RequestMethod.POST }) @ResponseBody public Object put(@RequestBody @Valid PutData putData) throws Exception { //DesensitiveData entity = new DesensitiveData(); DesensitiveData entity= new DesensitiveData(); entity.setPwd(putData.getPwd()); repository.save(entity); return null; } the entity code: @Entity @Data @Table(name = "t_order") public class DesensitiveData { @Id private int id; private String pwd; } the repository code @Repository public interface DesensitiveRepository extends JpaRepository<DesensitiveData, String> { } **question: my question is how to make the desensitization work? was my configuration or code wrong, please show me the right one. thx!!
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
