如何更正查询语法

本文介绍了如何更正查询语法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用oracle 11g在vb6中设计一个程序。 （作为OLE DB（ADO））和Crystal Report（作为报表编写者）。 我写了以下查询分类帐报表。

I am designing a program in vb6 with oracle 11g. ( as OLE DB (ADO)), and Crystal Report (As report writer). I have writing following query for a ledger report.

sSQL = "SELECT 0 AS VoucherNo ," & "'" & txtFromDt & "'" & " AS VoucherDate , Vendors.VendorName, 'Balance Brought Forward' AS Narration , " _ & " NVL(DocumentNo, 0) AS DocumentNo, 0 AS Debit, 0 AS Credit, Sum(DR-CR) AS Balance " _ & " FROM Vouchers A INNER JOIN Vendors ON A.ClientID = Vendors.VendorID" _ & " WHERE ((A.VoucherDate< " & "'" & Format(Myfrm.FromDt, "DD-MMM-YY") & "'" & " AND A.ClientID =" & "'" & Myfrm.Combo1 & "'" & " ) And Vstatus = 'Y' ) " _ & " GROUP BY DocumentNo, Voucherdate, Vendors.VendorName,'Balance Brought Forward' " _ & " UNION SELECT A.VoucherNo , A.VoucherDate , Vendors.VendorName, A.Narration, A.DocumentNo , " _ & " A.DR AS Debit, A.CR AS Credit, (Select Sum(DR-CR) FROM Vouchers Where Vouchers.ClientID=A.ClientID And " _ & " VoucherNo<= A.VoucherNo AND VStatus='Y' ) As Balance " _ & " FROM Vouchers A INNER JOIN Vendors ON A.ClientID = Vendors.VendorID " _ & " WHERE ((A.VoucherDate Between " & "'" & Format(Myfrm.FromDt, "DD-MMM-YY") & "'" & " And " & "'" & Format(Myfrm.ToDt, "DD-MMM-YY") & "'" & " )" _ & " And A.ClientID = " & "'" & Myfrm.Combo1 & "'" & " And Vstatus = 'Y') " _ & " GROUP BY A.VoucherNo, A.VoucherDate, A.ClientID, Vendors.VendorName, A.Narration,A.DocumentNo, A.DR, A.CR" _ & " Order by VoucherNo "

它在SQL Plus（Oracle）中工作正常，但是当我从vb6表单运行此查询时，它会出现以下错误< b> ORA-01790表达式必须与相应的表达式具有相同的数据类型。 < / B个我必须尽力而为，但未能解决。任何人都可以帮助查询中缺少的东西。 提前感谢。

It works fine in SQL Plus (Oracle), but when i run this query from vb6 form, It give the following error <b> ORA-01790 Expression must have same datatype as corresponding expression. </b> I have to tried by best but failed to fix. Can anybody help what is missing in query. Thank in advance.

推荐答案

帮自己一个忙：不要连接字符串以构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。 这可能会同时解决您的问题！ Do yourself a favour: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. The chances are that that will fix your problem at the same time!

您的方法从一开始就是错误的。通过串联从UI获取的字符串组成的查询。不仅重复的字符串连接是低效的（因为字符串是不可变的;我是否必须解释为什么它会使重复连接变坏？），但是有更重要的问题：它打开了通向良好的大门已知的漏洞称为 SQL注入。 这是它的工作原理： xkcd/327 。 你明白了吗？从控件中获取的字符串可以是任何东西，包括......一段SQL代码。 怎么办？只需阅读有关此问题和主要补救措施：参数化语句： en.wikipedia/ wiki / SQL_injection 。 使用ADO.NET，使用：msdn.microsoft/en-us/library/ff648339.aspx 。 请参阅我过去的答案有更多细节： 在com.ExecuteNonQuery中更新EROR（ ）; ， 嗨名字没有显示在名称中？。

-SA

Your approach is wrong from the very beginning. The query composed by concatenation with strings taken from UI. Not only repeated string concatenation is inefficient (because strings are immutable; do I have to explain why it makes repeated concatenation bad?), but there is way more important issue: it opens the doors to a well-known exploit called SQL injection. This is how it works: xkcd/327. Are you getting the idea? The string taken from a control can be anything, including… a fragment of SQL code. What to do? Just read about this problem and the main remedy: parametrized statements: en.wikipedia/wiki/SQL_injection. With ADO.NET, use this: msdn.microsoft/en-us/library/ff648339.aspx. Please see my past answers for some more detail: EROR IN UPATE in com.ExecuteNonQuery();, hi name is not displaying in name?.

—SA