troubleshooting Question

ASP/SQL Stored Procedure

Avatar of dcass
dcassFlag for United States of America asked on
ASPMicrosoft SQL Server 2005
8 Comments1 Solution493 ViewsLast Modified:
OK - I got one question answered but still have a problem with my stored procedure.  I got it to insert, but it times out on update.
See if you see what might cause that - I checked for a record lock already and don't see it but I must be missing something - what am I not closing?
My second question is that I'm doing this because I'm being hit with sql injection scripts into each data field - isn't this giving them the user/pwd they need to get into the database anyway?  How does changing it over to a stored procedure work to keep sql injection attacks out?


    Set SQL_Cn = server.createobject("ADODB.Connection")
     SQL_Cn.Provider = "sqloledb"
     SQL_Cn.Properties("Data Source").Value = "10.10.1.12, 1045"
     SQL_Cn.Properties("Initial Catalog").Value = "Promo"
     SQL_Cn.Properties("User ID").Value = "promo"
     SQL_Cn.Properties("Password").Value = "promo"
     SQL_Cn.open
     Set SQL_Rs = Server.CreateObject("ADODB.Recordset")
     Set SQL_Rs2 = Server.CreateObject("ADODB.Recordset")
     SQL_RS.ActiveConnection = SQL_Cn

   strID = "555"  
   strNew = "Y"  
   if strID > "0" then intID = cint(strID)
   If intID > 0  then
        SQL = "SELECT * FROM " & strDB & "  WITH (NOLOCK) WHERE ID = " & intID  
        SQL_Rs.open SQL,SQL_Cn,3
        IF NOT(SQL_Rs.EOF) THEN
             strNew = "N"
        END IF
        SQL_Rs.close
   end if

        Set cmd = Server.CreateObject ("ADODB.Command")
        cmd.ActiveConnection = SQL_cn
        cmd.CommandType = &H0004
   If strNew = "N" then   'record exists
         cmd.CommandText = "updtcust"    'this times out
         cmd.Parameters.Append = cmd.CreateParameter("@id",3,&H0001,,intID)
      else
         cmd.CommandText = "insertcust"   'this works
    end if  
        cmd.Parameters.Append cmd.CreateParameter("@p1", 200,&H0001, 80, parmnm)
         cmd.Parameters.Append cmd.CreateParameter("@p2", 200,&H0001, 80, var1)
         cmd.Parameters.Append cmd.CreateParameter("@p3", 200,&H0001, 80, var2)
         cmd.Parameters.Append cmd.CreateParameter("@p4", 200,&H0001, 80, var3)
         cmd.execute
         set cmd = nothing

The stored procedure is:
USE [Promo]
GO
/****** Object:  StoredProcedure [dbo].[updtcust]    Script Date: 05/28/2008 23:03:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[updtcust]
       @id int,
       @p1 varchar(80),
       @p2 varchar(80),
       @p3 varchar(80),
       @p4 varchar(80)
AS
BEGIN
      update dbo.db6l1p1 set parmnm=@p1,var1=@p2,var2=@p3,var3=@p4 where [id] = @id
END
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros