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

2022-01-16 00:00:00 parameters asp-classic sql-server

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

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 服务器.
  • SQL server 中的类型如下:
@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:

参数类型错误、超出可接受范围或相互冲突......ON spSQL.Parameters.Append spSQL.CreateParameter("@Order", adInteger,adParamInput,4)

更新 3

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

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]可选功能未实现..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 替换为问号.

  • 然后我分别添加了参数值，还添加了 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)

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

然而，最大的问题是由于没有包含正确的 DDL 文件来处理我的 ADO 参数.这是在 global.asa 文件中添加的.

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!

相关文章