In this article I will explain SQL Injection attackand some examples of SQL Injection in SQL Server.
 
What is SQL Injection?
The word Injection means to inject something in your system and SQL Injection means injecting some SQL in your database system for hacking it to steal your information such has Username and Passwords for login authentication or causing harm to your system by deleting data or dropping tables.
 
Sample application for SQL Injection in ASP.Net
I will explain by creating a simple example in ASP.Net which has a vulnerability in it.
Database
I am making use of Microsoft Northwind database, you can find the instructions for downloading and installing in the article Install Microsoft Northwind and Pubs Sample databases in SQL Server Management Studio
 
HTML Markup
In the below HTML Markup, I have a TextBox, a Button and a GridView control. One can search records using Customer Id and that record will be listed in GridView.
Customer Id: <asp:TextBox ID="txtCustomerId" runat="server" />
<asp:Button Text="Submit" runat="server" OnClick="Submit" />
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="Customer Id" />
        <asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
    </Columns>
</asp:GridView>
 
 
Namespaces
You will need to install the following namespaces.
using System.Configuration;
using System.Data.SqlClient;
 
 
Code
The following event handler is executed when the Button is clicked. You will notice here I have made concatenating the value of the TextBox to the SQL Query and then the query is executed and its results are displayed in GridView.
protected void Submit(object sender, EventArgs e)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE CustomerId = '" + txtCustomerId.Text + "'"))
    {
        using (SqlConnection con = new SqlConnection(conString))
        {
            con.Open();
            cmd.Connection = con;
            GridView1.DataSource = cmd.ExecuteReader();
            GridView1.DataBind();
            con.Close();
        }
    }
}
 
Our application is ready and it is working as intended.
SQL Injection attack examples in SQL Server
 
 
SQL Injection Examples
Now I will give you different examples how one can inject SQL into the application we just build.
Note: Here the intension is not to make people learn SQL Injection attack but to explain how it is done so that while building websites, developers can take precaution of such flaws.
 
1. Hack to get all records from table
If I add the 'OR 1 = 1;-- to the TextBox then it will list all records as shown below
SQL Injection attack examples in SQL Server
 
The reason is that due to concatenation it is easier to manipulate the query as follows
SELECT * FROM Customers WHERE CustomerId = '' OR 1 = 1;--'
Here even if Customer Id blank record is not available but 1 = 1 becomes True and hence it lists all records present in the Customers Table.
 
2. Get records of other table
If I type ';SELECT * FROM Employees;-- in the TextBox, the SQL Query will be manipulated as follows
SELECT * FROM Customers WHERE CustomerId = '';SELECT * FROM Employees;--'
Thus it will list all records of the Employees table of the database as shown below
SQL Injection attack examples in SQL Server
 
3. Drop a Table
If I type ';DROP TABLE Persons;-- in the TextBox, the SQL Query will be manipulated as follows
SELECT * FROM Customers WHERE CustomerId = '';DROP TABLE Persons;--'
Thus this Query will first fire SELECT query on the Customers Table and then will delete the Persons Table from the database.
 
4. Delete data from Table
If I type ';DELETE FROM Persons;-- in the TextBox, the SQL Query will be manipulated as follows
SELECT * FROM Customers WHERE CustomerId = '';DELETE FROM Persons;--'
Thus this Query will first fire SELECT query on the Customers Table and then will delete all records from the Persons Table.
 
 
Prevention mechanisms and techniques to stop SQL Injection attack
In order to stop SQL Injection attack we need to work at different layers of our application and create some security boundaries and make it difficult for any hacker to penetrate into the database.
 
1. Parameterized Queries
The very basic way is to use Parameterized Queries i.e. instead of string concatenation you need to add parameters to the Query and pass parameter value using the SqlCommand object. Below is an example of parameterized query.
SELECT * FROM Customers WHERE CustomerId = @CustomerId
Above query cannot be manipulated and will completely stop SQL Injection. Parameterized Queries have been explained in detail in my article Using Parameterized queries to prevent sql injection Attacks in SQL Server.
 
2. Avoid dynamic building of SQL in Stored Procedures
If you are building dynamic SQL in stored procedures make sure you do not concatenate the parameter value directly in the dynamic SQL.
Make use of sp_executesql instead of EXEC or EXECUTE functions for executing dynamic SQL as sp_executesql is capable of executing parameterized queries. For more details please refer my article How to use and pass parameters to dynamic SQL in SQL Server.
 
 
3. Allow only alphabets, digits and some special characters
 
Another ways is to prevent typing of characters such as semi-colon (;), dash (-) or percentage (%) that used in SQL Queries. This can be achieved using JavaScript.
a. Using JavaScript
Allow only AlphaNumeric (Alphabets and Numbers) characters and space only in TextBox using JavaScript
 
b. Using ASP.Net FilteredTextBoxExtender
ASP.Net AJAX FilteredTextBoxExtender Control Example
Thus in this article I have covered SQL Injection, examples of SQL Injection and prevention mechanisms and techniques to stop SQL Injection attack.