search

emacarron / mybatis

Automatically exported from code.google.com/p/mybatis
0 stars 0 forks source link

Single quotes cause MySQLSyntaxErrorException #172

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
What version of the MyBatis are you using?
3.0.1

Please describe the problem.  Unit tests are best!

The following select generates the exception I list below:

  <select id='getChildrenById'
    parameterType='string'
    resultMap='staff.organization'>

    SELECT DISTINCT
      o.id as org_id
    FROM
      org o 
    WHERE
      o.parent_id = 2
      and o.has_classes = 'Y';
  </select>

In spite of the exception, the sql still gets executed and returns the correct 
result.  Also, I can take the above sql, run it on the mysql command line and 
it runs correctly.  If I change the 'Y' to "Y" no error is generated in the log 
and everything works fine.

What is the expected output? What do you see instead?

I would expect single quotes around string arguments to be legal while it 
appears double quotes are required.

Please provide any additional information below.

The stack trace:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'Y'; ')' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)
    at org.apache.log4j.jdbc.JDBCAppender.execute(JDBCAppender.java:178)
    at org.apache.log4j.jdbc.JDBCAppender.flushBuffer(JDBCAppender.java:250)
    at org.apache.log4j.jdbc.JDBCAppender.append(JDBCAppender.java:146)
    at org.apache.log4j.AppenderSkeleton.doAppend(AppenderSkeleton.java:230)
    at org.apache.log4j.helpers.AppenderAttachableImpl.appendLoopOnAppenders(AppenderAttachableImpl.java:65)
    at org.apache.log4j.Category.callAppenders(Category.java:203)
    at org.apache.log4j.Category.forcedLog(Category.java:388)
    at org.apache.log4j.Category.log(Category.java:853)
    at org.apache.commons.logging.impl.Log4JLogger.debug(Log4JLogger.java:177)
    at org.apache.ibatis.logging.commons.JakartaCommonsLoggingImpl.debug(JakartaCommonsLoggingImpl.java:27)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:33)
    at $Proxy15.execute(Unknown Source)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:39)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:94)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:60)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:59)
    at org.mybatis.spring.SqlSessionTemplate$2.doInSqlSession(SqlSessionTemplate.java:220)
    at org.mybatis.spring.SqlSessionTemplate$2.doInSqlSession(SqlSessionTemplate.java:219)
    at org.mybatis.spring.SqlSessionTemplate.execute(SqlSessionTemplate.java:173)
    at org.mybatis.spring.SqlSessionTemplate.execute(SqlSessionTemplate.java:155)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:217)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:210)
    at edu.hawaii.cafe.data.IbatisUserDao.getOrgChildrenById(IbatisUserDao.java:213)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:196)
    at $Proxy11.getOrgChildrenById(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy12.getOrgChildrenById(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:196)
    at $Proxy12.getOrgChildrenById(Unknown Source)
    at edu.hawaii.cafe.data.Organization.getChildren(Organization.java:27)
    at edu.hawaii.cafe.data.Organization$$EnhancerByCGLIB$$61536b8b.CGLIB$getChildren$4(<generated>)
    at edu.hawaii.cafe.data.Organization$$EnhancerByCGLIB$$61536b8b$$FastClassByCGLIB$$7348c81c.invoke(<generated>)
    at net.sf.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:215)
    at org.apache.ibatis.executor.loader.ResultObjectProxy$EnhancedResultObjectProxyImpl.intercept(ResultObjectProxy.java:60)
    at edu.hawaii.cafe.data.Organization$$EnhancerByCGLIB$$61536b8b.getChildren(<generated>)
    at edu.hawaii.cafe.ctl.coordinator.CoordinatorController.handleRequest(CoordinatorController.java:124)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:175)
    at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:421)
    at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:409)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:774)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:343)
    at edu.hawaii.cafe.Log4jMDCFilter.doFilter(Log4jMDCFilter.java:38)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.switchuser.SwitchUserFilter.doFilter(SwitchUserFilter.java:177)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:96)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.jasig.cas.client.session.SingleSignOutFilter.doFilter(SingleSignOutFilter.java:110)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
    at java.lang.Thread.run(Thread.java:680)
2010-11-12 15:50:25,046 DEBUG [sql.PreparedStatement] ==>  Executing: SELECT 
DISTINCT o.id as org_id FROM org o WHERE o.parent_id = 2 and o.has_classes = 
'Y';

Original issue reported on code.google.com by jgei...@gmail.com on 13 Nov 2010 at 2:02

GoogleCodeExporter commented 9 years ago 
This has nothing to do with MyBatis.  MyBatis does not parse SQL - it passes 
your SQL as-is to the database.

You don't need the semi-colon at the end of the statement - that might have 
something to do with it.

Original comment by jeffgbut...@gmail.com on 12 Dec 2010 at 10:33