SQL includes a simple stored procedure, sp_who that allows for fast monitoring of SQL server resources by examining active and inactive SQL processes. Even with an efficient facade and coding style, sp_who can be used to assess performance in several different ways:

1. Detecting connection leaks - Even a well-developed .NET application can be brought to its knees with a single connection leak, causing the application’s connection pool to fill and prevent further SQL queries.
2. Assessing application load - How much of a resource hog is your application in a live environment? If you can identify SQL-based bottlenecks in your application, you’ll be able to optimize them.

Read the rest of this entry »

1. Protection against injection attacks All data that is passed to a stored procedure is passed as a parameter with a specific data type. Assuming you don’t dynamically build SQL queries within a stored permission, the likelihood of an injection attack on a stored procedure is slim to none. In all fairness, dynamic SQL queries written within ASP.NET can be executed using parameters, but parameters aren’t required. Making stored procedures a requirement ensures that everyone on your team is protecting against injection attacks.

   Consider the following two examples:

   
   Dim UserInput As String = "1'); DELETE FROM tblInjectionAttack;"
   Dim SQL As String
   SQL = "INSERT INTO tblInjectionAttack (Column1) VALUES ('" & UserInput & "')"
   

   
   CREATE PROCEUDRE InsertInjection
   	@UserInput NVARCHAR(500)
   AS
   	INSERT INTO tblInjectionAttack (Column1) VALUES (@UserInput)
   	--EXEC InsertInjection '1''); DELETE FROM tblInjectionAttack;'
   

   In the first example, the SQL query will be injected and the attacker will be able to execute any command that they have access to. In the second example, the attacker’s SQL injection will be saved in Column1 and won’t be executed at all.

2. Better database security Stored procedures allow for a reduction of privileges for the database account that ASP.NET uses. You can restrict your database account to only have permissions to execute stored procedures, and restrict its access to everything else. If the database account becomes compromised, the attacker will have much less power to do damage — he won’t be able to see or modify any tables, and won’t even be able to get a list of stored procedures. Generally speaking, the fewer users with elevated privileges the better.

   If you create a new user in a database, by default, that user has no privileges; however, if you need to remove existing privileges from an account, you can do so by executing the following query:
   [sourcecode language="sql"]
   –To deny access on one single table:
   DENY SELECT,INSERT,UPDATE,DELETE ON [TableName] TO [Username]

   Read the rest of this entry »