Click here to Skip to main content
11,641,211 members (65,576 online)
Click here to Skip to main content

Preventing SQL Injection Attacks

, 14 Jul 2008 CDDL 13.6K 19
Rate this:
Please Sign up or sign in to vote.
Security is a very important topic and a very complicated one in ASP.NET.

Introduction

In this article, I will show how to create protection against injection attacks.

Background

An injection attack is one in which the user enters some cleverly written text in an otherwise innocuous input field. This user-entered text can sometimes trick your application into doing unexpected things and could potentially wreak havoc on your system. In this section, we will take a look at a few examples and how to prevent them.

The most common type of injection attack is a SQL injection. Imagine that you've created a login form for use with Forms Authentication. Your code compares a user entered username and password to values in a database, and returns true or false via the following VB.NET code:

dim strSQL as string = "SELECT 1 FROM Users WHERE UserName = '" & _
              txtUserName.Text & "' AND Password = '" & txtPassword.Text & "'"

This statement by itself seems harmless. Now imagine that the user enters the following text into the txtUsername textbox:

'; DELETE FROM Users --

Your original code would then construct the following SQL statements:

SELECT 1 FROM Users WHERE UserName = ''; DELETE FROM Users --' AND Password = ''

MS SQL Server interprets this as two separate statements, and suddenly all of your user data is deleted.

There are a few ways around this issue. First, if you can, always used parameterized Stored Procedures instead of dynamically constructed SQL statements. For example:

dim cmdAuthentificate as new SqlCommand(queryUserAuthentificate)
cmdAuthentificate.CommandType = CommandType.StoredProcedure
cmdAuthentificate.Parameters(new SqlParameter("@Username",txtUsername.Text)
cmdAuthentificate.Parameters(new SqlParameter("@Password",txtPassword.Text)
cmdAuthentificate.Parameters(new SqlParameter("@Result",DBNull.Value)
cmdAuthentificate.Parameters("Result").Direction = ParameterDirection.Output
cmdAuthentificate.ExecuteNonQuery()

In this case, no matter what the user enters in the text boxes, the data is interpreted as literal content and no SQL statement can be injected.

If you can't use a Stored Procedure, you can still use a parameterized dynamic SQL statement:

dim strSQL as string = "SELECT  @Result = 1 FROM Users WHERE UserName = @UserName " &_
           "AND Password = @Password"
dim cmdAuthentificate as new SqlCommand(strSQL)
cmdAuthentificate.Parameters.Add(new SqlParameter("@Username", txtUsername.text))
........
cmdAuthentificate.ExecuteNonQuery()

A third method is to parse out any potentially damaging user-entered data. This can be rather daunting, but often a simple check of apostrophes does the trick:

dim strSQL as string = "SELECT 1 FROM Users WHERE UserName = '" & _
    txtUsername.Text.Replace("'", "''") & "' AND Password = '" & _
    txtPassword.Text.Replace("'", "''") & "'"

License

This article, along with any associated source code and files, is licensed under The Common Development and Distribution License (CDDL)

Share

About the Author

Marius Mihailescu
Instructor / Trainer ,,Titu Maiorescu'' University
Romania Romania
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralA fuller featured article... Pin
Pete O'Hanlon14-Jul-08 3:34
mvpPete O'Hanlon14-Jul-08 3:34 
GeneralRe: A fuller featured article... Pin
thund3rstruck14-Jul-08 5:13
memberthund3rstruck14-Jul-08 5:13 
GeneralRe: A fuller featured article... Pin
kamii4715-Jul-08 3:28
memberkamii4715-Jul-08 3:28 
GeneralMissing one Pin
Chris Maunder14-Jul-08 3:32
adminChris Maunder14-Jul-08 3:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150731.1 | Last Updated 14 Jul 2008
Article Copyright 2008 by Marius Mihailescu
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid