Hoodwinked

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



Re: Visual C# General preventing SQL Injection in C# code...................

boban.s

Yes even when usign stored procedures, parameters should be add, and not creating query string. The main reason is sql injection, but not just that. Also it is faster when executing parametrized command over query string.




Re: Visual C# General preventing SQL Injection in C# code...................

Mark Betz

SQL injection can only occur when invalid user input makes its way into a database command. In situations where you must pass user input to the database, as in a user-supplied search term, then you have to treat the input as strongly-typed and validate it against the strictest possible set of rules. I think the stored proc does help you a bit, as it creates a context in which the user-supplied text is treated in a more restricted way (i.e. not part of an arbitrary command string), but you still don't want just any user-supplied gunk showing up in a column, so you need to validate it anyway.