Post
Ask Question

All About OWASP #1 - SQL Injection Attack

In the introduction of the SOUND Programming Methodology, for effective programming and code review technique, we covered a high-level overview of each mandatory type of programming. Henceforth, I will be covering them in detail with applicable code examples (from C#).

What is Secure Programming?

As covered in the introduction, secure coding is different from defensive coding in the SOUND paradigm. This refresher is to clear any doubts as I have seen many developers are using these two interchangeably.

Secure Coding is to secure applications from hacking, threats and security vulnerabilities while defensive coding is to handle unexpected conditions.

Since Security is the most important facet of any application as any failure can lead to unavailability or denial of service, and also comes very first in SOUND Programming Methodology, hence we will be extensively discussing Security.

As part of Secure Coding, the industry standard is to secure your application from the top 10 threats recommended by OWASP. Here also, we will be mainly focusing on them.

OWASP Threat # 1 - Injection Attacks

In this part, we will be covering the SQL Injection attack from OWASP top 10 list. In the following articles, we will be covering other types of injection attacks and will move ahead to cover all 10 threats.

SQL Injection Attack (SQLi Attack)

This is the first security threat as the first chapter of security in programming.

Definition

In this type of attack, the attacker injects the command to read or modify unintended data or can compromise the actual SQL command.

How does it work?

In this, with the actual query, some additional query is also sent.

For example – There is one query for authentication. With my user name, id fetches some more information and gives me entry in to the application

  1. SELECT user_id, username, password_hash FROM USERS WHERE username = 'Atul'  

Now, someone sent this instead of just sending the user name.

  1. Atul'; DROP TABLE USERS  

Consequently, the USERS table will be dropped and it will have a catastrophic effect on the system.

This is just an example and hackers can use any query to harm the system and it is not limited to the user validation. It can happen at any stage, for any query. There is a general misconception that SQL Injection can happen only during the authentication process.

Blind SQL Injection Attack

Definition

This is another type of SQL Injection attack where database information is retrieved based on the true or false condition and behavior of the page accordingly.

How does it work?

I have one website www.mywebsite.com.

Now to fetch any page, it uses this www.mywebsite.com?id=’Atul’

Now the hacker will change this to www.mywebsite.com?id=’Atul’ and 1=1 i.e. true condition and then www.mywebsite.com?id=’Atul’ and 1=2 which evaluates to a false condition. Hacker will check the page behavior if it is loading in true and false conditions.

MySQL, MS SQL Server, and Oracle use different functions for datetime - now(), getdate(), and sysdate() respectively and a hacker can use this to get the information of database used in the system.

Using this approach, the attacker can get all the information about the database to pose all the threats possible from SQL Injection attack.

This video gives the live demonstration of a Blind SQL Injection attack and in just 7 minutes, you will get a fair idea about this attack.

Threats

  • Spoofing of identity and Authentication Bypass 
  • Data loss
  • Data Theft
  • Denial of Service
  • Whole database system compromise

Prevention

 
Validation of user input

In general, it says never trust any user input. Proper validation of user input on client and server side both with regular expression or similar techniques are necessary. Sometimes, we can also restrict users NOT to use some special characters e.g. %, ;, = etc.

Use Parametrized Query

In stead of using

  1. string cmdText=string.Format("SELECT * FROM USERS "+  
  2.     "WHERE USERNAME='{0}'", UserName);  
  3. SqlCommand cmd = new SqlCommand(cmdText, conn);  

We should use this code,

  1. string commandText = "SELECT * FROM USERS "+  
  2.     "WHERE USERNAME=@UserName";  
  3. SqlCommand cmd = new SqlCommand(commandText, conn);  
  4. cmd.Parameters.Add("@UserName",UserName);   
Use of Stored Procedure

It is always recommended to use stored procedure as it provides an additional layer of abstraction to data access.

Important Considerations in Stored Procedure

  1. Implement to check the existence of data in where clause, before executing the whole query.
  2. Be strict with the return from Stored procedure.
  1. CREATE PROCEDURE AKS.ValidateUser  
  2.         @userName varchar2(50),  
  3.     @Password varchar2(100),  
  4.     @IsValidated bit OUTPUT  
  5. AS  
  6. IF EXISTS(SELECT * FROM AKS.USERS  
  7.         WHERE USERNAME = @UserName) --Table Name  
  8.     BEGIN  
  9.         // Perform the operation  
  10.     // Get the user name and validate the credentials  
  11.     SET @IsValidated = 1  
  12.     END  
  13. ELSE  
  14.    BEGIN  
  15.     SET @IsValidated = 0   
  16.     // Can show error message to user from other layers  
  17.    END  
  18. GO  

Explanation

  1. As you can see, every time it will search the provided username in the database; if not found, it won’t do anything. So, any hacking attempt will get caught here.
  2. It will return bit as an output parameter of the Stored procedure and hence doesn’t expose any sensitive information to an attacker.
Restricted privilege to database objects

It is also advisable to create a different schema in the database and provide the least privilege to the user impersonating db access from the application. In the above example, I have used aks custom schema.

Use of ORM for data access

Using ORM for data access can help here as it uses parametrized query.

Custom Error message

Don’t provide system related information in exception; rather make it a user-friendly error message and with further steps

Insider Alert

As we see here, SQLi is only possible when we know the name of DB Objects. In most of the cases, that is done by internal developers or showing up exception information in the message. This is very difficult to handle but the above steps can help here as well. Additionally, it is recommended NOT to use friendly and memorable names for DB objects.

Blocking Malicious Requests at the IIS level

We can write custom filters in IIS 7 or above to filter out the suspicious HTTPRequests. We need to change the applicationhost.config file to something like this,

  1. <filteringRules>   
  2.     <filteringRule name="SQLInjection" scanQueryString="true">   
  3.         <appliesTo>   
  4.             <add fileExtension=".asp" />   
  5.             <add fileExtension=".aspx" />   
  6.         </appliesTo>   
  7.         <denyStrings>   
  8.             <add string="--" />   
  9.             <add string=";" />   
  10.             <add string="/*" />   
  11.             <add string="@" />   
  12.             <add string="char" />   
  13.             <add string="alter" />   
  14.             <add string="begin" />   
  15.             <add string="cast" />   
  16.             <add string="create" />   
  17.             <add string="cursor" />   
  18.             <add string="declare" />   
  19.             <add string="delete" />   
  20.             <add string="drop" />   
  21.             <add string="end" />   
  22.             <add string="exec" />   
  23.             <add string="fetch" />   
  24.             <add string="insert" />   
  25.             <add string="kill" />   
  26.             <add string="open" />   
  27.             <add string="select" />   
  28.             <add string="sys" />   
  29.             <add string="table" />   
  30.             <add string="update" />   
  31.         </denyStrings>   
  32.     </filteringRule>   
  33. </filteringRules>  

For any error, It will return 404 and we can redirect user to error page accordingly.

These restrictions will be applicable to all websites hosted on the server. If it is not required for all we can make similar changes in web.config and it should work.

When to take care of this attack,

  1. When querying to a relational database using queries.
  2. A must-have for publicly opened websites and applications.
  3. All types of relational databases are vulnerable to this attack.
  4. In house and Win Forms applications already have restricted access and user level logs, but it's still good to have the feature.

In the next part, we will be discussing other types of Injection Attacks.


Similar Articles