最近线上总是报org.springframework.jdbc.BadSqlGrammarException错误
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT lot_code as CODE,lot_name as NAME FROM or_custom_lot where 1=1 and lot_name like '%jia'zhao'y%' ORDER BY create_time asc]; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'zhao'y%' ORDER BY create_time asc' at line 1at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645)at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680)at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:707)at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757)at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192)at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForList(NamedParameterJdbcTemplate.java:264)at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForList(NamedParameterJdbcTemplate.java:271)at com.keytop.superpark.ks.ormanage.dao.CustomLotDao.getCatalCodeList(CustomLotDao.java:84)at com.keytop.superpark.ks.ormanage.service.CustomLotService.getCatalCodeList(CustomLotService.java:141)at com.keytop.superpark.ks.ormanage.service.CustomLotService$$FastClassBySpringCGLIB$$e7b6a5e2.invoke(<generated>)at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)at com.keytop.superpark.ks.ormanage.service.CustomLotService$$EnhancerBySpringCGLIB$$c63362e8.getCatalCodeList(<generated>)at com.keytop.superpark.ks.ormanage.controller.CustomLotController.getCatalCodeList(CustomLotController.java:109)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)at java.lang.reflect.Method.invoke(Unknown Source)at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814)at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737)at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969)at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:860)at javax.servlet.http.HttpServlet.service(HttpServlet.java:635)at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)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 com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)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:197)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 com.keytop.superpark.common.support.filter.IndexFilter.doFilter(IndexFilter.java:29)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at com.keytop.superpark.common.support.security.SecurityFilter.invoke(SecurityFilter.java:38)at com.keytop.superpark.common.support.security.SecurityFilter.doFilter(SecurityFilter.java:32)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:125)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)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:478)at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)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:799)at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1457)at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'zhao'y%' ORDER BY create_time asc' at line 1at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974)at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1024)at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3188)at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3185)at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:181)at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:688)at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629)... 106 more一开始以为只要转义特殊字符即可，后面查询相关SQL注入的资料发现，ibatis虽然已经防止了大部分的SQL注入，但是like方式还是有缺陷。
后面查询到的方式是
Ibatis like 查询防止SQL注入的方法
数据库用的是MySQL，所以采用concat方式
mysql: select * from tbl_school where school_name like concat(’%’,#name#,’%’)
原SQL：
StringBuffer sql = new StringBuffer("SELECT lot_code as CODE,lot_name as NAME FROM or_custom_lot where 1=1");if (StringUtils.hasText(name)) {
sql.append(" and lot_name like '%").append(name).append("%'");}sql.append(" ORDER BY create_time asc");
更改:
StringBuffer sql = new StringBuffer("SELECT lot_code as CODE,lot_name as NAME FROM or_custom_lot where 1=1");if (StringUtils.hasText(name)) {
sql.append(" and lot_name like CONCAT('%',:name,'%')");}sql.append(" ORDER BY create_time asc");Map param = new HashMap();param.put("name",name);
原查询：
更改后查询正常，不再报错：
SQL注入相关资料：
Mybatis中防止Sql注入
mybatis是如何防止SQL注入的