This site is the archived OWASP Foundation Wiki and is no longer accepting Account Requests.
To view the new OWASP Foundation website, please visit https://owasp.org
OWASP Backend Security Project .NET Security Programming
Overview
In this section we would like to explain what is the best solution for .NET programmer to avoid the sql injection when one of the most causes of attacking web applications.
In this context we will analize the interaction between a web application written in ASP.NET 2.0 and a SQL Server 2005 data provider.
If we try to understand what is sql injection, we have to thinking about the words “sql injection”. That is “injection of sql code in a context of execution of sql code”.
So we need both the conditions to try to exploit a web application with this kind of flaw:
- A particular point of the application that accepts input from the (malicious) user, input that will have an interaction with a database
- Input that we can manipulate in a particualr manner, injecting sql code
Imagine we have a database called “ExampleDB” in which we have some tables. One of these tables is “Users”. From a web application we want simply to query the database to extract information about the users through name.
The project is simple, one .aspx page with a textbox in which we have to insert the name of the user and the program will return the information, reading from ExampleDB. It's not important to specify how it's possibile to create an aspx page So the focus is the code that we have to write to interact with the database.
Description
We have two approach: inline query or stored procedure.
Case 1: INLINE QUERY
Inline queries are the queries in which we can compose a sql statement trough string concatenation. If we click the first button, we generate the execution of the OnClick event, that do the following:
protected void btnQueryInline_OnCLick(object sender, EventArgs e)
{
DbHelper dbHelper = new DbHelper();
string connectionString = dbHelper.returnConnectionString();
SqlConnection sqlConnection = new SqlConnection(connectionString);
try
{
sqlConnection.Open();
SqlCommand cmd = new SqlCommand("select Name,Surname,Code from dbo.Users where Name LIKE '%"
+ txtQueryInline.Text + "%'", sqlConnection);
txtQueryInline.Text + "%'", sqlConnection);
cmd.CommandType = CommandType.Text;
DataSet ds = new DataSet();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
sqlDataAdapter.Fill(ds, "ResultTable");
gridresult.DataSource = ds;
gridresult.DataBind();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if(sqlConnection != null)
sqlConnection.Close(); //close the connection
}
}
First Section:
DbHelper dbHelper = new DbHelper();
string connectionString = dbHelper.returnConnectionString();
SqlConnection sqlConnection = new SqlConnection(connectionString);
This section of code provide the sqlConnection, reading the connectionString from the Web.Config file. This is important for the application because it represents the entry point to the database, the credentials of the user that can authenticate to ExampleDB.
Second Section:
sqlConnection.Open();
SqlCommand cmd = new SqlCommand("select Name,Surname,Code from dbo.Users where Name LIKE '%" +
txtQueryInline.Text + "%'", sqlConnection);
cmd.CommandType = CommandType.Text;
After we have indicated the credentials, we want to provide the sql code. In this section we use the SqlCommand class, in which we write the sql code, concatenating with the text to search. The type of the SqlCommand is “Text”, so it's clear that we provide sql code directly. This code is prone to sql injection, because we can manipulate the statement inejcting in the textbox, for example, the string:
'; sql statement --
where sql statement is any sql code that we can imagine (we can drop tables, add users, reconfigure the xp_cmdshell ecc...)
Third Section:
DataSet ds = new DataSet();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
sqlDataAdapter.Fill(ds, "ResultTable");
gridresult.DataSource = ds;
gridresult.DataBind();
The third section is useful to represent the result set of the query. It uses the ADO.NET “Dataset”, and an intermediate class called SqlDataAdapter, that adapts the sql data in a form that can be used by the Dataset Object.
We can improve this query avoiding sql injection, using the second form of interaction
Case 2: PARAMETRIZED QUERY + STORED PROCEDURE VULNERABLE
If we click the third button, we generate the execution of the OnClick event, that do the following:
protected void btnQueryStoredVuln_OnCLick(object sender, EventArgs e)
{
DbHelper dbHelper = new DbHelper();
string connectionString = dbHelper.returnConnectionString();
SqlConnection sqlConnection = new SqlConnection(connectionString);
try
{
sqlConnection.Open();
SqlCommand cmd = new SqlCommand("USP_SearchUserByNameVuln", sqlConnection);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pName = new SqlParameter("@Name", SqlDbType.VarChar, 50);
pName.Value = txtQueryStoredVuln.Text;
cmd.Parameters.Add(pName);
DataSet ds = new DataSet();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
sqlDataAdapter.Fill(ds, "ResultTable");
gridresult.DataSource = ds;
gridresult.DataBind();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (sqlConnection != null)
sqlConnection.Close(); //close the connection
}
}
Apparently this code it the same of the Case 2. The only difference is the name of the stored procedure invocated: in this case we have not USP_SearchUserByNameNotVuln but USP_SearchUserByNameVuln, which is represented by the code:
CREATE PROCEDURE [dbo].[USP_SearchUserByNameVuln]
@Name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StrSQL varchar(max)
SET @StrSQL =
'SELECT U.Name,U.Surname,U.Code
FROM dbo.Userss U
WHERE U.Name LIKE ' + %'+ @Name+ '%
EXEC (@StrSql)
END
This kind of stored procedure are not infrequent. We have the possibility to compose the statement as we want using parameters, function and so on.
Altough we have a parametrized query with a stored procedure, as we can see in the code, we can inject the same string
'; sql statement --
to inject a sql statement. Using the SQL Server function REPLACE, we can “patch” the problem without rewrite the store procedure, replacing all the single quote with a couple of single quote.
ALTER PROCEDURE [dbo].[USP_SearchUserByNameVuln]
@Name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StrSQL varchar(max)
SET @Name = REPLACE(@Name,',')
SET @StrSQL =
'SELECT U.Name,U.Surname,U.Code
FROM dbo.Userss U
WHERE U.Name LIKE ' + %'+ @Name+ '%
EXEC (@StrSql)
END
This third case explain the fact that the use of parametrized query with stored procedure not always resolve the security flaws caused by sql injection, if we don't make the right things!
Case 3: PARAMETRIZED QUERY + STORED PROCEDURE NOT VULNERABLE
We can modify the manner to execute the same query, using parametrized query in conjunction with stored procedure. If we click the second button, we generate the execution of the OnClick event, that do the following:
protected void btnQueryStored_OnCLick(object sender, EventArgs e)
{
DbHelper dbHelper = new DbHelper();
string connectionString = dbHelper.returnConnectionString();
SqlConnection sqlConnection = new SqlConnection(connectionString);
try
{
sqlConnection.Open();
SqlCommand cmd = new SqlCommand("USP_SearchUserByNameNotVuln", sqlConnection);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pName = new SqlParameter("@Name", SqlDbType.VarChar, 50);
pName.Value = txtQueryStored.Text;
cmd.Parameters.Add(pName);
DataSet ds = new DataSet();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
sqlDataAdapter.Fill(ds, "ResultTable");
gridresult.DataSource = ds;
gridresult.DataBind();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if(sqlConnection != null)
sqlConnection.Close(); //close the connection
}
}
First Section:
DbHelper dbHelper = new DbHelper();
string connectionString = dbHelper.returnConnectionString();
SqlConnection sqlConnection = new SqlConnection(connectionString);
This section is the same of the example above.
Second Section:
sqlConnection.Open();
SqlCommand cmd = new SqlCommand("USP_SearchUserByNameNotVuln", sqlConnection);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pName = new SqlParameter("@Name", SqlDbType.VarChar, 50);
pName.Value = txtQueryStored.Text;
cmd.Parameters.Add(pName);
In this section we use the SqlCommand class as we have made above, but, the type of the SqlCommand is “StoredProcedure” , so we don't provide sql code directly, but we have a procedure inside the database that make the job. This procdure is called USP_SearchUserByNameNotVuln and accept a Varchar(50) parameter callled @Name The code of the stored is simply:
CREATE PROCEDURE [dbo].[USP_SearchUserByNameNotVuln]
@Name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT
U.Name,
U.Surname,
U.Code
FROM
dbo.Users U
WHERE
U.Name LIKE '%' + @Name + '%'
END
To use the SqlParameter class we have to do three steps for each parameter passed to the stored procedure:
- Instantiate the parameter with the right name and type used in the stored procedure
- Assign the value (in this case the value given by the user in the textbox)
- Add the parameter to the SqlCommand
When the sql command is executed, the parameters will be replaced with values specified by the SqlParameter object.
In conclusion, this kind of query is not prone to sql injection, because it is not possibile to build ad hoc sql statement, due the correct use of Stored Procedure and the SqlParameter class
Third Section:
DataSet ds = new DataSet();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
sqlDataAdapter.Fill(ds, "ResultTable");
gridresult.DataSource = ds;
gridresult.DataBind();
This section is the same of the example above.
INPUT VALIDATION
Another point that is important to consider is the validation of the input. For example in a context in which a user has to insert (or select) some data in (or from) the database we have to think the worst case, that is the user can digit anything and submit anything to the server. To avoid this we must validate the user's input. To make this there are two strategies
1. White list
2. Black list
The first solution answers at this condition: "deny all, except what is explicitly signed in the list" The second solution answers at this condition: "allow all, except what is explicitly signed in the list"
The best solution for the security of the application is try to implement a valdation of the input based on the first case. This comes more simply with numeric input, range input or strings that follow a specific pattern (for example an e-mail or a date). It comes more difficult for strings with no specfic pattern (for example strings inserted in a search engine) in which often only a solution based on a black list is reasonably possible.
In a Web Application, there are two kinds of input validation:
1. client validations
2. server validations
There are reasons to use both the type of validation that can be summarize in these points:
- client validations increase performance (the application don't postback to the server) but cause a false sense of security (the validation can bypassed intercepting and manipulating the client request)
- server validations make worse performance but increase the security, because the validation is made by the server.
In ASP.NET to realize these concepts there are the server web control Validators:
- RequiredFieldValidator
- CompareValidator
- RangeValidator
- RegularExpressionValidator
- CustomValidator
Technically these object realize both type of validations: that is if the client support Javascript, the Validator uses first the client validation, and after that the page is validated on then server side too. If the client doesn't support Javascript, the validation is made only on the server side. In this manner the application validate the input in a progressive mode and the design of the application doesn't follow a "Minimum-Denominator-Multiplier"
To explain the concept in code, it's possible to analyze the CustomValidator, that is the higher generalization because it's possibile to use a personalized validation logic.
CustomValidator
We can think symply to a textbox with a button, like the example above, in which we want to search all the suppliers with a specific code (a string of 16 char) For security reasons we can imagine that a specific user can only see the suppliers that have a code in which the first 4 character are "PFHG"
Any other string that doesn't match this pattern has to be exclude from the search (white list approach)
The XML part of the page is like that:
<asp:Label ID="lblQuerySearch" runat="server" Text="Digit the first four number"></asp:Label>
<asp:TextBox ID="txtQuerySearch" Width="5em" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidatorQuerySearch" runat="server" ErrorMessage="Required Code"
ControlToValidate="txtQuerySearch"></asp:RequiredFieldValidator>
<asp:CustomValidator ID="CustomValidatorQuerySearch" runat="server" ErrorMessage="Wrong code"
ControlToValidate="txtQuerySearch" OnServerValidate="ValidateCode"></asp:CustomValidator>
The control Label and Button are intuitive web controls.
It's clear to see that we have applied two Validator to the textbox whose ID is "txtQuerySearch" . The Validators are:
- RequiredFieldValidator: we don't accept an empty textbox when we submit our request to the server
- CustomValidator: we would implement some custom logic to our textbox
In fact the CustomValidator tag has a particular event called "OnServerValidate" that we can hook to a custom callback function, whose code is executed on the server side. For this example it's like that:
protected void ValidateCode(object source, ServerValidateEventArgs args)
{
try
{
string textToValidate = args.Value;
if (textToValidate.Equals("PFHG"))
args.IsValid = true;
else
args.IsValid = false;
}
catch (Exception ex)
{
args.IsValid = false;
}
}
The function handles the argument "arg" that brings the text inserted by the user. If this text match with our pattern, the argument is valid and the server executes the code associated to the button, querying the database in the same manner seen above; however the code is now conditioned by the statement "if(Page.IsValid)".
protected void btnQuerySearch_OnCLick(object sender, EventArgs e)
{
DbHelper dbHelper = new DbHelper();
string connectionString = dbHelper.returnConnectionString();
SqlConnection sqlConnection = new SqlConnection(connectionString);
if (Page.IsValid)
{
try
{
sqlConnection.Open();
SqlCommand cmd = new SqlCommand("USP_SearchUserByCode", sqlConnection);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pCode = new SqlParameter("@Code", SqlDbType.VarChar, 4);
pCode.Value = txtQuerySearch.Text;
cmd.Parameters.Add(pCode);
DataSet ds = new DataSet();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
sqlDataAdapter.Fill(ds, "ResultTable");
gridresult.DataSource = ds;
gridresult.DataBind();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (sqlConnection != null)
sqlConnection.Close(); //close the connection
}
}
}
This is only an example but the use of the Validators can be very important in all the context in which we need to protect our database from malicious input.
