Click here to Skip to main content
11,706,801 members (45,830 online)
Click here to Skip to main content

Hack Proof Your ASP.NET Applications From SQL Injection

, 7 Jun 2013 CPOL 52K 1.7K 76
Rate this:
Please Sign up or sign in to vote.
This article descirbes what SQL injection is and how to prevent from SQL injection.

Introduction

A developer never wants to get hacked his own web application .But intruder , malicious persons are more than developers, and I used to be one of them and then turned into a developer .As i have walk in both the shoes , so i have decided to write a series of articles which will definitely help to hack proofing a web application .

A developer should always concerned about hack attempts in their applications ,and its a developer duty as well. Lots of online tools, spoofing tools, sniffers tool, etc., are available on the internet .so even a normal internet user can turned into a hacker . i hope everybody knows the consequences of being hacked , so by not describing them , i better do write my article.

lets gets start understanding of some hacks and how a developer can prevent them .in this first article i will start by sql injections.

SQL Injection

SQL injection is an attack in which one or more commands are inserted into a query to form a danger query which may retrieve , damage , manipulate your existed data channel. This almost always occurs when dynamic SQL is being used and when you’re concatenating strings in your code (C#,VB,J#,F#) to form SQL statements. SQL injection can occur in your Microsoft .NET Framework code if you’re forming a query or procedure call, and it can occur in your server-side T-SQL code as well, such as in the case of dynamic SQL in stored procedures.

SQL injection was number one attack in 2010 .And legacy coded applications are still vulnerable to sql injections. let me describe it more clearly or we can say in simple language , it happens when commands(or other sql queries) are inserted where we were supposed to send the DATA into sql .We can divide a whole query into two channel control channel (query) and data channel (user inputs). A attacker usually do not care about your control channel (query) , he just do care ,how he can insert malicious query in your data channel.

There are other ways too to inject sql injection : -

  • String truncations from SQL functions
  • Automated tools

Except being hijacked of any individual or group accounts , sql injection can help to do virtually anything on the system that permissions allow : -

  • Install backdoors
  • Can copy database over port 80
  • Port scan (can scan your whole network)
  • Many more !!

How is it Exploited -

Generally attacker inject the sql Injection directly from the web page or by manipulating sql statements .

In the above code we are concatenating the string with user input data and forming a sql statement .and we supposed to work our query like in the following image -

But a hacker thinks differently , he will manipulate the query as in the following image -

SQL Injection Used in the above example -

' union select username , password ,'1' from [User] --' 

Above is just a example, there are many sql injections which a hacker can try on a application.Hacker can get the passwords , install the backdoor . hacker can manipulate the query to get the data from sysobject for all the database detail .

How to prevent SQL Injections -

  1. Validate user input (Using Regex or anything else)
  2. Using Parameterized query
  3. Use Stored procedure
  4. Use an ORM

1.Validate user input

The simplest way is that we can validate the user input using Regular Expression and replace the danger character by blank

//Validate the user input
string userinput = TextBox1.Text;
 
//only accept the alphabets and numbers , rest will be replaced by blank    
userinput = Regex.Replace(userinput, "[^A-Za-z0-9$]", ""); 

But but this is not the best way to prevent application from sql injection .Because SQL Injection does not only contains "-" or " ' " . There are many sql injections which consists legal code too.

2.Using Parameterized Query -

if we want to use inline SQL ,then to stop SQL injection we can pass the parameter in sql query and can add SQL Parameter in the query .this way we can prevent sql injection .there is nothing wrong in using sql statement inline.

string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
//add the parameter in query
string query = string.Format(@"select Username ,Age,Department from " + 
       @"[User] where Username like '%+@Username+%'", TextBox1.Text);

using (SqlConnection con = new SqlConnection(connectionString))
{
    //
    // Open the SqlConnection.
    //
    con.Open();
    //
    // The following code uses an SqlCommand based on the SqlConnection.
    //
    using (SqlDataAdapter da = new SqlDataAdapter())
    {
        using (SqlCommand command = new SqlCommand(query, con))
        {
            //pass the parameter
            command.Parameters.Add(new SqlParameter("@Username",TextBox1.Text)) ;
            DataSet ds = new DataSet();
            da.SelectCommand = command;
            da.Fill(ds, "test");
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
    }
}

parameterized query tell to the SQL server that data passed into any parameter will remain in the data channel thats how sql server prevent the SQL injection. But still its not the best way as we are writing business logic inline .every time i have to write again this query .

3.Using SQL Procedure -

The best way to prevent from SQL injection is use stored procedure . As business logic are hidden , Its provide better performance , reusability . Now you will have to just protect the table and stored procedure by using permissions .

string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
//add the stored procedure name 
string query = "dbo.GetUsername";

using (SqlConnection con = new SqlConnection(connectionString))
{
    //
    // Open the SqlConnection.
    //
    con.Open();
    //
    // The following code uses an SqlCommand based on the SqlConnection.
    //
    using (SqlDataAdapter da = new SqlDataAdapter())
    {
        using (SqlCommand command = new SqlCommand(query, con))
        {
            //pass the parameter
            command.Parameters.Add(new SqlParameter("@param1", TextBox1.Text));
            command.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            da.SelectCommand = command;
            da.Fill(ds, "test");
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
    }
} 

4.Use an ORM -

We can use any ORM (Entity framework , Nhibernate ,etc ) .ORM makes query parameterized behind the scene so using ORM we can also prevent SQL Injection.

References

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sarvesh Kushwaha
Software Developer
India India
I do believe life is to help others ... So here i am .. in my spare time i learn new things of programming and try to help people with my knowledge .
I'm an energetic, self-motivated and hard-working Developer and Information Technology Professional with experience in projects, website design and development.

Visit My Technical Blog

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
Afzaal Ahmad Zeeshan13-Dec-14 4:39
professionalAfzaal Ahmad Zeeshan13-Dec-14 4:39 
GeneralRe: My vote of 5 Pin
Sarvesh Kushwaha29-Dec-14 23:27
memberSarvesh Kushwaha29-Dec-14 23:27 
QuestionPrevent SQL injection Pin
Member 1106722514-Oct-14 17:14
memberMember 1106722514-Oct-14 17:14 
QuestionI need help!!! Pin
Member 1114811512-Oct-14 16:47
memberMember 1114811512-Oct-14 16:47 
AnswerRe: I need help!!! Pin
Sarvesh Kushwaha13-Oct-14 6:41
memberSarvesh Kushwaha13-Oct-14 6:41 
GeneralMy vote of 5 Pin
Sibeesh KV21-Sep-14 22:35
professionalSibeesh KV21-Sep-14 22:35 
GeneralRe: My vote of 5 Pin
Sarvesh Kushwaha22-Sep-14 5:55
memberSarvesh Kushwaha22-Sep-14 5:55 
GeneralRe: My vote of 5 Pin
Sibeesh KV22-Sep-14 6:24
professionalSibeesh KV22-Sep-14 6:24 
GeneralMy vote 5 Pin
Member 1100920112-Aug-14 17:56
memberMember 1100920112-Aug-14 17:56 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun20-Jun-14 3:45
memberHumayun Kabir Mamun20-Jun-14 3:45 
GeneralRe: My vote of 5 Pin
Sarvesh Kushwaha20-Jun-14 15:30
memberSarvesh Kushwaha20-Jun-14 15:30 
GeneralMy vote of 5 Pin
ravithejag17-Apr-14 23:38
memberravithejag17-Apr-14 23:38 
GeneralRe: My vote of 5 Pin
Sarvesh Kushwaha17-Apr-14 23:39
memberSarvesh Kushwaha17-Apr-14 23:39 
GeneralMy vote of 5 Pin
csharpbd23-Feb-14 8:47
membercsharpbd23-Feb-14 8:47 
GeneralRe: My vote of 5 Pin
Sarvesh Kushwaha23-Feb-14 17:21
memberSarvesh Kushwaha23-Feb-14 17:21 
Generaltest Pin
Deep Hemant Gabra25-Jan-14 7:41
memberDeep Hemant Gabra25-Jan-14 7:41 
GeneralRe: test Pin
Deep Hemant Gabra25-Jan-14 7:42
memberDeep Hemant Gabra25-Jan-14 7:42 
GeneralRe: test Pin
Sarvesh Kushwaha29-Jan-14 18:49
memberSarvesh Kushwaha29-Jan-14 18:49 
GeneralMy vote of 5 Pin
Renju Vinod23-Jan-14 1:31
professionalRenju Vinod23-Jan-14 1:31 
GeneralMy vote of 5 Pin
Mihai MOGA13-Jul-13 20:55
professionalMihai MOGA13-Jul-13 20:55 
GeneralRe: My vote of 5 Pin
Sarvesh Kushwaha14-Jul-13 19:40
memberSarvesh Kushwaha14-Jul-13 19:40 
GeneralMy vote of 5 Pin
Prasad Khandekar9-Jul-13 20:55
professionalPrasad Khandekar9-Jul-13 20:55 
GeneralRe: My vote of 5 Pin
Sarvesh Kushwaha9-Jul-13 21:09
memberSarvesh Kushwaha9-Jul-13 21:09 
GeneralRe: My vote of 5 Pin
Sarvesh Kushwaha9-Jul-13 21:21
memberSarvesh Kushwaha9-Jul-13 21:21 
GeneralEmbedded Dynamic Sql is just asking for trouble Pin
John B Oliver30-Jun-13 12:35
memberJohn B Oliver30-Jun-13 12:35 
GeneralRe: Embedded Dynamic Sql is just asking for trouble Pin
midnight_21-Jul-13 20:00
membermidnight_21-Jul-13 20:00 
GeneralRe: Embedded Dynamic Sql is just asking for trouble Pin
Jan Steyn14-Oct-13 0:41
memberJan Steyn14-Oct-13 0:41 
GeneralRe: Embedded Dynamic Sql is just asking for trouble Pin
Member 1168233522-Jun-15 9:52
memberMember 1168233522-Jun-15 9:52 
QuestionDatabase rights Pin
Member 988818818-Jun-13 5:59
memberMember 988818818-Jun-13 5:59 
Hi,
As stated on the article, I only use parameterised stored procedures to interact with the database.
As an extra layer of security whatever SQL login is used to connect to the database by my application is only given Execute rights on those stored procedures. i.e. no direct SELECT, UPDATE, DELETE, INSERT rights on any table but only execute rights on the appropriate Stored Procedures. Thus, even if they managed to bypass the Stored Procedure, they still could not directly do anything to the tables under that account. I also use a different SQL user for each database / application to prevent cross contamination.

A wise man once told me about car crime you can never make your car 100% proof but if the thief sees he will spend more time trying to get your car that the one next to it then he will move on. I apply the same logic to securing my applications.
SuggestionI use this method to prevent SQL Injection Pin
adriancs8-Jun-13 16:39
mvpadriancs8-Jun-13 16:39 
GeneralRe: I use this method to prevent SQL Injection Pin
Sarvesh Kushwaha9-Jun-13 19:01
memberSarvesh Kushwaha9-Jun-13 19:01 
GeneralRe: I use this method to prevent SQL Injection Pin
Richard Deeming18-Jun-13 3:38
professionalRichard Deeming18-Jun-13 3:38 
GeneralRe: I use this method to prevent SQL Injection Pin
Sarvesh Kushwaha18-Jun-13 4:03
memberSarvesh Kushwaha18-Jun-13 4:03 
GeneralRe: I use this method to prevent SQL Injection Pin
Richard Deeming18-Jun-13 4:15
professionalRichard Deeming18-Jun-13 4:15 
GeneralMy vote of 5 Pin
Carsten V2.07-Jun-13 11:15
memberCarsten V2.07-Jun-13 11:15 
GeneralRe: My vote of 5 Pin
Sarvesh Kushwaha8-Jun-13 4:01
memberSarvesh Kushwaha8-Jun-13 4:01 
GeneralRe: My vote of 5 Pin
Carsten V2.08-Jun-13 5:11
memberCarsten V2.08-Jun-13 5:11 
GeneralMy vote of 5 Pin
John Bracey7-Jun-13 10:18
groupJohn Bracey7-Jun-13 10:18 
GeneralRe: My vote of 5 Pin
Sarvesh Kushwaha8-Jun-13 4:01
memberSarvesh Kushwaha8-Jun-13 4:01 

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
Web02 | 2.8.150819.1 | Last Updated 8 Jun 2013
Article Copyright 2013 by Sarvesh Kushwaha
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid