Understanding SQL Injections - How to prevent them

Hope you have read our first article on understanding SQL Injections with examples :
http://codingbasics.blogspot.com/2011/01/understanding-sql-injection-examples.html
If not , we advise you to read it first.

Okay... So how can prevent them ( using asp.net as code reference)
----------------------------------------------------------------------


Step 1. Constrain Input

You should validate all input to your ASP.NET applications for type, length, format, and range.
By constraining the input used in your data access queries, you can protect your application from SQL injection.

Note :  When constraining input, it is a good practice to create a list of acceptable characters and use regular expressions to reject any characters that are not on the list. The potential risk associated with using a list of unacceptable characters is that it is always possible to overlook an unacceptable character when defining the list; also, an unacceptable character can be represented in an alternate format to pass validation.
Constrain Input in ASP.NET Web Pages

Start by constraining input in the server-side code for your ASP.NET Web pages.
Do not rely on client-side validation because it can be easily bypassed.
Use client-side validation only to reduce round trips and to improve the user experience.

If you use server controls, use the ASP.NET validator controls, such as the RegularExpressionValidator and RangeValidator controls to constrain input.
If you use regular HTML input controls, use the Regex class in your server-side code to constrain input.

If in the previous code example, the SSN value is captured by an ASP.NET TextBox control, you can constrain its input by using a RegularExpressionValidator control as shown in the following.

<%@ language="C#" %>
<form id="form1" runat="server">
    <asp:TextBox ID="SSN" runat="server"/>
    <asp:RegularExpressionValidator ID="regexpSSN" runat="server"         
                                    ErrorMessage="Incorrect SSN Number" 
                                    ControlToValidate="SSN"         
                                    ValidationExpression="^\d{3}-\d{2}-\d{4}$" />
</form>
 
If the SSN input is from another source, such as an HTML control, a query string parameter, or a cookie,
you can constrain it by using the Regex class from the System.Text.RegularExpressions namespace.
The following example assumes that the input is obtained from a cookie.

using System.Text.RegularExpressions;


if (Regex.IsMatch(Request.Cookies["SSN"], "^\d{3}-\d{2}-\d{4}$"))
{
    // access the database
}
else
{
    // handle the bad input
}
 
Constrain Input in Data Access Code

In some situations, you need to provide validation in your data access code, perhaps in addition to your ASP.NET page-level validation.
Two common situations where you need to provide validation in your data access code are:

Untrusted clients. 
If the data can come from an untrusted source or you cannot guarantee how well the data has been validated and constrained, add validation logic that constrains input to your data access routines.
Library code. If your data access code is packaged as a library designed for use by multiple applications, your data access code should perform its own validation, because you can make no safe assumptions about the client applications.

The following example shows how a data access routine can validate its input parameters by using regular expressions prior to using the parameters in a SQL statement.

using System;
using System.Text.RegularExpressions;


public void CreateNewUserAccount(string name, string password)
{
    // Check name contains only lower case or upper case letters, 
    // the apostrophe, a dot, or white space. Also check it is 
    // between 1 and 40 characters long
    if ( !Regex.IsMatch(userIDTxt.Text, @"^[a-zA-Z'./s]{1,40}$"))
      throw new FormatException("Invalid name format");


    // Check password contains at least one digit, one lower case 
    // letter, one uppercase letter, and is between 8 and 10 
    // characters long
    if ( !Regex.IsMatch(passwordTxt.Text, 
                      @"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$" ))
      throw new FormatException("Invalid password format");


    // Perform data access logic (using type safe parameters)
    ...
}
 
Step 2. Use Parameters with Stored Procedures

Using stored procedures does not necessarily prevent SQL injection.
The important thing to do is use parameters with stored procedures.
If you do not use parameters, your stored procedures can be susceptible to SQL injection if they use unfiltered input as described in the "Overview" section of this document.

The following code shows how to use SqlParameterCollection when calling a stored procedure.

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


using (SqlConnection connection = new SqlConnection(connectionString))
{
  DataSet userDataset = new DataSet();
  SqlDataAdapter myCommand = new SqlDataAdapter( 
             "LoginStoredProcedure", connection);
  myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
  myCommand.SelectCommand.Parameters.Add("@id", SqlDbType.VarChar, 11);
  myCommand.SelectCommand.Parameters["@id"].Value = SSN.Text;


  myCommand.Fill(userDataset);
}
 
In this case, the @id parameter is treated as a literal value and not as executable code.
Also, the parameter is checked for type and length.
In the preceding code example, the input value cannot be longer than 11 characters.
If the data does not conform to the type or length defined by the parameter, the SqlParameter class throws an exception.

Review Your Application's Use of Parameterized Stored Procedures

Because using stored procedures with parameters does not necessarily prevent SQL injection, you should review your application's use of this type of stored procedure. For example, the following parameterized stored procedure has several security vulnerabilities.

CREATE PROCEDURE dbo.RunQuery
@var ntext
AS
        exec sp_executesql @var
GO
  
An application that uses a stored procedure similar to the one in the preceding code example has the following vulnerabilities:

The stored procedure executes whatever statement is passed to it. Consider the @var variable being set to:
DROP TABLE ORDERS;
 
In this case, the ORDERS table will be dropped.

The stored procedure runs with dbo privileges.
The stored procedure's name (RunQuery) is a poor choice. If an attacker is able to probe the database, he or she will see the name of the stored procedure.
With a name like RunQuery, he can guess that the stored procedure is likely to run the supplied query.

Step 3. Use Parameters with Dynamic SQL

If you cannot use stored procedures, you should still use parameters when constructing dynamic SQL statements.
The following code shows how to use SqlParametersCollection with dynamic SQL.

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

using (SqlConnection connection = new SqlConnection(connectionString))
{
  DataSet userDataset = new DataSet();
  SqlDataAdapter myDataAdapter = new SqlDataAdapter(
         "SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id",
         connection);              
  myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
  myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;
  myDataAdapter.Fill(userDataset);
}
 
Using Parameter Batching

A common misconception is that if you concatenate several SQL statements to send a batch of statements to the server in a single round trip, you cannot use parameters. However, you can use this technique if you make sure that parameter names are not repeated. You can easily do this by making sure that you use unique parameter names during SQL text concatenation, as shown here.

using System.Data;
using System.Data.SqlClient;
. . .
using (SqlConnection connection = new SqlConnection(connectionString))
{
  SqlDataAdapter dataAdapter = new SqlDataAdapter(
       "SELECT CustomerID INTO #Temp1 FROM Customers " +
       "WHERE CustomerID > @custIDParm; SELECT CompanyName FROM Customers " +
       "WHERE Country = @countryParm and CustomerID IN " +
       "(SELECT CustomerID FROM #Temp1);",
       connection);
  SqlParameter custIDParm = dataAdapter.SelectCommand.Parameters.Add(
                                          "@custIDParm", SqlDbType.NChar, 5);
  custIDParm.Value = customerID.Text;


  SqlParameter countryParm = dataAdapter.SelectCommand.Parameters.Add(
                                      "@countryParm", SqlDbType.NVarChar, 15);
  countryParm.Value = country.Text;


  connection.Open();
  DataSet dataSet = new DataSet();
  dataAdapter.Fill(dataSet);
}
. . .
  
Additional Considerations

Other things to consider when you develop countermeasures to prevent SQL injection include:

Use escape routines to handle special input characters.
Use a least-privileged database account.
Avoid disclosing error information.
Use Escape Routines to Handle Special Input Characters

In situations where parameterized SQL cannot be used and you are forced to use dynamic SQL instead, you need to safeguard against input characters that have special meaning to SQL Server (such as the single quote character). If not handled, special characters such as the single quote character in the input can be utilized to cause SQL injection.

Note   Special input characters pose a threat only with dynamic SQL and not when using parameterized SQL.
Escape routines add an escape character to characters that have special meaning to SQL Server, thereby making them harmless. This is illustrated in the following code fragment:

private string SafeSqlLiteral(string inputSQL)
{
  return inputSQL.Replace("'", "''");
}

Use a Least-Privileged Database Account

Your application should connect to the database by using a least-privileged account. If you use Windows authentication to connect, the Windows account should be least-privileged from an operating system perspective and should have limited privileges and limited ability to access Windows resources. Additionally, whether or not you use Windows authentication or SQL authentication, the corresponding SQL Server login should be restricted by permissions in the database.

Avoid Disclosing Error Information

Use structured exception handling to catch errors and prevent them from propagating back to the client. Log detailed error information locally, but return limited error details to the client.

If errors occur while the user is connecting to the database, be sure that you provide only limited information about the nature of the error to the user.
If you disclose information related to data access and database errors, you could provide a malicious user with useful information that he or she can use to compromise your database security. Attackers use the information in detailed error messages to help deconstruct a SQL query that they are trying to inject with malicious code.
A detailed error message may reveal valuable information such as the connection string, SQL server name, or table and database naming conventions.

Hope you had good time preventing SQL Injections.

Understanding SQL Injection- Examples .

SQL injection is a hacking technique which attempts to pass SQL commands through a web application for execution by a database.

SQL Injection arises because the fields available for user input allow SQL statements to pass through and query the database directly.

Common vulnerabilities that make your data access code susceptible to SQL injection attacks include:


Weak input validation.
Dynamic construction of SQL statements without the use of type-safe parameters.
Use of over-privileged database logins.

Example of a SQLInjection Attack

Here is a sample basic HTML form with two inputs, login and password.

<form method="post" action="http://testasp.myweb.com/login.asp"> 
<input name="name" type="text" id="name"> 
<input name="password" type="password" id="password"> 
</form>

The easiest way for the login.asp to work is by building a database query that looks like this:

SELECT id
FROM logins
WHERE username = '$username'
AND password = '$password’

If the variables $username and $password are requested directly from the user's input, this can easily be compromised. Suppose that we gave "Joe" as a username and that the following string was provided as a password: anything' OR 'x'='x

SELECT id
FROM logins
WHERE username = 'xyz'
AND password = 'anything' OR 'x'='x'

As the inputs of the web application are not properly sanitised, the use of the single quotes has turned the WHERE SQL command into a two-component clause.

The 'x'='x' part guarantees to be true regardless of what the first part contains.

This will allow the attacker to bypass the login form without actually knowing a valid username / password combination!

Another Example

In the following example, assume that a web site is being used to mount an attack on the database. If you think about a typical SQL statement, you might think of something like:

SELECT ProductName, QuantityPerUnit, UnitPrice 
FROM Products 
WHERE ProductName LIKE '%'

The objective of the attacker is to inject their own SQL into the statement that the application will use to query the database. If, for instance, the above query was generated from a search feature on a web site, then they user may have inserted the "G" as their query. If the server side code then inserts the user input directly into the SQL statement, it might look like this:

string sql = "SELECT ProductName, QuantityPerUnit, UnitPrice "+
    "FROM Products " +
    "WHERE ProductName LIKE '"+this.search.Text+"%';
SqlDataAdapter da = new SqlDataAdapter(sql, DbCommand);
da.Fill(productDataSet);

This is all fine if the data is valid, but what if the user types something unexpected? What happens if the user types:

' UNION SELECT name, type, id FROM sysobjects;--

Note the initial apostrophe; it closes the opening quote in the original SQL statement. Also, note the two dashes at the end; that starts a comment, which means that anything left in the original SQL statement is ignored.

Now, when the attacker views the page that was meant to list the products the user has searched for, they get a list of all the names of all the objects in the database and the type of object that they are. From this list, the attacker can see that there is a table called Users. If they take note of the id for the Users table, they could then inject the following:

' UNION SELECT name, '', length FROM syscolumns 
WHERE id = 1845581613;--

This would give them a list of the column names in the Users table. Now they have enough information to get access to a list of users, passwords, and if they have admin privileges on the web site.

' UNION SELECT UserName, Password, IsAdmin FROM Users;--

Assume that there is a table called Users which has columns called UserName and Password, it is possible to union that with the original query and the results will be interpreted as if the UserName was the name of the product and the Password was the quantity per unit. Finally, because the attacker discovered that there is a IsAdmin column, they are likely to retrieve the information in that too.


Let us go to next page for Example 3.