CODEWITHSUNDEEP
javasqlpostgresqljdbc
gklaxman
gklaxman

Reputation: 165

Syntax Error at or near "00" at Position: 138

So I am trying to extract information from a PostgreSQL Database. Below is the method which extracts the data :

public ResultSet dashboardQuerySurveyWithSelectedActions(String startDate, 
        String endDate, String agents) throws SQLException {
    Connection connection = super.getNewConnection();
    Statement statement = connection.createStatement();
    String query = String.format("SELECT surveys_nps_rating, survey_agent_name, surveys_stream_item_key "
            + "FROM public.surveys "
            + "WHERE surveys_response_date BETWEEN %s AND %s"
            + "AND survey_agent_name IN %s", startDate, endDate, agents);
    ResultSet resultSet = statement.executeQuery(query);
    connection.close();
    return resultSet;
}

following is the error what i get when i call this method:

('Honorine') - Parameter for Agent
2017-12-19 18:30:00 UTC - Start Date
2017-12-21 18:29:59 UTC - End Date



org.postgresql.util.PSQLException: ERROR: syntax error at or near "00"
  Position: 138
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:303)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:289)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:266)
    at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:233)
    at application.repository.SpredfastSurveysRepository.dashboardQuerySurveyWithSelectedActions(SpredfastSurveysRepository.java:48)
    at application.controller.ReportController.getDashboardOutput(ReportController.java:261)
    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:205)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:635)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    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.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: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.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
    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: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 org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
    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: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:803)
    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:1459)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)

I have ran this query in PGadmin and the query runs fine. I cannot find any syntax error in the query. Any help is highly appreciated.

Upvotes: 2

Views: 3067

Answers (2)

Youcef LAIDANI
Youcef LAIDANI

Reputation: 60046

Don't use statement like this, it can cause syntax errors or SQL Injection instead use PreparedStatement :

// (I assume that agents is a list)
//this will define placeholders for example (?, ?, ?) if you have 3 agents
String inClause = "(" + String.format("%0" + agents.size() + "d", 0)
    .replace("0", "?, ").replaceFirst(", $", ")");

String query = String.format("SELECT surveys_nps_rating, survey_agent_name, surveys_stream_item_key "
                + "FROM public.surveys "
                + "WHERE surveys_response_date BETWEEN ? AND ? "
                + "AND survey_agent_name IN %s", inClause);

The query should return something like this :

SELECT surveys_nps_rating, survey_agent_name, surveys_stream_item_key 
FROM public.surveys WHERE surveys_response_date BETWEEN ? AND ? 
AND survey_agent_name IN (?, ?, ?)

try (PreparedStatement pst = con.prepareStatement(query);) {
    pst.setDate(1, startDate);
    pst.setDate(2, endDate);
    //Then Iterate over the agents list and set the values to the placeholders
    for (int i = 0; i < agents.size(); i++) {
        pst.setString(i + 3, agents.get(i));//Why (i+3)? because you already set 2 params
    }

    //get your results
    ResultSet rs = pst.executeQuery();
}

Upvotes: 5

Matthias
Matthias

Reputation: 7521

Add single quotes around the passed in values:

String query = String.format("SELECT surveys_nps_rating, survey_agent_name, surveys_stream_item_key " +
            "FROM public.surveys " +
            "WHERE surveys_response_date BETWEEN '%s' AND '%s'" + 
            "AND survey_agent_name IN '%s'",startDate,endDate,agents);

Please keep in mind that your query could be vulnerable to SQL injections.

Upvotes: 3

Related Questions