ERROR: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar Java Spring MVC
Solution 1
The SQL query you provided wasn't proper SQL, as the error suggested:
try String sql = "select * from assignment where username='"+username+"';";
instead of String sql = "select * from assignment where username="+username;
Solution 2
You should use query parameter for username to have it correctly quoted AND SQL escaped... your concatenation is a potential SQL injection entry point if username comes from any external source (UI, ...) you don't fully control and will fail at first username containing a single quote if caller doesn't escape correctly.
Solution 3
I have the same issue, solved it by commenting and uncommenting one of my dependencies which made my gradle file work properly.
Agnes Palit
I enjoy being challenged and engaging with the project that requires me to work outside my comfort in under pressure and knowledge set, as continuing to learn languages and development techniques are important to me
Updated on March 13, 2020Comments
-
Agnes Palit about 4 years
I am trying to create a web application using Java Spring MVC. This web mostly will do CRUD function (Create-Read-Update-Delete).
Recently, I got this error:
HTTP Status 500 - Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from assignment where username=reza]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause'
This is my DAO file:
@Override public List < Assignment > showAllAssignment(String username) { String sql = "select * from assignment where username=" + username; return jdbcTemplate.query(sql, new AssignmentMapper()); }
This is my controller
@RequestMapping(value = "/showAllAssignment/{reqUserName}/show", method = RequestMethod.GET) public ModelAndView showAllAssignment(@PathVariable("reqUserName") String reqUserName) { List < Assignment > list = new ArrayList < Assignment > (); list = assignmentService.showAllAssignment(reqUserName); ModelAndView mav = new ModelAndView("show_All_Assignments"); mav.addObject("assignment", list); return mav; }
Further error that I got:
2018-05-03 01:55:08,232 [org.springframework.web.servlet.mvc.method.annotation.ExceptionHandlerExceptionResolver]-[DEBUG] Resolving exception from handler [public org.springframework.web.servlet.ModelAndView org.assignment.controller.AssignmentController.showAllAssignment(java.lang.String)]: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from assignment where username=reza]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause' 2018-05-03 01:55:08,234 [org.springframework.web.servlet.mvc.annotation.ResponseStatusExceptionResolver]-[DEBUG] Resolving exception from handler [public org.springframework.web.servlet.ModelAndView org.assignment.controller.AssignmentController.showAllAssignment(java.lang.String)]: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from assignment where username=reza]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause' 2018-05-03 01:55:08,234 [org.springframework.web.servlet.mvc.support.DefaultHandlerExceptionResolver]-[DEBUG] Resolving exception from handler [public org.springframework.web.servlet.ModelAndView org.assignment.controller.AssignmentController.showAllAssignment(java.lang.String)]: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from assignment where username=reza]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause' 2018-05-03 01:55:08,235 [org.springframework.web.servlet.DispatcherServlet]-[DEBUG] Could not complete request org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from assignment where username=reza]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause' at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:388) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:446) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456) at org.assignment.dao.AssignmentDaoImpl.showAllAssignment(AssignmentDaoImpl.java:67) at org.assignment.service.AssignmentServiceImpl.showAllAssignment(AssignmentServiceImpl.java:39) at org.assignment.controller.AssignmentController.showAllAssignment(AssignmentController.java:193) 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:870) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:776) 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:978) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:870) at javax.servlet.http.HttpServlet.service(HttpServlet.java:622) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:855) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1539) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1495) 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) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'reza' in 'where clause' 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:389) at com.mysql.jdbc.Util.getInstance(Util.java:372) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2489) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446) at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:433) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376) ... 42 more
The problem is I would like to select data inside my table assignment, where the username is define.
For example the
String username1 = 'reza';
But when I try to show all data using syntax in aboveselect * .... where username="+username1;
The result, the system read the 'reza' as column not as the value on column.Any one can help me to solve this problem?
-
Agnes Palit about 6 yearsI have check it 'green' your answer, but I still can not up vote, and i saw it because my reputation less than 15, I am sorry with that. But once my repotation more than 15, I will vote up you answer. Many thanks @Gewure
-
Gewure about 6 yearsdon't worry. glad i helped.
-
Agnes Palit about 6 yearsCould you please give the clear example of syntax, to show what you mean?
-
p3consulting about 6 years
-
Agnes Palit about 6 yearsI see, but I am sorry that maybe your answer does not meet what I need to solve my real problem. Actually the previous answer by @Gewure already solve my problem and now it works well. But thank you for your answer.
-
Gewure almost 4 yearshow is this correlated to gradle? What dependency caused this outcome?