Store Procedures in ASP and SQL Injections

Create a stored procedure to add values


CREATE PROCEDURE dbo.MyProcedure
(
@prop1 varchar(255), --Fill in appropriate data types if necessary
@prop2 varchar(255),
@id int
)
AS
SET NOCOUNT ON;

UPDATE table
SET prop1 = @prop1,
prop2 = @prop2
WHERE id = @id;

[/asp]

<strong>ASP Script</strong>
A stored procedure lives in your backend database, not your ASP code. What's probably confusing you in the link you posted is that it contains the client-side code that's used to execute the server-side procedure, which isn't shown.
In your case, the actual stored procedure itself would look something like this:


CREATE PROCEDURE dbo.MyProcedure
(
@prop1 varchar(255), --Fill in appropriate data types if necessary
@prop2 varchar(255),
@id int
)
AS
SET NOCOUNT ON;

UPDATE table
SET prop1 = @prop1,
prop2 = @prop2
WHERE id = @id;
(@sgeddes is right that the procedure should include SET NOCOUNT ON. I've added that to my example.)
Then you would use the following client-side code to call that procedure (I've edited the code from your link to make it easier to follow).
'Create ADO command
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
'Set up DB connection to use, set the type of SQL command
.ActiveConnection = db
.CommandType = adCmdStoredProc
.CommandText = "dbo.MyProcedure" ' Set the name of the Stored Procedure to use

'Add a 255 character varchar parameter
.Parameters.Append .CreateParameter("@prop1",adVarChar, adParamInput, 255)
.Parameters("@prop1") = "Prop 1 Value"

'Add a 255 character varchar parameter
.Parameters.Append .CreateParameter("@prop2",adVarChar, adParamInput, 255)
.Parameters("@prop2") = "Prop 2 Value"

'Add a integer parameter, then pass the value of the variable userID to it
.Parameters.Append .CreateParameter("@id",adInteger, adParamInput)
.Parameters("@id") = userID

'Execute the command
.Execute
End With

'Clean up
set cmd = nothing

That’s obviously a lot more code than what you have right now. So why would you do it? The most important reason is security. By creating a parameterized procedure and passing the values in parameters, you drastically reduce the likelihood of any sort of SQL injection attack.

Leave a Reply

Your email address will not be published. Required fields are marked *