MS SQL Server stored procedure error 'incorrect syntax'

Question

Hi,

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

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

Thanks R.

Answer 1

You aren't including quotations for your varchar columns.

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.

Answer 2

you need to put single quotes around the ClientIP value.

Answer 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

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.

Here's an example of how you can protect your 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'

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.

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.