Guard Against SQL Injection Attacks

DevNote

Guard Against SQL Injection Attacks

By Paul Litwin

In her excellent 9Steps to Secure Forms Authentication article, Beth Breidenbach mentions SQLinjection attacks and how important it is to validate user entries to guardagainst such attacks. Because of space considerations, Beth didn't go intodetail about this class of attacks, so I thought I'd share with you how theywork - and why you need to protect against them.

The basic idea with a SQL injection attack is that a userenters malformed SQL into textbox controls to assist in hacking into yoursystem. At first, it might sound preposterous that a user could gain entryaccess to your system by making some entries into a textbox, so let meillustrate with an example. Let's say you're using forms authentication for anASP.NET site. Your form would probably authenticate users with code that lookssimilar to that shown in FIGURE 1.

Sub Submit_Click(src as object, e as EventArgs)

   IfValidateUser(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 AsString) As Boolean

   Dim cnx AsSqlConnection = New SqlConnection( " & _

    "server=localhost;uid=sa;pwd=;database=northwind;")

   cnx.Open()

   Dim strSQL As String =_

    "SELECT Count(*)FROM Users " & _

     "WHERE UserName='" &strUserName & "'" & _

    "ANDPassword='" & strPassword & "'"

   Dim scdSecurity As NewSqlCommand(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 toauthenticate users using forms authentication. Note that the SQL string isbuilt 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 asingle valid username or password:

' OR 1=1 -

How does this work? Take a look at how this seemingly innocuousentry can get a hacker logged into your system by examining the SQL string itgenerates:

SELECT Count(*) FROM Users WHERE UserName='' OR 1=1 --'ANDPassword=''

The apostrophe serves to close the UserName valueand "or" it with a value that is always true (1=1). The rest of the statementis then rendered useless by the two hyphens, which in SQL Server signify thebeginning of a comment. The WHERE clause effectively becomes this:

WHERE UserName='' Or 1=1

This statement is always going to be true! Thus, thehacker can use the previous code to log onto your system without knowing anyusername or password. Wow! (If you're saying to yourself that this is not aproblem because you are using Oracle or some other database, think again.Hackers can craft similar strings that will break into Oracle and most otherdatabases that pass dynamically built SQL.)

So what can you do to guard against a SQL injection attacklike this? One easy way to fix this hole is to use a stored procedure ratherthan dynamically built SQL. The way parameters are passed to SQL Server storedprocedures (and perhaps other databases that support stored procedures)prevents the use of apostrophes and hyphens in such a manner. A second defenseis to validate all user entries that will be used to generate dynamically builtqueries. The RegularExpressionValidator control can be especiallyeffective in preventing the use of apostrophes, spaces, equal signs, andhyphens in username and password textboxes.

The moral of the story - as Beth Breidenbach states ratheremphatically in her article - is to never trust user input. Always assume theworst of your users! If your site or a site you work with uses formsauthentication, I urge you to check it right now to see if you can hack inusing a SQL string like the one I showed you. And keep your guard up at alltimes. A little healthy paranoia can go a long way toward creating secure Websites that can resist attacks from nefarious hackers who seem to have nothingbetter to do than try to make our lives miserable.

Paul Litwin is editor and technical director of asp.netPROmagazine. Contact him at [email protected].