Creating the Stored Procedures

For security reasons, it is always best to use stored procedures to retrieve, save, and delete information in the database. The reasons why can fill a book on security all by themselves, so I only mention it briefly here. The type of attack this protects against is called a SQL injection attack.

Note

Until I started taking courses on security, I was unaware of the power of this attack and even that a database could be attacked this way. Although this book is not a security manual, it will point you in the right direction so you can get additional information.

SQL Injection Attacks

A SQL injection attack is an attack in which a malformed entry in a text field is turned into executable SQL code. Take the following example: You have a form with a textbox that searches for books in the database; users enter the title they want to find. The resulting SQL statement might look something like the following:

 Select * From Books Where book_title = '" & txtTitle & '"" 

This simple SQL statement retrieves all of the rows from the books table where the title matches what the user entered. (This assumes the user entered a real book title.) Now, the user could enter the following text in the txtTitle field:

 ' Drop Table Books -- 

Your SQL statement will end up looking like the following:

 Select * From Books Where book_title = '' Drop Table Books -- 

This drops the books table, which is not something you want to happen! Using stored procedures, parameters are always treated as strings and are never executed (unless you want them to be). This blocks a user from performing this type of attack.

If you are interested in protecting against this type of attack and other attacks, you should refer to the book Writing Secure Code by Michael Howard and David Leblanc (Microsoft Press, 2001).

For each object that you create, there should be, in general, four stored procedures. These stored procedures should perform the following operations: getting all records, getting one record, deleting a record, and saving a record. You can break the save stored procedure into an insert and update stored procedure, but it is really up to you. Listing 3-4 shows all of the stored procedures that you should execute against the Northwind database in SQL Server.

Listing 3-4: The Region Stored Procedures

 --Retrieve all of the records from the Region table CREATE PROCEDURE usp_region_getall AS SELECT  * FROM    Region go --Retrieve a single record from the Region table CREATE PROCEDURE usp_region_getone @id int AS SELECT  * FROM    Region WHERE   RegionID = @id go --Delete a single record from the Region table CREATE PROCEDURE usp_region_delete @id int AS DELETE FROM    Region WHERE   RegionID = @id go --Save a record to the Region table (this includes both inserts and updates) CREATE PROCEDURE usp_region_save @id int, @region varchar(50), @new_id int OUTPUT AS IF @id = 0   BEGIN     SET @id = (SELECT MAX(RegionID)     FROM Region) + 1     INSERT INTO Region (RegionID, RegionDescription)       VALUES (@id, @region)   END ELSE   UPDATE Region   SET     RegionDescription = @region   WHERE     RegionID = @id SET @new_id = @id 

I like to name my stored procedures in the following format:

 "usp_" + table name + "_" + operation 

This tells you what the stored procedure is for and separates the stored procedures out from SQL Server's stored procedures (which all begin with sp_).

Some people may protest against the inclusion of both the insert and update statements in the single Save stored procedure. However, when creating a large application, it is easier for the database developer to write this than to have the developer code multiple parameters to multiple stored procedures. It also abstracts the save process from the objects. All the object has to do is pass the parameters and let the stored procedure figure out what to do with it. This does place a little bit of business logic into the stored procedure, but it sometimes may be worth it.