OWASP # 1 : SQL Injection Attack

Author
Recent Posts

Atul Sharma

An extrovert by nature, Atul has been sharing his opinions and views on various social network platforms. He always had been thinking about having his own platform and here it is. Creative and tech-savvy Atul has a good sense of humor and when he is not programming or writing blogs or traveling, you can find him cooking, Netflixing or sleeping.

What is Secure Programming?

As covered in Introduction that Secure coding is different from defensive coding in 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 application from hacking, threats and security vulnerabilities while Defensive coding is to handle unexpected conditions, those are eventually not for destroying or taking control of the system.

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, industry standard is to secure your application from top 10 threats recommended by OWASP. Here also we will be focussing majorly on them.

OWASP Threat # 1 – Injection Attacks

In this part, we will be covering 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, 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

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

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

Atul'; DROP TABLE USERS

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

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

Blind SQL Injection Attack

Definition

This is another type pf SQL Injection attack where database information are retrieved based on the true or false condition and behavior of 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 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 false condition. Hacker will check the page behavior if it is loading in true and false condition.

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

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

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

Threats –

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

Prevention –

1. 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. Sometimes, we can also restrict user NOT to use some special characters e.g. %, ;, = etc.

2. Use Parametrized Query

In stead of using

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

We should use this code

string commandText = "SELECT * FROM USERS "+
    "WHERE USERNAME=@UserName";
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Parameters.Add("@UserName",UserName);

3. Use of Stored Procedure

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

Important Consideration in Stored Procedure 

Implement to check the existence of data in where clause, before executing the whole query.
Strict the return from Stored procedure.

CREATE PROCEDURE AKS.ValidateUser
    	@userName varchar2(50),
	@Password varchar2(100),
	@IsValidated bit OUTPUT
AS
IF EXISTS(SELECT * FROM AKS.USERS
        WHERE USERNAME = @UserName) --Table Name
    BEGIN
        // Perform the operation
	// Get the user name and validate the credentials
	SET @IsValidated = 1
    END
ELSE
   BEGIN
	SET @IsValidated = 0 
	// Can show error message to user from other layers
   END
GO

Explanation –

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

4. Restricted privilege to database objects

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

5. Use of ORM for data access

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

6. Custom Error message

Don’t provide system related information in exception rather make it user friendly error message and steps to do what.

7. 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.

8. Blocking Malicious Requests at 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 something like this –

<filteringRules> 
    <filteringRule name="SQLInjection" scanQueryString="true"> 
        <appliesTo> 
            <add fileExtension=".asp" /> 
            <add fileExtension=".aspx" /> 
        </appliesTo> 
        <denyStrings> 
            <add string="--" /> 
            <add string=";" /> 
            <add string="/*" /> 
            <add string="@" /> 
            <add string="char" /> 
            <add string="alter" /> 
            <add string="begin" /> 
            <add string="cast" /> 
            <add string="create" /> 
            <add string="cursor" /> 
            <add string="declare" /> 
            <add string="delete" /> 
            <add string="drop" /> 
            <add string="end" /> 
            <add string="exec" /> 
            <add string="fetch" /> 
            <add string="insert" /> 
            <add string="kill" /> 
            <add string="open" /> 
            <add string="select" /> 
            <add string="sys" /> 
            <add string="table" /> 
            <add string="update" /> 
        </denyStrings> 
    </filteringRule> 
</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 the similar changes in web.config and it should work.

When to take care of this attack –

When querying to Relation database using queries.
A must have for publicly opened websites and applications.
All types of relational databases are vulnerable for this attack.
In house and Win Forms applications already have restricted access and user level logs, but still good to have feature.

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

SOUND – S for Secure Programming #1 | OWASP #1 | Injection Attack

What is Secure Programming?

OWASP Threat # 1 – Injection Attacks

SQL Injection Attack (SQLi Attack)

Definition

How does it work?

Blind SQL Injection Attack

Definition

How does it work?

Threats –

Prevention –

1. Validation of user input

2. Use Parametrized Query

3. Use of Stored Procedure

4. Restricted privilege to database objects

5. Use of ORM for data access

6. Custom Error message

7. Insider Alert

8. Blocking Malicious Requests at IIS level

When to take care of this attack –

You may like these too...

What is Secure Programming?

OWASP Threat # 1 – Injection Attacks

SQL Injection Attack (SQLi Attack)

Definition

How does it work?

Blind SQL Injection Attack

Definition

How does it work?

Threats –

Prevention –

1. Validation of user input

2. Use Parametrized Query

3. Use of Stored Procedure

4. Restricted privilege to database objects

5. Use of ORM for data access

6. Custom Error message

7. Insider Alert

8. Blocking Malicious Requests at IIS level

When to take care of this attack –

Share this:

You may like these too...