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 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
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
'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("@strLogin") = username
'Parameter for stored procedure
SET objParam = objConn.CreateParameter("@strPassWord",200, 1, 50)
objConn.Parameters("@strPassWord") = password
Set objrso = objConn.Execute
If NOT objrso.EOF then
Response.Write "Access Granted"
Response.Write "Access Denied"
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.
For many years now, SQL Injection attacks on large corporate websites have been highly publicized. Several articles around the Web have described what an injection attack is, how it works, and the basics of how to defend against it. A couple of very good articles here at SQL Server Central also delve into this topic (SQL Injection by Christoffer Hedgate and SQL Injection - Part 1 by Randy Dyess).
So why did I feel the need to write another article on SQL Injection? For three reasons:
1. The good work by Mr. Dyess and Mr. Hedgate offer code samples and examples for ASP. I felt that a sample pertaining to ASP.NET, for those without the ASP background, was in order.
2. These two authors focus on using parameterized queries; and in the case of Mr. Hedgate, validating user input. Excellent advice all around, but I feel there are other lines of defense which should be addressed as well.
3. Finally, no matter how many SQL Injection articles are posted around the Web, DBA's and developers continue to post highly exploitable code samples to newsgroups and discussion boards.
In this article, I hope to build upon the good work of Mr. Hedgate and Mr. Dyess, and provide updated samples as well as a more complete defensive strategy for dealing with SQL Injection.
What Is SQL Injection
SQL Injection is a method of exploiting databases via the user interface. The method takes advantage of the fact that SQL allows multiple commands on a single line, separated by semicolons. SQL Injection also takes advantage of the way SQL handles single quotes.
We'll create a sample using a fictitious Database Admin. We'll call him Ima D. Ba. Ima has to create a login table for Northwind that contains the usernames and passwords of all employees. He creates the following table, called Test_Logins, in the Northwind Database:
Ima then made a SQL Login for the local ASPNET user. He made ASPNET the database owner (dbo) for the Northwind Database and also made ASPNET a SQL system administrator.
One of the company developers then wrote a simple ASP.NET login webform that asks for username and password, which it then validates against Ima's Test_Logins table.
Private Sub LoginButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoginButton.Click
Dim success As Boolean = False
Dim sqlcon As New System.Data.SqlClient.SqlConnection("SERVER=(local);" & _
"INITIAL CATALOG=Northwind;" & _
Dim sqlstr As String = "SELECT * " & _
"FROM Test_Logins " & _
"WHERE UserName = '" & Username.Text & "' " & _
"AND Password = '" & Password.Text & "'"
Dim sqlcmd As New System.Data.SqlClient.SqlCommand(sqlstr, sqlcon)
Dim sqldr As System.Data.SqlClient.SqlDataReader = sqlcmd.ExecuteReader()
If sqldr.HasRows Then
success = True
If success Then
The login code basically performs five steps:
1. User enters username and password
2. Web form opens a connection to the Northwind Database
3. Web form dynamically builds a SQL query: SELECT * FROM Test_Logins WHERE UserName = 'username' AND Password = 'password'
4. Web form performs the SQL query over the open connection
5. If there are any rows in our resultset, we know that the user has entered a good username and password.
Enter The Hacker
Our hacker Yuri picks up one of Andrew Fuller's business cards at a tech convention. The business card has Andrew's title ("Vice President of Sales") in big 12 point type. So Yuri decides that Andrew probably has access to a lot of confidential company information. Andrew is now targeted.
This is a very simplified example, but it demonstrates several avenues of attack. The first is to trick the server into believing we are actually logging in as someone else. We can enter the following as a username:
The resulting SQL query is:
SELECT * FROM Test_Logins WHERE UserName = 'AndrewF'; -- AND Password = ''
Everything after the -- is a comment, so the password check is completely bypassed, allowing us to successfully log in as AndrewF without a password!
Alternatively Yuri could append DROP statements, DELETE statements or any other valid T-SQL commands to our username to produce intrusive or destructive results, as in these examples. We could even potentially access stored procedures and extended stored procedures (like xp_cmdshell) to wreak havoc all over the corporate network!
How To Avoid SQL Injection
So now that we know what SQL Injection is, how can we avoid it? Let's look at the mistakes Ima and his developer made:
The Developer Did Not Validate User Input
Your first line of defense against SQL Injection is always input validation. Any time an application accepts input from a user and uses it in a query against a database, it absolutely has to be validated. This validation can be done server-side, client-side, or even both. In our example, we know that certain special characters in input strings can open us up to SQL injection: double-dash (--), single-quote(') and semi-colon (;) are the primary trouble-makers. We can check for these and other special characters on the client or server side and eliminate them before we dynamically build queries based on user input.
A good rule of thumb is if you don't allow certain characters in the input string, eliminate them completely (or throw an exception) during the validation phase. For instance, all our usernames consist of only alphabetic characters, so we could limit input in that form field to letters 'A' - 'Z' and 'a' - 'z'. Anything the user inputs, including drop-down box form inputs, should be validated before you try to query with it.
The Developer Built A Query Dynamically
Our next line of defense is to avoid building queries dynamically when possible. Always look to parameterized queries (sp_executesql in T-SQL) first.
But if you do need to build a query dynamically, validate your input well and be certain to replace single quotes (') with escaped single quotes ('') in string constants. This can be done with the T-SQL Replace() function, or the .NET String.Replace() method can be used for this purpose.
For this particular application, however, it makes much more sense for Ima and the Developer to use a Parameterized Query to validate our user. Parameterized queries pass the input values to the server separately from the actual SQL Query. The server then provides a level of safety by using sp_executesql with parameters instead of dynamically building the query string.
Build queries dynamically only when you have to. And when you do have to, be sure to validate all input to the generated query string before running it against the server. Also when building dynamic SQL Queries, you generally should not need to dynamically insert table names, column names, etc. (unless you're building some sort of SQL DDL/Admin tools). If you find that you are generating a lot of dynamic queries with dynamic table and column names, it might be a good idea to take a closer look at your database implementation.
Ideally, Ima will build a stored procedure that accepts the username and password as parameters. This procedure will validate the user and return a success or failure code and any other pertinent information. The stored procedure model will add a layer of abstraction that will keep the hacker a little further away from our base tables.
The DBA Gave Away The Farm
You might notice in the example attacks that the hacker was able to run such commands as DELETE FROM Test_Logins and DROP TABLE Test_Logins. This is because Ima made the ASPNET user the Northwind dbo. Also, Ima gave the ASPNET user SysAdmin rights. Our DBA needs to scale back the rights for the ASPNET user to something more reasonable. Most likely this user will have read-only rights to most information; and Ima's ASPNET user should never, ever have rights to run DDL commands such as CREATE TABLE, DROP TABLE, etc. Ima also needs to lock down the master database to ensure the ASPNET user doesn't grab all available information about the server configuration.
The Developer Is Sending Too Much Information Across The Wire
In our sample, our Developer is sending much more information across the wire (between the IIS and SQL Server) than is necessary. All we need to know is if our username and password matched. So all we really need to return from SQL Server is a single number, not a complete table row! We could change the SELECT statement to return the EmployeeID of the logged in user with SELECT EmployeeID FROM .... The less information we have to pass along, the better our security.
The DBA Is Storing Plain Text In Critical Columns
Closely related to the previous problem, Ima is storing passwords in plain text format in the Test_Logins table. This is a big no-no. Sensitive information such as passwords should be stored in encrypted or hashed format. That way, even if our hacker happens to get past all the other lines of defense and is able to view the data in our tables, it will be unusable to him. One method of protecting passwords is to hash the passwords using a hash algorithm (like MD5) before storing them in your table. Then when you go to compare passwords at login time, hash the password in your ASP.NET code or your Windows Forms code before sending it to the SQL Server. Any time we can prevent sending sensitive information across the wire in plain text format, we should take advantage of it.
SQL Injection is a tactic used by hackers to gain unauthorized access to confidential data stored in SQL Server. Proper planning and development practices can help secure your system against SQL Injection and other types of attacks.