An SQL query comprises one or more SQL commands, such as SELECT, UPDATE or INSERT. For SELECT queries, each query typically has a clause by which it returns data, It’s these types of queries that make the SQL language so popular and flexible… it’s also what makes it open to SQL injection attacks. As the name suggests, an SQL injection attack "injects" or manipulates SQL code. By adding unexpected SQL to a query, it is possible to manipulate a database in many unanticipated ways.
One of the most popular ways to validate a user on a Website is to provide them with an HTML form through which they can enter their username and password. Let’s assume that we have the following simple HTML form:
With the blow example we will understand the SQL Injection You enter the ID of the users, you want to search and click the Search users button. If a match is found in the database, we show the users record in the GridView.
SQL-Server
| create table users (
) |
insert into users(userName, userPass) values('abc', 'a')
insert into users(userName, userPass) values('pqr', 'p')
insert into users(userName, userPass) values('xyz', 'x')
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Pagetitle>
head>
<body>
<form id="form1" runat="server">
<div>
User ID:<asp:TextBox ID="TextBox1" runat="server">asp:TextBox>
<br>
<br>
<asp:Button ID="Button1" runat="server" Text="Search" OnClick="Button1_Click" />
<br />
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
Code Behind
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection Objcon = new SqlConnection("Data Source=localhost;Initial Catalog=myDB;integrated security=SSPI");
SqlCommand Objcmd = new SqlCommand();
DataSet Objds = new DataSet();
Objcmd.Connection = Objcon;
Objcmd.CommandType = CommandType.Text;
Objcmd.CommandText = "select * from users where userid='" + TextBox1.Text.ToString() + "'";
SqlDataAdapter Objda = new SqlDataAdapter();
Objda.SelectCommand = Objcmd;
Objda.Fill(Objds);
GridView1.DataSource = Objds.Tables[0];
GridView1.DataBind();
}
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 user ID that we typed into the TextBox.
So, for example, if we had typed 3 into the user ID textbox, we will have a SQL query as shown below.
Select * from users where userid=’3’
If a malicious user, types something like ‘3 OR 1=1-- into the TextBox, then we will have a SQL query as shown below.
Select * from users where userid=’’ OR 1=1 –‘
When this query is executed, it shows all the data in the users 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.
SQL-SERVER
CREATE PROCEDURE sp_getUsers
@Id VARCHAR(255)
AS
BEGIN
SELECT * FROM users WHERE userid=@Id
END
GO
CODE Behind
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection Objcon = new SqlConnection("Data Source=localhost;Initial Catalog=myDB;integrated security=SSPI");
SqlCommand Objcmd = new SqlCommand();
DataSet Objds = new DataSet();
Objcmd.Connection = Objcon;
Objcmd.CommandType = CommandType.StoredProcedure;
Objcmd.Parameters.Add("@Id", SqlDbType.VarChar, 255, "userid").Value = TextBox1.Text.ToString();
Objcmd.CommandText = "sp_GetUsers";
SqlDataAdapter Objda = new SqlDataAdapter();
Objda.SelectCommand = Objcmd;
Objda.Fill(Objds);
GridView1.DataSource = Objds.Tables[0];
GridView1.DataBind();
}
Now types something like ‘3 OR 1=1-- into the TextBox, then it will thrown an exception
Comments
Post a Comment