Using Parameterized Query to Avoid SQL Injection

Introduction

To understand why you need to use a parameterized query to avoid SQL Injection over a concatenated inline query you need to understand SQL Injection.

SQL Injection

In SQL Injection, when an end user sends some invalid input to a CRUD operation or forcibly executes the wrong query into the database, that can be harmful for the database.

Harmful means "data loss" or "invalid inputs".

To learn more, use the following procedure.

Step 1

Create a table named "Login" in any database.

create table User_Login
(
   UserID varchar(20),
   Pwd varchar(20)
)

Now save some user credentials into the database for login purposes and select the table.

insert into user_login values('rahul','bansal@123')
insert into user_login values('bansal','rahul@123')

Step 2

Create a website named "Website1".



Now I will create a login page named "Default.aspx" to validate the credentials from the "Login" table and if the user is valid then redirect to it to the next page named "Home.aspx".

Add 2 textboxes for UserID and Password respectively and a button for login.



Add 2 namespaces in the .cs file of the "Default.aspx".

using System.Data.SqlClient;
using System.Data;

Now add the following code to validate the credentials from the database on the click event of the Login button.

protected void btn_Login_Click(object sender, EventArgs e)
{
    string constr = System.Configuration.ConfigurationManager.ConnectionStrings["Constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    string sql = "select count(UserID) from user_login where UserID='" + txtUSerID.Text + "' and pwd='" + txtPwd.Text + "'";
    SqlCommand cmd = new SqlCommand(sql, con);
    con.Open();
    object res = cmd.ExecuteScalar();
    con.Close();
    if (Convert.ToInt32(res) > 0) Response.Redirect("Home.aspx");
    else
    {
        Response.Write("Invalid Credentials");
        return;
    }
}

Add a new page named "Home.aspx".



Where any valid user will get a welcome message.



Step 3

Now run the "Default" page and log in with valid credentials.



It will redirect to the next page "Home.aspx" for valid user.



Note: 

1. Here I have not used the TextMode="Password" property in the password TextBox to show the password.
   
2. I have not used any input validations to explain my example.

Problem: Now I will do the SQL injection with some invalid credentials with successful query execution and then I will redirect to the next page "Home.aspx" as a valid user.

I will enter a string in both textboxes like the following:

‘ or ‘1’=’1

Now run the page and login with the preceding string in both textboxes.



It will redirect to next page name "Home.aspx" for valid user.



See what happened. This is called SQL injection in the hacking world.

Reason

It happened just because of the string and after filling in this string in both textboxes our SQL query became like the following:

select count(UserID) from user_login where UserID='' or '1'='1' and pwd='' or '1'='1'

That will provide the UserID count and that is 2 in the table because 2 users are in the "user_login" table.

It can be used in more ways like just fill in the following string only in the User ID TextBox and you will go the next page as a valid user.

or 1=1 - -

And it will also provide users count 2 because SQLquery will become like the following:

select count(UserID) from user_login where UserID='' or 1=1 --' and pwd='' or '1'='1'

Note: The sign -- are for commenting the preceding text in SQL.

It can be more harmful or dangerous when the invalid user/hacker executes a script to drop all tables in the database or drop an entire database.

Solution

To resolve this issue you need to do 2 things:

1. Always use a parameterized query.
2. Input validations on both the client and server side.

Sometimes if your input validation fails then parameterized will not execute any scripted value.

Let's see the example.

protected void btn_Login_Click(object sender, EventArgs e)
{
    string constr = System.Configuration.ConfigurationManager.ConnectionStrings["Constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    string sql = "select count(UserID) from user_login where UserID=@UserID and pwd=@pwd";
    SqlCommand cmd = new SqlCommand(sql, con);
    SqlParameter[] param = new SqlParameter[2];
    param[0] = new SqlParameter("@UserID", txtUSerID.Text);
    param[1] = new SqlParameter("@pwd", txtPwd.Text);
    cmd.Parameters.Add(param[0]);
    cmd.Parameters.Add(param[1]);
    con.Open();
    object res = cmd.ExecuteScalar();
    con.Close();
    if (Convert.ToInt32(res) > 0) Response.Redirect("Home.aspx");
    else
    {
        Response.Write("Invalid Credentials");
        return;
    }
}

Now if I run the page and try to login with SQL scripts as done earlier.

With ‘ or ‘1’=’1



With ' or 1=1 - -



As you have seen, the parameterized didn't execute the SQL Script, but why?

Reason: The reason behind this is the parameterized query would not be vulnerable and would instead look for a user id or password that literally matched the entire string.

In other words "The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally and not as part of the SQL to be executed".

Conclusion: Always use a parameterized query and input validations on both the client and server side.