What is SQL Injection Attack

Let us understand SQL injection attack, with an example. I have an Employee Search Page as shown in the image below. This webform has a very simple functionality. You enter the ID of the employee, you want to search and click the Search Employee button. If a match is found in the database, we show the employee record in the GridView.

Employee Search Page


Here is a youtube video that I have recorded on SQL Injection. Hope, you will find it useful.



The HTML for the Employee Serach Page is shown below. As you can see from the HTML, the Employee Serach Page contains TextBox, Button and a GridView control.


Employee Search Page HTML


The codebehind page for the EmployeeSearchPage is shown below.

Employee Search Page Code Behind

The Button1_Click event handler has the required ADO.NET code to get data from the database. This code is highly susceptible to sql injection attack and I will never ever have code like this in production environment. The second line in Button1_Click event handler, dynamically builds the sql query by concatenating the Employee ID that we typed into the TextBox.


So, for example, if we had typed 2 into the Employee ID textbox, we will have a SQL query as shown below.
Select * from Employees where Id=2

If a malicious user, types something like 2; Delete from Employees into the TextBox, then we will have a SQL query as shown below.
Select * from Employees where Id=2; Delete from Employees

When this query is executed, we loose all the data in the Employees table. This is SQL Injection Attack, as the user of the application is able to inject SQL and get it executed against the database. It is very easy to avoid SQL Injection attacks by using either parameterized queries or using stored procedures.

You may be thinking, how will the user of the application know the name of the table. Well, one way is to simply guess or inject a sql syntax error. The injected SQL syntax error causes the page to crash and can possibly reveal the name of the table as shown below. However, proper exception handling and custom error pages can be used to prevent the end user from seeing the yello screen of death. The screen shot below shows the table name Employees.

Page crash revealing Employees table name

To solve SQL injection attack, create a Stored Procedure as shown below.

Create Procedure spGetEmployees
@Id int
as
Begin
Select * from Employees where Id=@Id
End

Modify the codebehind page for the EmployeeSearchPage, to use the stored procedure as shown below


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


namespace TestWeb
{
    public partial class EmployeeSearch : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }


        protected void Button1_Click(object sender, EventArgs e)
        {
            // Create the SQL Connection object. 
            SqlConnection con = new SqlConnection
            ("server=localhost; database=TestDB; integrated security=SSPI");

            // Create the SQL command object. Pass the stored procedure name 
            // as a parameter to the constructor of the SQL command class
            SqlCommand cmd = new SqlCommand("spGetEmployees", con);
            // Create the SQL parameter object, specifying the name and the value 
            // we want to pass to the SP.
            SqlParameter paramId = new SqlParameter("@Id", txtEmployeeId.Text);
            // Associate the Id parameter object with the command object, using
            // parameters collection property 
of the SQL Command object. 

            cmd.Parameters.Add(paramId);
            // Specify the command type as stored procedure. This tells the command
            // object, that the command 
is a SQL stored procedure and not an adhoc sql query
            cmd.CommandType = CommandType.StoredProcedure;
            // Open the connection
            con.Open();
            // Execute the command and assign the returned results as the data source for 
            // the employyes girdview
            gvEmployees.DataSource = cmd.ExecuteReader();
            // Call the DataBind() method, to bind the results to the employees grid view control
            gvEmployees.DataBind();
            // Finally close the sql server connection object
            con.Close();
        }
    }
}

27 comments:

  1. You haven't mentioned the exact ways to avoid the situation. It would be great if you could explain it in detail.

    ReplyDelete
    Replies
    1. Use stored procedure instead of plain sql query in code.

      Delete
  2. In a way, he did, if you have a proper handling of the exception then you won't show an error like, exposing table names. One easy way, is imply wrap around the answer , in this case 2 with single quotes. that way is different this:
    select * from Employees where ID = 2;delete from Employees, than select * from Employees where ID = '2;delete from Employees'. The second one will generate an error that you can capture.

    ReplyDelete
  3. yep i also want an explaination that how to avoid it by showing eg through queries.

    ReplyDelete
  4. pleas explaine how to solve this problem

    ReplyDelete
  5. The solution is that we need to think as if we are tester and try to code according to ppl who may write such a kind of things. The better way is to always use store procs and fucntions to get values from database.

    ReplyDelete
  6. May be we can control that from the text box level, by avoiding the user to enter special characters or curtail the length of the textbox ! Or else u check for the validity of the query too ! Yes agree with all of you. He should have shown an example to avoid this.

    ReplyDelete
  7. For those who asked for a solution, he mentions it "It is very easy to avoid SQL Injection attacks by using either parameterized queries or using stored procedures."

    Always, always, always use stored procedures. This not only helps stop a SQL injection attack, it also encapsulates the SQL in the database and not in the web site code. Additionally security can be placed against that SP further prevent unauthorized execution.

    You can also implement javascript to validate the input for the form field to prevent another vector of attack.

    ReplyDelete
  8. nice explanation.i would happy if provide eg of how to avoid error by ussing procesures.i m expecting u will append soon

    ReplyDelete
  9. For a comprehensive approach to security, multiple layers are the best bet, and approaching the best practice:
    1. Asking for the id (primary key) for the input is questionable. Some other input is perferrable.
    2. Javascript/Jquery validation of the input is a good way of stopping problems at the client, before reaching the server. In this case, validating the string as a whole integer of a certain range (ie, 1 to 9999).
    3. Utilization of stored procedures.
    4. Fully specify the sql query (ie, "Select fname, lname from employees where lname like " + lname + "%", instead of "select *..."
    5. Put the connection string somewhere else, other than directly in the code. An encrypted config file is a valid choice.

    ReplyDelete
  10. Oh, missed this one:
    Validate input on the server side, as well. In this case, insuring you have a numeric value in the proper range (ie, 1 to 99999) before executing the sql code (hopefully in a stored procedure) is good practice

    ReplyDelete
  11. This is an employee search page and not a CRUD. so just have a select grant for the user. No delete or update grants. always validate the controls at the client site to avoid unnecessary round trips of post backs.

    ReplyDelete
  12. In addition to previous advices, you can also use user with read-only access for select queries.

    ReplyDelete
  13. SQl injection attack can easily be done on such a codes where we are exposing the query for eg 'Select * from table_name where id = 2'. We can avoid this either by good exception handling or writing stored procedures even for such small queries. Passing the stored procedure will not allow the malicious user to manipulate the queries .

    ReplyDelete
  14. Using stored procedures even for small queries like in the above example is very good practice to avoid sql injection attacks. The malicious user won't be able to manipulate the query inside the stored procedure. good Exception handling can also avoid sql injection attacks to an extent.

    ReplyDelete
  15. Simple way to solve the sql injection problem is to set the text-box max length property. The above method is also excellent. It is jst for this case only otherwise stored procedure is best.

    ReplyDelete
  16. Thanks good work. Very simple and clean.

    ReplyDelete
  17. Another way to avoid sql injection is to rip off special characters or disallow special characters from incoming values before passing values to query string

    ReplyDelete
  18. Cuuld anyone please explain how to avoid page crash like that so that we can easily implement the logic

    ReplyDelete
  19. In simple case like above use parameterized query
    Select * from employees where id={0},id
    And also use client and server side validation for text box

    ReplyDelete
  20. Does SQL Injection possible in desktop application

    ReplyDelete
  21. best way is to validate the field on the form, whether it should only accept integer or if accept string then strip out the character ';' and also handle sql error properly.

    for posting from a query string not from a form, make sure the query string does not contain character ';' before parse the query string into variables

    ReplyDelete
  22. @VenkatSir...Video explain a lot BRilliant...May b i m Too Late To get Your Tutorial...but you are Briliant

    ReplyDelete

If you are aware of any other asp.net questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us