在ASP CLASSIC中使用SQL参数，对象不正确地定义了错误

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...

我试图使用参数保护我的INSERT语句免受SQL注入,但由于某种原因我得到错误:参数对象未正确定义。提供的信息不一致或不完整......

Though, I have no clue what is causing it.

虽然,我不知道是什么导致它。

My SQL statement is as follows:

我的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?

一些参数值设置为硬编码(仅用于测试目的),一些参数值使用变量设置。我在第一个附加参数行上已经收到了错误..我做错了什么?

Some additional information:

一些其他信息:

I am inserting the data into a SQL 2012 Server.

我将数据插入SQL 2012 Server。

The types in the SQL server are as follows:

SQL Server中的类型如下:

@Order = int
@Pos = nvarchar(10)
@Tar = nvarchar(50)
@Rel = int
@Datum = datetime2(0)
@Aantal = real
@Omsch = nvarchar(150)
@Bedrag = money (will be changed to Decimal(17,2) soon
@Totaal = money (will be changed to Decimal(17,2) soon)
@Status = int

@Order = int @Pos = nvarchar(10)@Tar = nvarchar(50)@Rel = int @Datum = datetime2(0)@Aantal = real @Omsch = nvarchar(150)@Bedrag = money(将更改为Decimal (17,2)很快@Totaal = money(很快将改为Decimal(17,2))@Status = int

UPDATE 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)

Update 2, Removed the parenthesis around the .append and added the right size values in the parameters. Still getting the error:

更新2,删除.append周围的括号,并在参数中添加正确的大小值。仍然得到错误:

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)

参数类型错误,超出可接受的范围,或彼此冲突。 on spSQL.Parameters.Append spSQL.CreateParameter(“@ Order”,adInteger,adParamInput,4)

UPDATE 3

This update was after inserting the meta tag in my global.asa file.

此更新是在我的global.asa文件中插入元标记之后。

I updated my global.asa file with the following:

我用以下内容更新了我的global.asa文件:

  <!-- METADATA 
        TYPE="typelib" 
        UUID="00000200-0000-0010-8000-00AA006D2EA4"
-->

The global.asa file now looks as follows:

global.asa文件现在看起来如下:

<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)

[Microsoft] [ODBC SQL Server驱动程序]未实现可选功能。。 ON set rst = spSQL.execute(vrdSQL)

1 个解决方案

#1

Alright, after much discussion with Lankymart, which continued in the chat, I finally got it fixed.

好吧,经过与Lankymart的讨论,在聊天中继续进行,我终于解决了问题。

Because the error was not fixed with just one adjustment, ill post all the adjustments made.

因为仅通过一次调整就无法修复错误,所以所有调整都会生效。

First of all I removed the first (unnecessary) parenthesis of spSQL.Parameters.Append(spSQL.CreateParameter("@Order", adInteger,,,1506))

首先,我删除了spSQL.Parameters.Append的第一个(不必要的)括号(spSQL.CreateParameter(“@ Order”,adInteger ,,, 1506))

Secondly, I replaced the @vars in my SQL string with question marks.

其次,我用问号替换了我的SQL字符串中的@vars。

Then I separately added the Parameters values and also added the spSQLCommandType = adCmdText (pointed out in this link: *.com/a/22037613/692942)

然后我单独添加了参数值,还添加了spSQLCommandType = adCmdText(在此链接中指出:*.com/a/22037613/692942)
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.

我还将所有参数数据类型的SIZES更改为正确的大小(使用此链接:数据类型映射)而不是默认值或0。
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. 

然而,最大的问题是由于不包括用于处理我的ADO参数的正确DDL文件。这已添加到global.asa文件中。
A few smaller problems remained with one of them being a error on the execute which was changed to: Call spSQL.execute(adExecuteNoRecords)

一些较小的问题仍然存在,其中一个是执行错误,更改为:调用spSQL.execute(adExecuteNoRecords)

The last problem was caused because adDate wasn't recognized or viable for my SQL server 2012. I changed the ADO type adDate to adDBTimeStamp which solved the problem.

最后一个问题是由于我的SQL Server 2012无法识别或可行adDate而引起的。我将ADO类型adDate更改为adDBTimeStamp,这解决了问题。

The entire 'fixed' code is as follow:

整个“固定”代码如下:

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)

Thanks to Lankymart for the awesome help fixing this problem!

感谢Lankymart为解决这个问题提供了极好的帮助!

#1