#java #sql #oracle #jdbc #plsql
Вопрос:
Вот мой код : Когда я тестирую с одинарной кавычкой для полей name и gender получаю приведенное ниже исключение:
}
sql = "SELECT * FROM dummy.employee_details " "WHERE name = decode('" name "','',name,'" name
"') AND " "fname = decode('" fname "','',fname,'" fname "') AND " "gender = decode('"
gender "','',gender,'" gender "') AND " "Age = decode(" Age ",null,Age," Age
") AND " "startDate = decode(to_date('" query_date
"','yyyy-mm-dd'),'null',startDate,to_date('" query_date "','yyyy-mm-dd')) AND "
"salary = decode(" salary ",null,salary," salary ")";
} else {
sql = "SELECT * FROM dummy.employee_details " "WHERE name = decode('" name "','',name,'" name
"') AND " "fname = decode('" fname "','',fname,'" fname "') AND " "gender = decode('"
gender "','',gender,'" gender "') AND " "Age = decode(" Age ",null,Age," Age
") AND " "salary = decode(" salary ",null,salary," salary ")";
}
Исключение
java.sql.SQLSyntaxErrorException: ORA-00938: not enough arguments for function
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
at com.fadel.model.dao.impl.EmployeeDAOImpl.advanceSearch(EmployeeDAOImpl.java:177)
at com.fadel.model.bo.Impl.EmployeeBOImpl.advanceSearch(EmployeeBOImpl.java:47)
at com.fadel.model.view.backing.EmployeeController.listEmployees(EmployeeController.java:151)
at com.fadel.model.view.backing.EmployeeController.getEmployees(EmployeeController.java:129)
at sun.reflect.GeneratedMethodAccessor483.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at javax.el.BeanELResolver.getValue(BeanELResolver.java:88)
at com.sun.faces.el.DemuxCompositeELResolver._getValue(Unknown Source)
at com.sun.faces.el.DemuxCompositeELResolver.getValue(Unknown Source)
at org.apache.el.parser.AstValue.getValue(AstValue.java:169)
at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:190)
at com.sun.faces.facelets.el.TagValueExpression.getValue(Unknown Source)
at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:194)
at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:182)
at org.richfaces.component.UISequence.getValue(UISequence.java:180)
at org.richfaces.component.UISequence.createExtendedDataModel(UISequence.java:114)
at org.richfaces.component.UIDataTableBase.createExtendedDataModel(UIDataTableBase.java:194)
at org.richfaces.component.UIDataAdaptor.getExtendedDataModel(UIDataAdaptor.java:467)
at org.richfaces.component.UIDataAdaptor.getRowCount(UIDataAdaptor.java:518)
at sun.reflect.GeneratedMethodAccessor414.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at javax.faces.component.UIComponentBase$AttributesMap.get(UIComponentBase.java:2387)
at org.richfaces.DataScrollerUtils.eval(DataScrollerUtils.java:86)
at org.richfaces.DataScrollerUtils.getRowCount(DataScrollerUtils.java:81)
at org.richfaces.event.DataTablePreRenderListener.processEvent(DataTablePreRenderListener.java:124)
at javax.faces.event.SystemEvent.processListener(SystemEvent.java:108)
at javax.faces.event.ComponentSystemEvent.processListener(ComponentSystemEvent.java:118)
at com.sun.faces.application.ApplicationImpl.processListeners(Unknown Source)
at com.sun.faces.application.ApplicationImpl.invokeListenersFor(Unknown Source)
at com.sun.faces.application.ApplicationImpl.publishEvent(Unknown Source)
at com.sun.faces.application.ApplicationImpl.publishEvent(Unknown Source)
at javax.faces.component.UIComponentBase.encodeBegin(UIComponentBase.java:856)
at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeRecursive(Unknown Source)
at com.sun.faces.renderkit.html_basic.GroupRenderer.encodeChildren(Unknown Source)
at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:889)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1857)
at com.sun.faces.context.PartialViewContextImpl$PhaseAwareVisitCallback.visit(Unknown Source)
at org.richfaces.context.MetaComponentEncodingVisitCallback.visit(MetaComponentEncodingVisitCallback.java:83)
at org.richfaces.context.BaseExtendedVisitContext.invokeVisitCallback(BaseExtendedVisitContext.java:103)
at org.richfaces.context.ExtendedRenderVisitContext.invokeVisitCallback(ExtendedRenderVisitContext.java:65)
at javax.faces.component.UIComponent.visitTree(UIComponent.java:1690)
at javax.faces.component.UIComponent.visitTree(UIComponent.java:1701)
at javax.faces.component.UIComponent.visitTree(UIComponent.java:1701)
at javax.faces.component.UIForm.visitTree(UIForm.java:371)
at javax.faces.component.UIComponent.visitTree(UIComponent.java:1701)
at javax.faces.component.UIComponent.visitTree(UIComponent.java:1701)
at com.sun.faces.context.PartialViewContextImpl.processComponents(Unknown Source)
at com.sun.faces.context.PartialViewContextImpl.processPartial(Unknown Source)
at org.richfaces.context.ExtendedPartialViewContext.processPartial(ExtendedPartialViewContext.java:264)
at javax.faces.component.UIViewRoot.encodeChildren(UIViewRoot.java:1004)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1857)
at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(Unknown Source)
at com.sun.faces.application.view.MultiViewHandler.renderView(Unknown Source)
at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:337)
at com.sun.faces.lifecycle.RenderResponsePhase.execute(Unknown Source)
at com.sun.faces.lifecycle.Phase.doPhase(Unknown Source)
at com.sun.faces.lifecycle.LifecycleImpl.render(Unknown Source)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:647)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684)
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)
Caused by: Error : 938, Position : 120, Sql = SELECT * FROM dummy.employee_details WHERE name = decode('','',name,'') AND fname = decode('','',fname,'') AND gender = decode(''','',gender,''') AND Age = decode(null,null,Age,null) AND salary = decode(null,null,salary,null), OriginalSql = SELECT * FROM dummy.employee_details WHERE name = decode('','',name,'') AND fname = decode('','',fname,'') AND gender = decode(''','',gender,''') AND Age = decode(null,null,Age,null) AND salary = decode(null,null,salary,null), Error Msg = ORA-00938: not enough arguments for function
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
... 96 more
Комментарии:
1. Используйте параметризованный запрос
2. Не объединяйте строки. Примените параметры, используя
?вместо этого, и вам никогда не придется иметь дело с экранирующими строками.3. Алекс, Цепеш, Спасибо за ответы, пожалуйста, дайте мне знать, как использовать расширенную систему цитирования q'[]’ в функции ДЕКОДИРОВАНИЯ, которая будет полезна здесь для решения проблемы.
4. Свяжите свои переменные. СВЯЖИТЕ СВОИ ПЕРЕМЕННЫЕ! Серьезно, зачем подвергать свой код SQL-инъекции, когда вы можете связать свои переменные… как уже предлагалось? Кроме того, база данных будет вам благодарна, так как ей не нужно выполнять жесткий анализ запроса каждый раз, когда вы вводите новые значения. Не объединяйте свои ценности, связывайте их.
Ответ №1:
Проверьте это:
выберите «парень кобера» в качестве имени из dual;—распечатать парня кобера
вы могли бы использовать в коде ur простую замену
имя = имя.заменить(«‘»,»»»);