locked
preventing SQL Injection in C# code................... RRS feed

  • Question

  • 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

    Sunday, January 21, 2007 8:12 PM

Answers

  • 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.
    Sunday, January 21, 2007 10:21 PM

All replies

  • 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.
    Sunday, January 21, 2007 10:21 PM
  • 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.
    Monday, January 22, 2007 4:24 AM