Preventing SQL Injection in ADO.NET

• Download sample - 340.5 KB

Introduction 

What is SQL Injection? How it happen? How to prevent it? Google helped me with the definitions. SQL Injection is a code injection technique that exploits a code vulnerability occurring in the database layer of an application - Wikipedia. SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution - msdn and the answers for second and third questions are below!!   

How SQL Injection Occurs? 

Basically SQL Injection happens because of poor coding practises. SQL Injection occurs when hackers are trying to inject or insert pieces of strings which tends to break our logic and pave way them to hack our sites. This is how I found how SQL Injection occurs. Some three months back my mentor asked me to design a login page with which users must be able to sign up and then log in. Quite simple.. Isn't it? As a beginner I was very excited and this was my code

 First I designed my Sign Up logic as follows 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
 
namespace SampleHacking
{
    public partial class SignUp : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
 
        }
        protected void SignUp_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;" + 
              "AttachDbFilename=|DataDirectory|UserDetails.mdf;Integrated Security=True;User Instance=True");
            conn.Open();
            SqlCommand cmd = new SqlCommand("Insert into Authentication " + 
               "values('"+txt1.Text+"','"+txt2.Text+"')",conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            successlbl.Text = "Sign up successful go back to login page";
          
        }
        protected void BackBtn_Click(object sender, EventArgs e)
        {
            Response.Redirect("Default.aspx");
        }
    }
}

Logic here is pretty decent. I just got the values which the users have typed in the username and password textboxes and inserted them into the DB. Next I designed the login logic as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
 
namespace SampleHacking
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Session["user"]=txt1.Text;
            
 
        }
        protected void Login_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename" + 
               "=|DataDirectory|UserDetails.mdf;Integrated Security=True;User Instance=True");
            conn.Open();
 
            SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Authentication where Username='" + 
               txt1.Text + "'and password='" + txt2.Text + "'", conn);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            conn.Close();
            if (dt.Rows.Count != 0)
            {
                Response.Redirect("Redirected.aspx");
            }
            else
            {
                errorlbl.Text = "Incorrect username and password";
            }
        }
        protected void SignUp_Click(object sender, EventArgs e)
        {
            Response.Redirect("SignUp.aspx");
 
        }
    }
}

Here I am checking whether the DB has the username and corresponding password that the users have typed in the username and password textboxes and if the dataTable has rows redirect them else indicate users of wrong passwords.... Download the sample and run it without removing the comments. Sign Up and then login. Yipee!! working properly right??.. That is what I was also thinking till I heard this SQL Injection stuff. What I have did here is I have written a very very poor code which could be easily broken by hackers and enter my site.

Become A Hacker Now!!

Now just try entering a wrong username and password to check whether our login login is secure. Not able to Login??It is perhaps well secure isn't it? Now just type the following in the username and password text boxes 

Username   :  anything' OR 'x'='x 

Password    : anything' OR 'x'='x 

What happens now!! How did it happen ? What you have did now is you have successfully hacked a poorly coded site through SQL Injection. So this is how it works. My Query for checking the username and password existence is

SELECT * FROM Authentication where Username='" + txt1.Text + "'and password='" + txt2.Text + "'

Here txt1 and txt2 are name of the username and password textboxes respectively. So for example if I enter the username and password as K C R the query will be rendered as follows

SELECT * FROM Authentication where Username='K C R' and Password='K C R'

SELECT * FROM Authentication where Username='anything' OR 'x'='x' and Password='anything' OR 'x'='x' 

The values have changed the query itself and thus our logic too. It is checking whether username is "anything" or 'x'='x'. As 'x'='x' condition is  always true  it allows one to enter the site without checking DB itself. The same scenario is for password too. Now try typing the following credentials in the downloaded app 

 Username  :  anything' or 'x'='x'--  . Don't enter anything in password field

Observe the query

SELECT * FROM Authentication where Username='anything' or 'x'='x'-- and password='.....  

Here it gets still worse as '--' is used for commenting  in SQL the entire password validation query part is commented thus letting you easily go into the site.

Preventing SQL Injection:

How to prevent it? Simple, don't code your login page logic like this!! There are many authentication techniques available in .NET, use them. My solution here involves a bit of logical thinking and consciousness that my code should not be hacked.

Uncomment all the green comments in the downloaded source code and run it. Now try entering username and passwords for injection. What happened? You were not able to login isn't it? Is it like someone has taken your hacking skills !! be cool " /> That is because of these lines of codes that I added before doing login logic

SqlDataAdapter da = new SqlDataAdapter("Select Username from Authentication",conn);
DataTable dtCheckUsername = new DataTable();
da.Fill(dtCheckUsername);
dtCheckUsername.DefaultView.Sort = "Username";                                                    
int usernameRowIndex = dtCheckUsername.DefaultView.Find(txt1.Text);                            
SqlDataAdapter da1 = new SqlDataAdapter("Select Password from Authentication", conn);       
DataTable dtCheckPassword = new DataTable();
da1.Fill(dtCheckPassword);
dtCheckPassword.DefaultView.Sort = "Password";                                                   
int passwordRowIndex=dtCheckPassword.DefaultView.Find(txt2.Text);
if (usernameRowIndex != -1 && passwordRowIndex != -1)
   login logic goes here
else
   error msg

Here I am getting all the usernames and passwords in a separate table and consciously  checking whether the user entered username and password first of all exists. If it exists only I am doing the login page logic else error.

SQL Parameters 

The above mentioned method works fine but its not the best one. I just got comments circling around SQL parameters stuff. I wrote this for  a sample app. Real world apps have very large amount of data in their DB . For instance think of an app which has a million users, so traversing the DB each time is going to degrade your app's performance to a large extent. This is where SqlParameters comes in. I just modified the logic as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

namespace SampleHacking
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Session["user"]=txt1.Text;
            

        }
        protected void Login_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;" + 
               "AttachDbFilename=|DataDirectory|UserDetails.mdf;Integrated Security=True;User Instance=True");
            conn.Open();
           
            SqlCommand cmd = new SqlCommand("SELECT * FROM Authentication " + 
              "where Username=@Username and Password=@Password", conn);
            SqlParameter p1 = new SqlParameter();
            p1.ParameterName = "@Username";
            p1.Value = txt1.Text;
            cmd.Parameters.Add(p1);

            SqlParameter p2 = new SqlParameter();
            p2.ParameterName = "@Password";
            p2.Value = txt2.Text;
            cmd.Parameters.Add(p2);
            SqlDataReader rdr =  cmd.ExecuteReader();
            if(rdr.HasRows)
            {
                Response.Redirect("Redirected.aspx");
            }
            else
            {
                errorlbl.Text="Incorrect username and password";
            }
         
        }
        protected void SignUp_Click(object sender, EventArgs e)
        {
            Response.Redirect("SignUp.aspx");

        }
    }
}

This also prevents SQL injection and also performance is maintained solving our problem  

Finally 

I don't think anyone is still using such a logic for login pages nowadays. But as a beginner one always tend to think at the basic level. This is the least and best solution that a beginner can think for his/her login page but it is hackable! Now that we know what is SQL Injection, how it occurs ( you did it right?)  and how to prevent it, next time when coding any logic it must be broken only by us because developers don't find solutions they find the best solutions " /> Cheers " />