CREATE PROCEDURE usp_ProcedureName
    (
    @Variable1 int,
         --First variable

    @bVictory bit OUTPUT, 
         -- Returns 1 if we get to the end of the procedure with 
         -- no errors. Returns 0 if there was an error. This allows
         -- any code that calls this stored procedure to know whether
         -- it worked or not, to make it easier to handle errors up
         -- the chain.

    @ErrorMessage varchar(100) OUTPUT
         -- If there is an error, this variable captures the
         -- description.

    )
AS

--The whole piece of code should be in a Try/Catch block for proper 
-- error handling
BEGIN TRY
   BEGIN TRANSACTION

    --1) VALIDATE INPUTS VIA A CALLED STORED PROCEDURE
       -- Check that the variables are actually valid, to prevent
       -- Little Bobby Tables errors. (See xkcd.com/327)
 
       --a) Execute the procedure that validates our inputs
           EXEC usp_ValidateVariable1 
                     @Variable1, 
                     @bVictory OUTPUT, 
                     @ErrorMessage OUTPUT

       --b) See if the validation checked out. (If it did, bVictory
       --    will equal 1. If it didn't, it will equal zero, and
       --    the error message will be in @ErrorMessage)

           IF @bVictory <> 1
             BEGIN
                 RAISERROR(@ErrorMessage, 11, 1);
             END

    --2) ACTUALLY RUN THE CODE YOU WANT TO RUN
         <Code goes here>

    --3) DECLARE VICTORY
         -- If we get this far in the code without errors, then let's
         -- set our error-handling variables to show the code that
         -- called it that we made it.
         SET @bVictory = 1;
         SET @ErrorMessage = 'Success';

   COMMIT TRANSACTION

END TRY
BEGIN CATCH
   --1) Set our variables @bVictory and @ErrorMessage to show the
   --   calling code that we didn't make it after all

         SET @bVictory = 0
         IF @ErrorMessage IS NULL
            BEGIN
               SET @ErrorMessage = ERROR_MESSAGE();
            END

   --2) Log the error
         EXEC usp_LogError @ErrorMessage
   --3) Roll back the transaction
         ROLLBACK TRANSACTION

END CATCH

Fred'); DROP TABLE Customers;--

quote:

---

Originally posted by durand26


? If not, is there another way we design our sprocs to prevent SQL injections?

---

quote:

---

Originally posted by durand26

Thanks for your valuable feedback guys!

Visakh: It was mostly to stop SQL injections. Eg, to stop someone filling out the name section of a web form with

Fred'); DROP TABLE Customers;--

which could cause our customer table to be dropped.

It would be nice if the client side application developer puts enough data validation to stop this sort of input, but surely we as database developers should sort it out too, right? If not, is there another way we design our sprocs to prevent SQL injections?

Bustaz Kool: Thanks! I'll add that

tkizer: Thank you! I'll shorten my transactions. :)

---