It seems sometimes that every time you turn around you see a new article concerning SQL Injection. Recently when I was brushing up on my ASP to create www.Database-Security.Info, I found out why SQL Injection seems to be a reoccurring problem within ASP pages. Most of the basic examples found on the web and in books during my studies would lead any beginning ASP programmer to create pages just ripe for SQL Injection. It was this problem of overly simplified examples that lead me to create this two-part series to give web developers a chance to look at another way of lessening the threat of SQL Injection; parameterized stored procedures. Keep in mind that both of these articles are using ASP with SQL Server 2000 as the back-end, but you should be able to apply the basic concepts to any back-end platform that has the ability to parameterize its stored procedures. Also keep in mind that many of the articles advocate validating any data received from users before utilizing that data in your processes, you should still perform validation on any user input data even if you are utilizing parameterized stored procedures. This series of articles will explain the basic of using parameterized stored procedures to show you that even if you missed something during your input cleanup, you can still protect yourself by using stored procedures.

Being a DBA at heart and training, it did not take much of a leap for me to move from the embedded SQL found in most ASP pages to stored procedures for my own pages. What I found during this transition was one major benefit: Parameterized stored procedures are a great method to lessen chances of SQL Injection. Yes, you should still validate user input and watch how you use parameterized stored procedures, but if you do these two steps you can almost eliminate the threat from SQL Injection.

In this series of articles I would like to show two examples of how parameterized stored procedures should lessen the threat of SQL Injection. The article will cover the ever-present Logon screen and how it is often used to attack a web site with SQL Injection. The second article will cover a more complex method of utilizing stored procedure parameters to create a dynamic stored procedure for a search screen.

Basic Logon Screen

In order to order to see how we can use a parameterized stored procedure during the login process, we need to set up our basic logon page. In this article I will only show the screen shot of the logon page, if you need the code for the page please follow this link to my web site and copy the code from there; Code.

Standard Logon Page

Login Name:

Password:

Most web develops will take the input from the two input boxes and concatenate those two inputs into a SQL string to be utilized by their code.

strSQL = "SELECT strusername, strpassword FROM tUser WHERE strusername = '" + username + "' AND strpassword = '" + password + "'"

set objRS = objDB.execute(strSQL)

if (objRS.EOF) then

'this is where you would do normal processing for invalid users

end if

'this is where you would do normal processing for valid users

If you use this typical method of logging in your users, you have left yourself open to SQL Injection. Yes, you could validate each of the user inputs to scrub the data for SQL

Injection, but validation has its own set of problems and sometimes requires a complex set of logic code to determine if the input is "bad" or "good".

You can greatly reduce the threat of SQL Injection in the case of the logon screen and in the case of missed threats during your input scrubbing by not creating the SQL statement in ASP and passing the user inputs as parameters to a stored procedure.

'Execute stored procedure

Set objConn = Server.CreateObject("ADODB.Command")

objConn.ActiveConnection = cn

objConn.CommandText = "spTestLogon"

objConn.CommandType = 4

'Parameter for stored procedure

SET objParam = objConn.CreateParameter("@strLogin",200, 1, 50)

objConn.Parameters.Append objParam

objConn.Parameters("@strLogin") = username

'Parameter for stored procedure

SET objParam = objConn.CreateParameter("@strPassWord",200, 1, 50)

objConn.Parameters.Append objParam

objConn.Parameters("@strPassWord") = password

Set objrso = objConn.Execute

If NOT objrso.EOF then

Response.Write "Access Granted"

Else

Response.Write "Access Denied"

End If

Stored Procedure Text

SELECT strusername, strpassword FROM tUser WHERE strusername = @strLogin AND strpassword = @strPassWord

The difference in the two methods of obtaining your result set is that the stored procedure will accept the entire user input string as one string. It doesn't matter if the user placed standard SQL Injection code in the string. The stored procedure will look for a name matching 'name'-' and password '1=1' or some similar injection attack. A great benefit is you are having a hard time during your data validation catching all the possible SQL Injection attack methods or determining if that single quote is an attack or just part of someone's name.

Embedded VS. Stored Procedures Test

To prove my point, lets take both forms of the logon screen and try some basic SQL Injection attacks and see what we get back from the database. You can find code for the tuser table, the stored procedure, logon.asp, logonprocess.asp, and logonprocess_sp.asp pages on my web site; Code.

Basic SQL Injection Attack

To demonstrate the advantages of using a stored procedure over embedded SQL, try a basic SQL Injection attack against our logon screen.

user name: foo' OR 1=1--

with no password

This simple SQL Injection attack will give you access to the site, even though we did not supply a valid user name and password.

Now use this small attack against our logon.asp page utilizing the stored procedure to access the database.

You will have to modify the logon.asp page to call processlogon_sp.asp instead of processlogon.asp.

user name: foo' OR 1=1--

with no password

As you can tell from the results return by the logon.asp page, parameterized stored procedures can go a long way in protecting your database applications from SQL Injection. Given no input validation, the parameterized stored procedure still does not allow you to gain access to the site. As stated above, I would not drop all validation from your inputs; just add parameterized stored procedures as a second layer of protection.

Granted the example attack was not very complex but it is enough to show you how dangerous embedded SQL statements can be and how simple it is to create a stored procedure to validate users with your standard logon screen.

In the next article in this series, I will go one step further and show you how to protect those often hard to protect search boxes by using parameterized stored procedures to create a "dynamic" stored procedure.