the

the

关注
发信
关注(28)粉丝(399)

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

本文介绍了在ASP CLASSIC中使用SQL参数，对象定义不正确的错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试使用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参数，对象定义不正确的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

09-27 10:06
Powered by LMLPHP ©2024 the 0.004576