Insert Error

FishBoy · April 6th, 2008, 10:46 AM

I have a client who's getting an error that I cannot duplicate.

Error Message:

Code:

Microsoft OLE DB Provider for SQL Server error '80040e14' 

Line 1: Incorrect syntax near 's'. 

/Private/couponcodesadd2.asp, line 19

'

couponcodesadd2.asp:

Code:

<%
varpromoamount = 0.00
promocode = Request.Form("promocode")
promodesc = Request.Form("promodesc")
promotype = Request.Form("promotype")
varpromoamount = varpromoamount + Request.Form("promoamount")
promoexpiration = Request.Form("promoexpiration")

adCmdText = 1

strInsert = "insert_PromoCodes_1 '" & promocode & "', '" & promodesc & "', '" & promotype & "', '" & CSng(varpromoamount) & "', '" & promoexpiration & "';"
'response.write promotype &" | " &promoamount    
set objCmd = Server.CreateObject("ADODB.Command")
set objCmd.ActiveConnection = objConn
objCmd.CommandText = strInsert
objCmd.CommandType = adCmdText
objCmd.Execute 'Line 19

Set objCmd = Nothing
%>

insert_PromoCodes_1:

Code:

ALTER PROCEDURE [dinners_sqladmin].[insert_PromoCodes_1]
    (@promocode_1     [varchar](20),
     @promodesc_2     [varchar](512),
     @promotype_3     [varchar](50),
     @promoamount_4     [decimal](18,2),
     @promoexpiration_5     [smalldatetime])

AS INSERT INTO [dinners_dbtd].[dinners_sqladmin].[PromoCodes] 
     ([promocode],
     [promodesc],
     [promotype],
     [promoamount],
     [promoexpiration]) 
 
VALUES 
    (@promocode_1,
     @promodesc_2,
     @promotype_3,
     @promoamount_4,
     @promoexpiration_5)

I've tried executing the insert from the web pages and directly through SQL Mgt Studio and cannot duplicate the error.

Any help is appreciated.

Imar · April 6th, 2008, 11:43 AM

Your client is probably inserting something that contains an ' like O'Brien.

Since a ' has special meaning in SQL, this breaks things. The fix is easy: just replace a single ' for two before you send the data to the database. E.g.:

promocode = Replace(promocode, ".", "''")

Note that this is only a short term fix. Google for "SQL Injection" to learn why this is not only a client's nuisance, but also a thread to your application and server. Instead, you should look at solid escaping techniques / validation routines and parameterized queries. You need to use the Parameters collection of the Command object to add new parameters and provide their values.

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.

FishBoy · April 7th, 2008, 06:45 AM

That was it! Thanks for the help and the SQL Injection info.