Prevent SQL Injection attacks in .Net

Prevent SQL Injection attacks in .Net

SQL Injection is used to attack the security of a web application/website by inputting SQL statements (e.g. in URLs) in a web form to get a badly designed website to perform operations on the database. Although the website is properly designed, hackers creating SQL advantage of developer’s general practice of writing non-standard code.
SQL injection Attack (SQLIA) is considered one of the top 10 web application vulnerabilities.
If a user logging-in into a website over some web-form. We ask the user to provide username and password. Based on the user's inputs we construct a database query and validate the user. In SQL Injection a hacker input some special commands/key-words in web-form input fields. These input commands used as parameters in the database query and this query executes some command over the database. Website code creates a query with the combination of input command and database query language. This changes the meaning of the created query and executes commands which should not be executed. For example-

Select * from tblUsers where username=’username’ and password=’password’

The hacker enters the command in such a way that, that modifies generated query. For example-

Select * from tblUsers where username=’username’ and password=’password’; Drop table tblUsers

This happens due to some bad programming practice of writing programmes and deep knowledge of hackers of the structure of web-application and how web-application communicates with the database.
Before going forward and find out approaches to prevent these attacks, lets first understand types of SQL injection attacks.

Types of SQL Injection attack

Here we will discuss commonly known types of SQL Injection attacks.

AND/OR Attack:
Here we are considering an example of authenticating a user from a web form. In this case, a typical web programmer takes user input values from a web form. These values represent usernames and passwords and then programmer place these values directly into a SQL statement to be run against a database.

Suppose the database table structure is as follows:
Table name: UserAuthentication
Username column name: UserName
Password column name: Password

Following example shows this approach

SELECT username, password
FROM UserAuthentication
WHERE username = 'usernameForm'
AND password = 'passwordForm';

In this example, the values usernameForm and passwordForm are the values captured from the web form.
Here we are checking whether values entered by a user matches with any columns username and password in the UserAuthentication table in Database. If any rows are matched, the user is authenticated otherwise, the appropriate message returned to the user saying invalid login.
However, if the web programmer is not aware with SQL Injection attack and uses this simple approach and the form values without checking them, a hacker may instead pass arbitrary values that the programmer did not originally anticipate. One such attack is the basic attack that involves the AND or OR logic in the SQL predicate.

The hacker can specify a valid username such as “Satya” and then specify the password as “' OR '1'='1” in the form. The final generated SQL query that uses these values will be:

SELECT username, password
FROM UserAuthentication
WHERE username = 'Satya'
AND password = '' OR '1'='1';

Here we are assuming that “Satya” is a valid user, the database will allow the hacker to log-in and proceed as “Satya” even password is wrong because even the password string is not empty, ‘1’=‘1’ is a valid predicate that will always return TRUE. Thus, the hacker has just accessed the account without knowing the password, and now the hacker has full access to the victim’s information.
If the attack does not succeed, the attacker simply moves on and tries another method. If it succeeds, the hacker now has access to the database through that username.

Comments Attack
As we know that an SQL statement or stored-procedure allows inline commenting within the SQL code. This allows SQL comments attacks. If we consider the above example then the hacker can specify a valid username such as "admin'--" and then specify the password as "anystring" in the form. The final generated SQL query that uses these values will be:

SELECT username, password
FROM UserAuthentication
WHERE username = 'admin' --' AND password = 'anything';

Everything after the “--” in the WHERE clause will be ignored so this will allow the hacker to log in as “admin”.
This is a method of using comments as a way of ignoring the rest of the query.

String Concatenation Attack
SQL has many options to concatenate multiple strings or characters to form a new string. This can be accomplished using the following approaches

Plus size (+)
Double pipe (||)
Function CONCAT

These string concatenation operators can be used to create a variation of the UNION Injection attack by obfuscating the UNION keyword in a string concatenation operation.
For example, an attacker may use
‘UNI’ + ‘ON A’ + ‘LL’ in place of “UNION ALL” if he suspects the system is checking and looking for "UNION ALL" keyword.
CHAR() in place of "'" if he suspects the system is searching for single quotes (‘).
CONCAT(CHAR(65), CHAR(68), CHAR(77), CHAR(73), CHAR(78)) in place of "ADMIN" if he suspects the system is checking for a single quote. In this case, the system will not find a single quote if it was looking for them.

UNION Injection Attack
This type of attack (if successful), doesn't only relies on data from a table, on which query is being executed but also return another table data as well. This attack may be the most dangerous. For example, an attacker can modify the SQL query statement that selects from the user authentication table to select another table such as the Accounting table.

SELECT username, password FROM userAuthentication
UNION ALL
SELECT accountNum, balance FROM Accounting

The use of UNION ALL in this attack allows the attacker access to tables that the SQL query statement was not originally designed for. The resulting rows selected from both tables will appear on the resulting page.
The condition in this attack lies in the fact that the columns selected from both tables must be compatible in number and type (In above example, both SQL statements returning 2 columns and both data type is a string. When trying to guess the correct number of columns, the attack may simply keep trying to use a different number of columns in each attempt until he finds the right number. To match the type, the attacker may try to try different types until he comes upon the right one or he may simply choose to use NULL instead.

Hexadecimal/Decimal/Binary Variation Attack
Attackers can inject SQL statements by using hexadecimal or decimal representations of the keywords used in SQL itself, instead of the regular strings and characters of the injection text.
The system that does not look for hexadecimal or decimal characters will be susceptible to this variation of the SQL attack.

White space manipulation attack
Web developers sometimes check suspected input, including spaces between words/characters. Attackers can take advantages of this also. For example, the SQL pattern
' or 'a' <> 'b
can be re-written as
'or'a'<>'b
Which doesn’t contains no spaces in between?
A DBMS SQL parser will be able to handle a variable around all of white space characters or keywords. In general, if we match patterns, the only first pattern will be matched and it will completely overlook the second one.
White space characters do not limit to space only but may include the tab, carriage return. To properly implement detection, the system must be able to handle whitespace characters.

Coding standards to avoid SQL Injection

Following are some coding standards, which should be followed by website/application developers to avoid SQL Injection

Store sensitive data encrypted in Database
Generally, programmers store sensitive data, such as username, password, and website settings in the database in plain format. If hackers have access to the database (even for a small duration) he can take advantage of information stored in the database. So we should encrypt sensitive data and then store into the database.

Access the database using an account with the least privileges necessary
A web-application access database after authentication of the database by web-application. To connect the database with web-application, web-application must have authentication information of the database. If web-application using admin account of the database (which can do all operation on a database), then every command sent by web-application will be executed. Such as drop table, delete a database, and even shut down the SQL server. To prevent this scenario we should use the least privileged account from web-application which can do only required functionality. For example, a web-application gets data from a database to display on the website only, in this case, the account used should be limited to read-only access to a database.

Run the application using an account with the least privileges necessary:
Similar to least privileged database access, we should give application least privileged access to web-application.

Ensure that data is valid
We should validate data before storing and fetching into the database. If we fail to do so, hackers can store malicious information or script into the database, which can be used to do malicious operations. For example, the hacker is allowed to post on an article he can store java-script block with his comments.

Use parameterized queries
Instead of creating queries by combining static SQL block and command supplied by the end user, we should create a parameterized query. Parameterized queries can prevent passing illegal command via web-page input fields.

Use stored procedures
Stored procedures give similar security given by parameterized queries.

Re-validate data in stored procedures
This is very important. We should validate data at every step. First, we should validate data on the client side, then on the server side, and finally on the database. It greatly reduces the chances of hacking our database and database information.

Ensure that error messages give nothing away about the internal architecture of the application or the database
We must stop our web-application to display real error messages caused by our application. Real error message generally contains database and/or web-application information. For example, if a hacker inputs some invalid command via web-application input fields. Then web-application can show message something like “tablename doesn’t exist columnname”. In this case, hacker knows that the database contains a table say tablename with specific column name columnname. Using this information hacker create their knowledge base and can attack the web-application.

Prevent SQL Injection attack Steps

In this work, we will protect our database from executing malicious statements inputted by the end user over a website input form. This proposed work covers the following 4 steps.

Check for restricted IP
Whenever any user submits a form over the web. We maintain IP address of submitter user. If we found that user is trying to inject malicious SQL statement, we store that users IP address. Any further request by the same IP will be rejected before considering or creating a database query. Also, we can manually enter IP address, for which web-page won’t accept user inputs.

Check for user input special characters
If user IP address is fine, we then check the user's inputted values against special characters, which is the root cause of SQL injection. For example %; statements have some special meaning in SQL queries. If user entering this special characters, we deny user to execute the query. We can configure our application to include more special characters.

Check for database special commands
Finally, we check whether the user inputting special commands being used in the SQL database? Some of the examples of special commands are sp_cmdshell (to execute SQL command), update (to update a table) and many more. This check is very critical and important, because database commands can do everything including drop databases, shut down workstation, etc. If any user issuing these commands we block that IP address for any further requests.

Create a dataset with SQL Transaction
After validating above 3 steps, if there is still some SQL injection attack, then we use a SQL transaction with DataSet. In this step we use the following technique so that even hacker bypasses above steps, this final step rollbacks their SQL injected code.

We entered into SQL transaction
Fill a dataset from given/generated query
Rollback transaction
Return dataset for the application

Sample code

Class to Prevent bad characters and bad strings
public static class BadChars
{
public static char[] badChars = { ';', ',', '"', '%' };
public static string[] badCommands = { "--", "xp_cmdshell", "Drop", "Update" };
}

Class to check blocked IP addresses
static string FilePath = HttpRuntime.AppDomainAppPath + @"App_Data\Log.xml";

public static bool IsBlockedIP(string HostAddress)
{
XElement element = XElement.Load(FilePath);
if (element != null)
{
var xml = (from member in element.Descendants("ResIPs").Descendants("IP")
where
member.Value == HostAddress
select member).SingleOrDefault();
if (xml != null)
return true;
else
return false;
}
else
return false;
}

Class to write Log entry
public class LogEntry
{
static string FilePath = HttpRuntime.AppDomainAppPath + @"App_Data\Log.xml";

public static void WriteEntry(string ip, string result, string query)
{
    XDocument doc = XDocument.Load(FilePath);
    IEnumerable<XElement> oMemberList = doc.Element("Logs").Elements("Log");
    var oMember = new XElement("Log",
                                new XElement("DateTime", DateTime.Now.ToString()),
                                new XElement("IP", ip),
                                new XElement("Result", result),
                                new XElement("Query", query));
           
    oMemberList.Last().AddAfterSelf(oMember); 
    doc.Save(FilePath);
}

public static void WriteBlockIP(string ip)
{
    if (!BlockedIPs.IsBlockedIP(ip))
    {
        XDocument doc = XDocument.Load(FilePath);
        IEnumerable<XElement> oMemberList = doc.Element("Logs").Elements("ResIPs");
        var oMember = new XElement("IP", ip);

        oMemberList.Last().Add(oMember);
        doc.Save(FilePath);
    }
}

}

XML file to maintain restricted IPs and queries executed by users
<?xml version="1.0" encoding="utf-8"?>
<Logs>
<ResIPs>
<IP>127.0.0.11</IP>
</ResIPs>
<Log>
<DateTime>4/12/2012 8:34:06 AM</DateTime>
<IP>127.0.0.1</IP>
<Result>Unsuccessfull</Result>
<Query>Select * From Employees</Query>
</Log>
<Log>
<DateTime>4/12/2012 8:35:38 AM</DateTime>
<IP>127.0.0.1</IP>
<Result>Unsuccessfull</Result>
<Query>Select * From Categories </Query>
</Log>
</Logs>

Actual method to execute SQL Inject free query
We will bind the following grid if the query is safe.
<asp:GridView ID="gvResult" runat="server">

</asp:GridView>

private void ExecuteQuery(string query)
{
gvResult.DataSource = null;
gvResult.DataBind();

//Step1: Check for Restricted IPs
bool res = BlockedIPs.IsBlockedIP(HttpContext.Current.Request.UserHostAddress);
if (res)
{
    LogEntry.WriteEntry(HttpContext.Current.Request.UserHostAddress, "Unsuccessfull", query);
    return;
}

//Step2: Check for user input special characters
if (txtCurrentQuery.Text.IndexOfAny(BadChars.badChars) != -1)
{
    LogEntry.WriteEntry(HttpContext.Current.Request.UserHostAddress, "Unsuccessfull", query);
    return;
}

//Step3: Check for database special commands
foreach (var item in BadChars.badCommands)
{
    if (txtCurrentQuery.Text.ToUpper().Contains(item.ToUpper()))
    {
        LogEntry.WriteEntry(HttpContext.Current.Request.UserHostAddress, "Unsuccessfull", query);
        LogEntry.WriteBlockIP(HttpContext.Current.Request.UserHostAddress);
        return;
    }
}

//Step4: Use DataSet technique with transaction and rollback transaction
DataSet ds = new DataSet();
IDbConnection con = null;
IDbTransaction trans = null;
IDbCommand oSelCmd = null;
IDataReader oDr;
string provider = ConfigurationManager.AppSettings["Provider"];
switch (provider)
{
    case "SQL":
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
        con.Open();
        trans = con.BeginTransaction();
        oSelCmd = new SqlCommand();
        oSelCmd.CommandText = query;
        oSelCmd.Connection = con;
        break;
    case "ACCESS":
        //Create connection, transaction, command for ACCESS
        break;
    default:
        break;
}
try
{
    oSelCmd.Transaction = trans;
    oSelCmd.CommandType = CommandType.Text;
    oDr = oSelCmd.ExecuteReader();
    gvResult.DataSource = oDr;
    gvResult.DataBind();
    oDr.Close();
    trans.Rollback();
}

catch (Exception ex)
{

}
finally
{
    if (con.State == ConnectionState.Open)
        con.Close();
}

}
References
SQL Injection on WIKI
SQL Injection Attacks and Some Tips on How to Prevent Them
OWASP SQL Injection
Types of SQL Injection Attacks
https://mstechnoguru.blogspot.com/2013/09/prevent-sql-injection-attack-in-net.html