MS SQL Server存储过程错误'语法不正确'

This is my stored procedure:

这是我的存储过程：

ALTER PROCEDURE its.sp_WriteTransaction

    (
    @LoginID int, 
    @PersonID int, 
    @BusinessID int, 
    @TransType smallint, 
    @LastHost varchar(15), 
    @TransData varchar(255)
)

AS

DECLARE @TransDate DATETIME
SET @TransDate = GETDATE()

INSERT INTO Transactions (LoginID, PersonID, BusinessID, TransDate, TransType, LastHost, TransData)
VALUES (@LoginID, @PersonID, @BusinessID, @TransDate, @TransType, @LastHost, @TransData) 
RETURN

This is my calling line:

这是我的主叫：

    sql = "sp_WriteTransaction" & " " & Session("UserID") & "," & Session("PersonID") & "," & Session("bizID") & "," & TransType & "," & ClientIP & "," & TransData

But everytime I run it I get the following error message:

但每次我运行它时都会收到以下错误消息：

Error message:

错误信息：

Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E14) Line 1: Incorrect syntax near '.0'. /etearsheets/authorize/CheckAccess.asp, line 1163

错误类型：用于SQL Server的Microsoft OLE DB提供程序（0x80040E14）第1行：“.0”附近的语法不正确。 /etearsheets/authorize/CheckAccess.asp，第1163行

What is wrong with the IP format that is causing the '.0' error and how do I correct it?

导致'.0'错误的IP格式有什么问题，我该如何纠正？

Thanks R.

谢谢R.

3 个解决方案

#1

You aren't including quotations for your varchar columns.

您没有包含varchar列的引用。

Try this:

尝试这个：

sql = "sp_WriteTransaction" & " " & Session("UserID") & "," & _
    Session("PersonID") & "," & Session("bizID") & "," & _
    TransType & ",'" & ClientIP & "','" & TransData & "'"

It's failing out on the IP address, since 127.0.0.1 is not a number. You're currently trying to pass it as float, which only uses one decimal. Encompassing it in single quotes forces SQL to parse it as a string.

它在IP地址上失败，因为127.0.0.1不是数字。您当前正尝试将其作为float传递，它只使用一个小数。用单引号括起来强制SQL将其解析为字符串。

#2

you need to put single quotes around the ClientIP value.

你需要在ClientIP值周围加上单引号。

#3

Like the others said, you're not single quoting parameters.

像其他人说的那样，你不是单引号参数。

Assuming...

假设...

Session("UserID") = 0000
Session("PersonID") = 4321
Session("bizID") = 1234
TransType = "GET"
ClientIP = "192.168.1.1"
TransData = "xyz"

then executing the following...

然后执行以下...

sql = "sp_WriteTransaction" & " " & Session("UserID") & "," & Session("PersonID") & "," & Session("bizID") & "," & TransType & "," & ClientIP & "," & TransData
response.write(sql)

would yield...

会屈服......

sp_WriteTransaction 0,4321,1234,GET,192.168.1.1,xyz

sp_WriteTransaction 0,4321,1234，GET，192.168.1.1，xyz

What's more troubling is that you're passing unencoded strings to SQL because this leaves you vulnerable to SQL Injection attacks. In this case it looks like the data might all be derived with no client origin but considering the nature/naivety of your question I suspect you are probably vulnerable elsewhere.

更令人不安的是，您将未编码的字符串传递给SQL，因为这会使您容易受到SQL注入攻击。在这种情况下，看起来数据可能都是在没有客户来源的情况下得出的，但考虑到问题的性质/天真，我怀疑你可能在其他地方容易受到攻击。

Here's an example of how you can protect your SQL

这是一个如何保护SQL的示例

Session("UserID") = 11111
Session("PersonID") = 4321
Session("bizID") = 1234
TransType = "GET"
ClientIP = "192.168.1.1"
TransData = "xyz"

sql = "sp_WriteTransaction {0},{1},{2},{3},{4},{5}"
parameters = Array(Session("UserID"),Session("PersonID"),Session("bizID"),TransType,ClientIP,TransData)

Function BuildSQL(query, params)
    Dim result : result = query

    If Not IsArray(params) Then
        BuildSQL = Null
        Exit Function
    End If

    Dim i
    For i = lbound(params) to ubound(params)
        result = replace(result,"{" & i & "}",SQLEncode(params(i)))
    Next

    BuildSQL = result
End Function

Function SQLEncode (uVar)
    If IsNull(uVar) Then
      SQLEncode = "null"
    Else
      SQLEncode = "'" & replace(uVar,"'","''") & "'"
    End If
End Function

Response.Write BuildSQL("sp_WriteTransaction {0},{1},{2},{3},{4},{5}",parameters)

This code outputs the following...

此代码输出以下内容......

sp_WriteTransaction '11111','4321','1234','GET','192.168.1.1','xyz'

sp_WriteTransaction'11111'，'4321'，'1234'，'GET'，'192.168.1.1'，'xyz'

You could take this a step further by putting SQLEncode and BuildSQL into their own file DataAccess.inc and making it available in all of your ASP files with an include statement.

您可以通过将SQLEncode和BuildSQL放入他们自己的文件DataAccess.inc并使用include语句在所有ASP文件中使用它来更进一步。

e.g.

例如

<!-- #include file="DataAccess.inc"-->

To do this you'll need to have Server Side Includes enabled in IIS and make sure the relative path in the #include statement is correct.

为此，您需要在IIS中启用服务器端包含，并确保#include语句中的相对路径正确。

#1