preventing SQL Injection - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

preventing SQL Injection

Author

Topic

newbie08
Starting Member

2 Posts

Posted - 2007-01-21 : 15:07:16

I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add

db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID);

Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie

My C# update method in the DAL (still working on the code)

private static bool Update(AvatarImageInfo avatarImage)
{
//Invoke a SQL command and return true if the update was successful.
db.ExecuteNonQuery("syl_AvatarImageUpdate",
avatarImage.AvatarImageID,
avatarImage.DateAdded,
avatarImage.ImageName,
avatarImage.ImagePath,
avatarImage.IsApproved);

return true;
}

I am using stored procedures to access the data in the database.

My update stored proc

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate]
@AvatarImageID int,
@DateAdded datetime,
@ImageName nvarchar(64),
@ImagePath nvarchar(64),
@IsApproved bit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
UPDATE [syl_AvatarImages]
SET
[DateAdded] = @DateAdded,
[ImageName] = @ImageName,
[ImagePath] = @ImagePath,
[IsApproved] = @IsApproved
WHERE [AvatarImageID] = @AvatarImageID
RETURN
END TRY
BEGIN CATCH
--Execute LogError SP
EXECUTE [dbo].[syl_LogError];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).
RETURN -1
END CATCH
END

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-01-21 : 17:31:59

The stored procedure, while still good practice, will only get you so far and does not mean you don't have to use other tools. Where ever you find yourself going

SQL=SQL+" from x where blah=" + myID

then you should always use the addInParameter method. Always. No exceptions, and don't be fooled into changing your data using "StripOutQuotes" type of functions.

As an added bonus you often get a performance increase because the underlying SQL does not change so you get more cache hits and fewer parses/query plans.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 00:45:41

Also read this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77814

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-22 : 08:43:03

The question is: what does your ExecuteNonQuery method actually do? Does it assign and set parameters, or does it concatenate everything together and just execute it?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources