SQL Injection in ASP.NET
SQL Injection is a technique to pass invalid input data to SQL Commands through Web Application for execution in SQL Server. In this article we discuss about how SQL Injection occurs in Asp.Net web application and how can we avoid SQL Injection in Asp.Net.
For example we have an Asp.Net web application which has login screen with User Name and Password fields. This login screen is passing these details to Validation page by using HTML POST method and in Validation page we are checking these details against SQL Server database table. Here we have Users SQL Server table as script given below.
USE [test]
GO
/****** Object: Table [dbo].[Users] Script Date: 03/24/2012 02:11:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[id] [int] IDENTITY(1,1) NOT NULL,
[userName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Insert some records into Users table by using below query.
Insert Into Users(userName,password) Values('abc','abc')
and the Login page is as shown below.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Login Page</title>
</head>
<body>
<form name="frmLogin" action="Validation.aspx" method="post">
Username: <input type="text" id="txtUserName" name="txtUserName" /><br /><br />
Password: <input type="password" id="txtPassword" name="txtPassword" /><br /><br />
<input type="submit" value="Login" /> <input type="reset" value="Clear" />
</form>
</body>
</html>
Here we are passing the user name and password fields to validation page. In Validation.aspx we are validating the user credentials against Users table in test data base as shown below.
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class Validation : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strUserName, strPassword;
string sql;
try
{
strUserName = Request.Form["txtUserName"];
strPassword = Request.Form["txtPassword"];
if (strUserName == null || strPassword == null)
{
Response.Redirect("Login.aspx");
return;
}
SqlConnection con = new SqlConnection("Data Source=HOME-9AE5EAAEEE;Initial Catalog=test;Integrated Security=SSPI;");
sql = "Select * from Users where userName='" + strUserName + "' and password = '" + strPassword + "'";
SqlCommand sqlCom = new SqlCommand(sql, con);
con.Open();
SqlDataReader sqldr = sqlCom.ExecuteReader();
if (sqldr.Read())
{
Session["userName"] = sqldr["userName"];
}
else
Response.Redirect("Login.aspx");
sqldr.Close();
con.Close();
Response.Redirect("Default.aspx");
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
}
Enter user name and password fileds as “abc”, Login was successful and you are navigating to Default.aspx page.
Now try to enter invalid username and password fields, you are redirect to Login page again. But enter user name as “abc” and password as “' Or ”'1' = '1“, then SQL query form as below.
Select * from Users where userName='abc' and password = '' Or '1' = '1'
By using above query, you are able to login successfully even you are entering wrong password. Even you can try with different userName and password field’s combination to login. This is called SQL Injection. If you enter the single quotes (‘) in userName or password fields, your application produces database exceptions.
You can avoid SQL Injection in Asp.Net by defining the parameters for SQL query and mentioning the parameter direction as input or output as shown below.
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class Validation : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strUserName, strPassword;
string sql;
try
{
strUserName = Request.Form["txtUserName"];
strPassword = Request.Form["txtPassword"];
if (strUserName == null || strPassword == null)
{
Response.Redirect("Login.aspx");
return;
}
SqlConnection con = new SqlConnection("Data Source=HOME-9AE5EAAEEE;Initial Catalog=test;Integrated Security=SSPI;");
//sql = "Select * from Users where userName='" + strUserName + "' and password = '" + strPassword + "'";
//SqlCommand sqlCom = new SqlCommand(sql,con);
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = "Select * from Users Where userName=@userName and password=@strPassword";
sqlCom.Connection = con;
SqlParameter userNameParam = new SqlParameter("@userName", SqlDbType.VarChar, 50);
userNameParam.Direction = ParameterDirection.Input;
userNameParam.Value = strUserName;
sqlCom.Parameters.Add(userNameParam);
SqlParameter passwordParam = new SqlParameter("@strPassword", SqlDbType.VarChar, 50);
passwordParam.Direction = ParameterDirection.Input;
passwordParam.Value = strPassword;
sqlCom.Parameters.Add(passwordParam);
con.Open();
SqlDataReader sqldr = sqlCom.ExecuteReader();
if (sqldr.Read())
{
Session["userName"] = sqldr["userName"];
}
else
Response.Redirect("Login.aspx");
sqldr.Close();
con.Close();
Response.Redirect("Default.aspx");
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
}
As shown above, we are defining the SqlParameter for userName, password fields and mentioning those parameters direction as Input. By mentioning the parameter direction, it will consider input fields as only values for respected columns.
