In a previous article we provided some examples of how intruders will try to
attack your site using Cross-site Scripting (XSS) attacks. In an XSS attack, the
attacker tries to use client-side methods of injecting client-side script and
then high-jacking a user's session. Now, we're going to provide some examples of
a server-side attack where an intruder will try to obtain information from
within your database. After the examples, we will go through methods of securing
your code against these types of attacks.
SQL injection attacks take advantage of code that does not filter input that
is being entered directly into a form. Susceptible applications are applications
that take direct user input and then generate dynamic SQL that is executed via
back-end code. For example say you have a logon form that accepts a user name
and password. Once authenticated against the database, the application then sets
a session value, or some other token for allowing the user to access the
protected data.
Take a logon form for example, here you have two basic form elements, a
textbox for accepting a user name, and a password box for the password.
<form action="myscript.aspx">
<input type="textbox" name="username">
<input type="password" name="password"><br/>
<input type="submit">
</form>
Then in the code behind:
Dim SQL As String = "SELECT Count(*) FROM Users WHERE UserName = '" & _
username.text & "' AND Password = '" & password.text & "'"
Dim thisCommand As SQLCommand = New SQLCommand(SQL, Connection)
Dim thisCount As Integer = thisCommand.ExecuteScalar()
In the previous code block it executes the built SQL script directly, if count is greater than one,
then you know the values entered in for the user name and password were the ones
matching the database.
Now with that code in the previous example, suppose someone entered the
following string into your username text box:
' or 0=0 --
The apostrophe will close the username value being sent to the SQL query, then pass another argument to the SQL
query, after the last argument it then comments out the rest of the query using the
"--". Since the second argument they entered into your texbox is an "or"
statement, the first check on the user name doesn't matter, and since 0 is
always going to equal 0 the script will execute successfully and return a
positive logon. Guess what? Your intruder now has access to your application.
Ok so maybe they can logon into your application, but what else can they do?
Let's take another example of SQL injection, as in the previous example of using
the apostrophe to terminate the value, and proceed on to another argument, lets
do this, but using something that can really ruin your application's data and
day:
'; drop table users --
Definitely something that can ruin your day. Of course this type of an
attack you'll probably notice pretty quick. Other SQL commands can then be entered
to determine your database's structure, and return all user names and passwords
from the database. You make it even easier for the attacker if you do not
provide some ambiguous error message and provide the error message returned from
.NET. This error message can provide critical information they need to determine
what to enter in your form in order to obtain information.
SQL Injection Prevention
One method of preventing SQL injection is to avoid the use of dynamically
generated SQL in your code. By using parameterized queries and stored
procedures, you then make it impossible for SQL injection to occur against your
application. For example, the previous SQL query could have been done in the
following way in order to avoid the attack demonstrated in the example:
Dim thisCommand As SQLCommand = New SQLCommand("SELECT Count(*) " & _
"FROM Users WHERE UserName = @username AND Password = @password", Connection)
thisCommand.Parameters.Add ("@username", SqlDbType.VarChar).Value = username
thisCommand.Parameters.Add ("@password", SqlDbType.VarChar).Value = password
Dim thisCount As Integer = thisCommand.ExecuteScalar()
By passing parameters you avoid many types of SQL injection attacks, and even
better method of securing your database access is to use stored procedures.
Stored procedures can secure your database by restricting objects within the
database to specific accounts, and permitting the accounts to just execute
stored procedures. Your code then does all database access using this one
account that only has access to execute stored procedures. You do not provide
this account any other permissions, such as write, which would allow an attacker
to enter in SQL statement to executed against your database. Any interaction to
your database would have to be done using a stored procedure which you wrote and
is in the database itself, which is usually inaccessible to a perimeter network
or DMZ.
So if you wanted to do the authentication via a stored procedure, it may look
like the following:
Dim thisCommand As SQLCommand = New SqlCommand ("proc_CheckLogon", Connection)
thisCommand.CommandType = CommandType.StoredProcedure
thisCommand.Parameters.Add ("@username", SqlDbType.VarChar).Value = username
thisCommand.Parameters.Add ("@password", SqlDbType.VarChar).Value = password
thisCommand.Parameters.Add ("@return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
Dim thisCount As Integer = thisCommand.ExecuteScalar()
Finally, ensure you provide very little information to the user when an error
does occur. If there is database access failure, make sure you don't dump out
the entire error message. Always try to provide the least amount of information
possible to the users. Besides, do you want them to start helping you to debug
your code? If not, why provide them with debugging information?
By following these tips for your database access you're on your way to
preventing unwanted eyes from viewing your data.
By: Patrick Santry, Microsoft MVP (ASP/ASP.NET), developer of this site, author of books on Web technologies, and member of the DotNetNuke core development team. If you're interested in the services provided by Patrick, visit his company Website at Santry.com.