Avatar of dcass
dcassFlag for United States of America asked on

ASP/SQL Stored Procedure

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
Microsoft SQL Server 2005ASP

Avatar of undefined
Last Comment
dcass

8/22/2022 - Mon
dosth

dosth

can you able to run this sp directly on server with values exec updtcust 555,'test','test','test','test'
ASKER
dcass

Seems to me, since they are hitting tables not in any form, that a deny sysobjects would be the best solution.  I can't find where to do that in SQL2005.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
dosth

can you start sql profiler and run the page so we can see the update procedure comes with all params it needs
ASKER
dcass

Need to find out where to deny sysobjects in sql2005 please -
ASKER
dcass

Sorry - need more instruction on both, dosth.
Have run in sql query before, it's just been a long time and am trying but not remembering.

Where do you deny access to sysobjects per user in sql2005?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
dcass

Got the sql query to run - sql2005 is different, but easy.  Runs fine.

Don't know how to start sql profilier or deny sysobjects.
ASKER CERTIFIED SOLUTION
dcass

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question