SQL injection attack and prevention using stored procedure

What is SQL Injection Attack?

SQL Injection is one of the many web attack mechanisms (hacking technique) used by hackers to steal data from organizations. It is perhaps one of the most common application layer attack techniques.

Improper coding of your web applications that allows hacker to inject SQL commands into say a login form to allow them to gain access to the data held within your database.

How does SQL Database get attacked or Main Reason for SQL Injection Attack?

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

In general, the inline query written in the project or application is the main cause of the SQL Injection Attack.

Ex: select count (*) from [user] where usn = ’”+ txtUserName.Text +”’ and

      pwd = ’”+ txtUserPassword.Text +”’

The above mentioned query is to check (verify credentials) whether username and password matches what user entered? Generally,     used in login forms.

There are number of SQL injection techniques available and they differ from attacker to attacker; however, the functionality or malfunctioning they exploit is the same. They find out the vulnerability in SQL queries using the web URL or the error messages               generated.

Often developers use dynamic SQL statements made up of strings that are concatenated or query parameters directly specified along with input keywords.

Example

1.    Select * from MyLoginAccounts where loginname='xyz' and  loginID='123' and permission='admin'

In the above mentioned query, he/she tries to manipulate the query using sql commands this leads to SQL Injection Attack.

2.    Select * from MyLoginAccounts where loginname='arpit' or '1'='1' -- and loginID='123'

       and  permission='admin'

By passing one more parameter such as "or '1'='1'" which is always true, the user tries to capture all the records from the system. Also, to restrict the other condition to be executed from the system, attacker uses '--' to make the keywords following it look like a comment statement.

By this, attacker can login and access the confidential information from other user’s right.

Types of attacks

1.   First Order Attack :

In this attack, he/she add a sub query or a union statement to the existing SQL query to buy information illegally. Here it executes only the intended part of the query.

2.   Second Order Attack :

Here, the attacker tries to get control of persistent storage systems i.e., Attacker tries to create or delete the information, tables and even login accounts. Which can further be used to carry out dangerous attacks like retrieving the database schema.

How to avoid SQL Injection attacks

• Developers should expose a database only via a API. And user privileges should be carefully made so that the client has no direct access to tables and views.

• Execute privileges should be granted only to users who are authorized to perform DDL and DML operations.

• Appropriately choose the privileges or rights such as AUTHID CURRENT_USER and AUTHID DEFINER.

• Limit user inputs, like restrict users to specified web pages using the restricted language for input, not specifying VARCHAR        parameter when the parameter will be used as a number, and using int instead of number if you need only positive integers.

• Developers should use SQL statement text which are compile-time-fixed.

• All the input values should be validated before putting them under code to perform database transactions.

• Use of Stored Procedures (in right way) reduces risk of SQL Injection Attack.   

How to avoid SQL Injection attacks using Stored Procedures

Some database programmers believe that by using stored procedures, their code are safe from SQL injection Attacks.

That is not true because, if dynamic query is used inside the stored procedures and the dynamic query is constructed by concatenating the parameters it is at high risk of attack.

The easiest way to prevent SQL injection from happening, is to use parameters and sp_executesql to execute the dynamically generated statement.

Example

1.   Create Procedure Usp_GetCountry

@Name Varchar(50)

AS

Begin

Select * from dbo.TblCountry where Name like ‘%’+@Name+’%’

End

If @Name contains any malicious string then the attacker can gain the access. For example query, look below. If input parameter from C# is

2.   India’; EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" --

The above mentioned statement deletes all the tables present in the DataBase. After the “--“ (double hyphen) the sql treats all the written query as comments.

Correct method is, as mentioned before, is to use parameters and sp_executesql. The second argument of sp_executesql should be set to the name and type of the parameters to expect in string form.

Format is shown below

1.   Create Procedure Usp_GetCountry

@Name Varchar(50)

AS

Begin

DECLARE @sqlcmd NVARCHAR(MAX);

      DECLARE @params NVARCHAR(MAX);

      SET @sqlcmd = N'SELECT * FROM dbo.TblCountry WHERE Name = @Name';

      SET @params = N'@Name NVARCHAR(50)';

      EXECUTE sp_executesql @sqlcmd, @params, @Name;

      End

There are other methods, few are mentioned below which can be used to prevent SQL Injection Attack

• Using parameterized query.
• Using ORM tools (LINQ, LINQ to Entities)
• Using regular expression to discard input string.
• Encrypt sensitive data.