SQL injection (SQLi) refers to an injection attack wherein an attacker can execute malicious SQL statements (also commonly referred to as a malicious payload) that control a web application’s database server (also commonly referred to as a Relational Database Management System – RDBMS). Since an SQL injection vulnerability could possibly affect any website or web application that makes use of an SQL-based database, the vulnerability is one of the oldest, most prevalent and most dangerous of web application vulnerabilities.

By leveraging an SQL injection vulnerability, given the right circumstances, an attacker can use it to bypass a web application’s authentication and authorization mechanisms and retrieve the contents of an entire database. SQL injection can also be used to add, modify and delete records in a database, affecting data integrity.

To such an extent, SQL injection can provide an attacker with unauthorised access to sensitive data including, customer data, personally identifiable information (PII), trade secrets, intellectual property and other sensitive information.

How SQL Injection works

SQL injection occurs when information submitted by a browser to a web application is inserted into a database query without being properly checked.

An example of this is an HTML form that receives posted data from the user and passes it to an Active Server Pages (ASP) script running on Microsoft's IIS web server. The two data items passed are a username and password, and they are checked by querying a SQL Server database. The schema of the user's table in the backend database is as follows:

[username] [varchar](255)
[password] [varchar](255)

The query executed is –

SELECT * FROM dbo.[users] 
	WHERE [username] = '<username>' AND [password] ='<password>';

However, the ASP script builds the query from user data using the following line:

var query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

If the username is a single-quote character (') the effective query becomes

SELECT * FROM users WHERE username = ''' AND password = '[password]'

This is invalid SQL syntax and produces a SQL Server error message in the user's browser:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'password'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ';

The quotation mark provided by the user has closed the first one, and the second generates an error because it is unclosed. 

The attacker can now begin to inject strings into the query in order to customise its behaviour; for example, if you want to log in as the user without knowing username and password, pass the username and put ' OR 1=1 -- as a password.  And this will convert to a query like –

SELECT * FROM [users] WHERE [username] = '' OR 1=1 --' AND password = '[password]'

The double hyphens (--) signify a Transact-SQL comment, so all subsequent text is ignored. Because one will always equal one, this query will return the entire users table, the ASP script will accept the logon because results were returned, and the client will be authenticated as the first user in the table.

If a specific username is known the account can be accessed with the username. Pass ' OR username='<username>' -- as username. Query will become like –

SELECT * FROM [users] WHERE [username] = '' OR username='<username>' -- AND [password] ='<password>'

SQL Injection Prevention

To prevent SQLi we should take care of following things –

1. 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 (var connection = new SqlConnection(connectionString))
   {
   	var dataAdapter = new SqlDataAdapter(
   		"LoginStoredProcedure", connection)
   	{
   		SelectCommand = {CommandType = CommandType.StoredProcedure}
   	};
   	dataAdapter.SelectCommand.Parameters.Add("@Username", SqlDbType.VarChar, 50);
   	dataAdapter.SelectCommand.Parameters["@Username"].Value = username;
   	var dataset = new DataSet();
   	dataAdapter.Fill(dataset);
   }

   In this case, the @Username 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 50 characters. If the data does not conform to the type or length defined by the parameter, the SqlParameter class throws an exception.
   
   
2. 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 SqlParameterCollection with dynamic SQL.

   using (var connection = new SqlConnection(connectionString))
   {
   	var dataAdapter = new SqlDataAdapter( "SELECT * FROM [Users] WHERE [Username] = @Username",connection);
   	dataAdapter.SelectCommand.Parameters.Add("@Username", SqlDbType.VarChar, 50);
   	dataAdapter.SelectCommand.Parameters["@Username"].Value = username;
   	var dataSet = new DataSet();
   	dataAdapter.Fill(dataSet);
   }

3. Additional considerations: Other things to consider when you develop countermeasures to prevent SQL injection include.
       a. Use escape routines to handle special input characters.
       b. Use a least-privileged database account.
       c. Avoid disclosing error information.