问题描述
我正在尝试使用Parameters保护我的INSERT语句免于SQL注入,但由于某些原因,我遇到了错误:Parameter object is improperly defined. Inconsistent or incomplete information was provided...
I am trying to protect my INSERT statement from SQL injection using Parameters, but for some reason I am getting the error: Parameter object is improperly defined. Inconsistent or incomplete information was provided...
但是,我不知道是什么原因造成的.
Though, I have no clue what is causing it.
我的SQL语句如下:
Set spSQL = Server.CreateObject("ADODB.Command")
Set spSQL.ActiveConnection = con_vhs
vrdSQL = "INSERT INTO boekingen ([Order],[Positie],[Tariefnummer],[Relatie],[Datum],[AantalEenheden],[Omschrijving],[Bedrag],[Totaal],[Status]) VALUES (@Order,@Pos,@Tar,@Rel,@Datum,@Aantal,@Omsch,@Bedrag,@Totaal,@Status)"
spSQL.commandtext= vrdSQL
spSQL.Parameters.Append(spSQL.CreateParameter("@Order", adInteger,,,1506))
spSQL.Parameters.Append(spSQL.CreateParameter("@Pos", adVarWChar,,10,"0"))
spSQL.Parameters.Append(spSQL.CreateParameter("@Tar", adVarWChar,,50,"VRD"))
spSQL.Parameters.Append(spSQL.CreateParameter("@Rel", adInteger,,,4020))
spSQL.Parameters.Append(spSQL.CreateParameter("@Datum", adDate,,,iDatumTotaal))
spSQL.Parameters.Append(spSQL.CreateParameter("@Aantal", adSingle,,,"5,25"))
spSQL.Parameters.Append(spSQL.CreateParameter("@Omsch", adVarWChar,,150,OmschrijvingGoed))
spSQL.Parameters.Append(spSQL.CreateParameter("@Bedrag", adDecimal,,,sBedrag))
spSQL.Parameters.Append(spSQL.CreateParameter("@Totaal", adDecimal,,,sTotaal))
spSQL.Parameters.Append(spSQL.CreateParameter("@Status", adInteger,,,StatusVRD))
Dim oPrm
For Each oPrm In spSQL.Parameters
If oPrm.Type = adDecimal Then
oPrm.NumericScale = 2
oPrm.Precision = 17
End If
Next
set rst= spSQL.execute(vrdSQL)
一些参数值是硬编码设置的(仅出于测试目的),而有些则是使用变量设置的.我在第一个附加参数行上却收到了错误.我在做什么错呢?
Some parameters values are set hard-coded (just for test purposes) and some are set using variables. I am getting the error however already on the first append parameter line.. What am I doing wrong?
一些其他信息:
- 我正在将数据插入SQL 2012 Server.
- SQL服务器中的类型如下:
@Order = int <br/>
@Pos = nvarchar(10) <br/>
@Tar = nvarchar(50) <br/>
@Rel = int <br/>
@Datum = datetime2(0) <br/>
@Aantal = real <br/>
@Omsch = nvarchar(150) <br/>
@Bedrag = money (will be changed to Decimal(17,2) soon <br/>
@Totaal = money (will be changed to Decimal(17,2) soon) <br/>
@Status = int
更新2
Set spSQL = Server.CreateObject("ADODB.Command")
Set spSQL.ActiveConnection=con_vhs
spSQLCommandType = adCmdText
vrdSQL="INSERT INTO boekingen ([Order],[Positie],[Tariefnummer],[Relatie],[Datum],[AantalEenheden],[Omschrijving],[Bedrag],[Totaal],[Status]) VALUES (?,?,?,?,?,?,?,?,?,?)"
spSQL.commandtext= vrdSQL
spSQL.Parameters.Append spSQL.CreateParameter("@Order", adInteger,adParamInput,4)
spSQL.Parameters.Append spSQL.CreateParameter("@Positie", adVarWChar,adParamInput,10)
spSQL.Parameters.Append spSQL.CreateParameter("@Tariefnummer", adVarWChar,adParamInput,50)
spSQL.Parameters.Append spSQL.CreateParameter("@Relatie", adInteger,adParamInput,4)
spSQL.Parameters.Append spSQL.CreateParameter("@Datum", adDate,adParamInput,0)
spSQL.Parameters.Append spSQL.CreateParameter("@AantalEenheden", adSingle,adParamInput,4)
spSQL.Parameters.Append spSQL.CreateParameter("@Omschrijving", adVarWChar,adParamInput,150)
spSQL.Parameters.Append spSQL.CreateParameter("@Bedrag", adDecimal,adParamInput,0)
spSQL.Parameters.Append spSQL.CreateParameter("@Totaal", adDecimal,adParamInput,0)
spSQL.Parameters.Append spSQL.CreateParameter("@Status", adInteger,adParamInput,4)
spSQL.Parameters("@Order").Value = 1506
spSQL.Parameters("@Positie").Value = "0"
spSQL.Parameters("@Tariefnummer").Value = "VRD"
spSQL.Parameters("@Relatie").Value = 4020
spSQL.Parameters("@Datum").Value = iDatumTotaal
spSQL.Parameters("@AantalEenheden").Value = TestAantal
spSQL.Parameters("@Omschrijving").Value = OmschrijvingGoed
spSQL.Parameters("@Bedrag").Value = sBedrag
spSQL.Parameters("@Totaal").Value = sTotaal
spSQL.Parameters("@Status").Value = StatusVRD
Dim oPrm
For Each oPrm In spSQL.Parameters
If oPrm.Type = adDecimal Then
oPrm.NumericScale = 2
oPrm.Precision = 17
End If
Next
set rst= spSQL.execute(vrdSQL)
更新2,删除了.append
周围的括号,并在参数中添加了正确的大小值.仍然出现错误:
Update 2, Removed the parenthesis around the .append
and added the right size values in the parameters. Still getting the error:
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.. . ON spSQL.Parameters.Append spSQL.CreateParameter("@Order", adInteger,adParamInput,4)
更新3
此更新是在我的global.asa文件中插入meta标签之后.
This update was after inserting the meta tag in my global.asa file.
我使用以下内容更新了global.asa文件:
I updated my global.asa file with the following:
<!-- METADATA TYPE="typelib" UUID="00000200-0000-0010-8000-00AA006D2EA4" -->
global.asa文件现在如下所示:
The global.asa file now looks as follows:
<script language="VBScript" runat="Server">
Sub Session_OnStart()
<!-- METADATA TYPE="typelib" UUID="00000200-0000-0010-8000-00AA006D2EA4" -->
Session.Timeout = 480
End Sub
</script>
参数代码保持不变.现在,我在执行过程中(高兴地)遇到了另一个错误:
The code of the parameters remained the same. Now I am getting (happily) a different error on the execute:
[Microsoft][ODBC SQL Server Driver]Optional feature not implemented. . ON set rst= spSQL.execute(vrdSQL)
推荐答案
好吧,在与Lankymart进行了很多讨论之后,我们继续进行了聊天,最终我将其修复.
Alright, after much discussion with Lankymart, which continued in the chat, I finally got it fixed.
由于仅一次调整并不能解决错误,所以请过帐所有调整后的错误.
Because the error was not fixed with just one adjustment, ill post all the adjustments made.
- 首先,我删除了
spSQL.Parameters.Append(spSQL.CreateParameter("@Order", adInteger,,,1506))
的第一个(不必要的)括号 - 第二,我用问号替换了SQL字符串中的@vars.
-
然后我分别添加了Parameters值,还添加了
spSQLCommandType = adCmdText
(在此链接中指出: stackoverflow.com /a/22037613/692942 )
- First of all I removed the first (unnecessary) parenthesis of
spSQL.Parameters.Append(spSQL.CreateParameter("@Order", adInteger,,,1506))
- Secondly, I replaced the @vars in my SQL string with question marks.
Then I separately added the Parameters values and also added the
spSQLCommandType = adCmdText
(pointed out in this link: stackoverflow.com/a/22037613/692942)
我还将所有参数数据类型的大小更改为正确的大小(使用此链接:数据类型映射),而不是默认为空或0.
I also changed the SIZES of all the parameter data types to the right size (using this link: Data type mapping) instead of default nothing or 0.
但是,最大的问题是由于不包括用于处理我的ADO参数的正确的DDL文件引起的.这已添加到global.asa文件中.<!-- METADATA TYPE="typelib" UUID="00000200-0000-0010-8000-00AA006D2EA4"-->
The biggest problem however was caused by not including the right DDL file for handling my ADO parameters. This was added in the global.asa file. <!-- METADATA TYPE="typelib" UUID="00000200-0000-0010-8000-00AA006D2EA4"-->
整个固定"代码如下:
Set spSQL = Server.CreateObject("ADODB.Command")
Set spSQL.ActiveConnection=con_vhs
spSQL.CommandType = adCmdText
vrdSQL="INSERT INTO boekingen ([Order],[Positie],[Tariefnummer],[Relatie],[Datum],[AantalEenheden],[Omschrijving],[Bedrag],[Totaal],[Status]) VALUES (?,?,?,?,?,?,?,?,?,?)"
spSQL.commandtext= vrdSQL
spSQL.Parameters.Append spSQL.CreateParameter("@Order",adInteger,adParamInput,4)
spSQL.Parameters.Append spSQL.CreateParameter("@Positie", adVarWChar,adParamInput,10)
spSQL.Parameters.Append spSQL.CreateParameter("@Tariefnummer", adVarWChar,adParamInput,50)
spSQL.Parameters.Append spSQL.CreateParameter("@Relatie", adInteger,adParamInput,4)
spSQL.Parameters.Append spSQL.CreateParameter("@Datum", adDBTimeStamp,adParamInput,0)
spSQL.Parameters.Append spSQL.CreateParameter("@AantalEenheden", adSingle,adParamInput,4)
spSQL.Parameters.Append spSQL.CreateParameter("@Omschrijving", adVarWChar,adParamInput,150)
spSQL.Parameters.Append spSQL.CreateParameter("@Bedrag", adDecimal,adParamInput,0)
spSQL.Parameters.Append spSQL.CreateParameter("@Totaal", adDecimal,adParamInput,0)
spSQL.Parameters.Append spSQL.CreateParameter("@Status", adInteger,adParamInput,4)
spSQL.Parameters("@Order").Value = 1506
spSQL.Parameters("@Positie").Value = "0"
spSQL.Parameters("@Tariefnummer").Value = "VRD"
spSQL.Parameters("@Relatie").Value = 4020
spSQL.Parameters("@Datum").Value = iDatumTotaal
spSQL.Parameters("@AantalEenheden").Value = TestAantal
spSQL.Parameters("@Omschrijving").Value = OmschrijvingGoed
spSQL.Parameters("@Bedrag").Value = sBedrag
spSQL.Parameters("@Totaal").Value = sTotaal
spSQL.Parameters("@Status").Value = StatusVRD
Dim oPrm
For Each oPrm In spSQL.Parameters
If oPrm.Type = adDecimal Then
oPrm.NumericScale = 2
oPrm.Precision = 17
End If
Next
Call spSQL.execute(adExecuteNoRecords)
感谢Lankymart提供的很棒的帮助来解决此问题!
Thanks to Lankymart for the awesome help fixing this problem!
这篇关于在ASP CLASSIC中使用SQL参数,对象定义不正确的错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!