DevNote
Guard Against SQL Injection Attacks
By Paul Litwin
In her excellent 9 Steps to Secure Forms Authentication article, Beth Breidenbach mentions SQL injection attacks and how important it is to validate user entries to guard against such attacks. Because of space considerations, Beth didn't go into detail about this class of attacks, so I thought I'd share with you how they work - and why you need to protect against them.
The basic idea with a SQL injection attack is that a user enters malformed SQL into textbox controls to assist in hacking into your system. At first, it might sound preposterous that a user could gain entry access to your system by making some entries into a textbox, so let me illustrate with an example. Let's say you're using forms authentication for an ASP.NET site. Your form would probably authenticate users with code that looks similar to that shown in FIGURE 1.
Sub Submit_Click(src as object, e as EventArgs)
If ValidateUser(txtUserName.Text, txtPassword.Text) Then
FormsAuthentication.RedirectFromLoginPage( _
txtUserName.Text)
Else
lblStatus.Text="Invalid Login!"
End If
End Sub
Function ValidateUser(ByVal strUserName As String, _
ByVal strPassword As String) As Boolean
Dim cnx As SqlConnection = New SqlConnection( " & _
"server=localhost;uid=sa;pwd=;database=northwind;")
cnx.Open()
Dim strSQL As String = _
"SELECT Count(*) FROM Users " & _
"WHERE UserName='" & strUserName & "'" & _
"AND Password='" & strPassword & "'"
Dim scdSecurity As New SqlCommand(strSQL, cnx)
Dim lngCount As Integer = scdSecurity.ExecuteScalar()
If lngCount>0 Then
Return True
Else
Return False
End If
End Function
FIGURE 1: Typical code used on an ASP.NET site to authenticate users using forms authentication. Note that the SQL string is built dynamically from unchecked user entries.
Now a hacker need only enter the following cryptic - but effective - string into the txtUserName textbox to gain entry to your system (assuming your database is stored in SQL Server) without having access to a single valid username or password:
' OR 1=1 -
How does this work? Take a look at how this seemingly innocuous entry can get a hacker logged into your system by examining the SQL string it generates:
SELECT Count(*) FROM Users WHERE UserName='' OR 1=1 --'AND Password=''
The apostrophe serves to close the UserName value and "or" it with a value that is always true (1=1). The rest of the statement is then rendered useless by the two hyphens, which in SQL Server signify the beginning of a comment. The WHERE clause effectively becomes this:
WHERE UserName='' Or 1=1
This statement is always going to be true! Thus, the hacker can use the previous code to log onto your system without knowing any username or password. Wow! (If you're saying to yourself that this is not a problem because you are using Oracle or some other database, think again. Hackers can craft similar strings that will break into Oracle and most other databases that pass dynamically built SQL.)
So what can you do to guard against a SQL injection attack like this? One easy way to fix this hole is to use a stored procedure rather than dynamically built SQL. The way parameters are passed to SQL Server stored procedures (and perhaps other databases that support stored procedures) prevents the use of apostrophes and hyphens in such a manner. A second defense is to validate all user entries that will be used to generate dynamically built queries. The RegularExpressionValidator control can be especially effective in preventing the use of apostrophes, spaces, equal signs, and hyphens in username and password textboxes.
The moral of the story - as Beth Breidenbach states rather emphatically in her article - is to never trust user input. Always assume the worst of your users! If your site or a site you work with uses forms authentication, I urge you to check it right now to see if you can hack in using a SQL string like the one I showed you. And keep your guard up at all times. A little healthy paranoia can go a long way toward creating secure Web sites that can resist attacks from nefarious hackers who seem to have nothing better to do than try to make our lives miserable.
Paul Litwin is editor and technical director of asp.netPRO magazine. Contact him at [email protected].
