Application Security : SQL Injection attack

This 4th and concluding part of my series : Application Security.  Continued from previous Post

SQL Injection Attack

In present web application scenarios most applications are developed using database at the back-end. All storage are taken care by database layer and presentation layer is handled by Website. All database input/output operations are performed using SQL. Hackers can take advantage of this situation. SQL injection is a code injection technique, used to Attack (computing) data-driven applications, in which nefarious SQL statements are inserted into an entry field for execution.  SQL Injection attack allow attacker to spoof identity, temper with existing data, cause massive damage and take full control of database server. SQL Injection is more common to ASP or PHP front end applications, compared to J2EE or MVC web application frameworks where sql injections issue is mitigated. Let us have one example of  SQL Injection :


select * from employee from Employee_ID='" + vEmployee + "';

The above SQL statement seems to be very normal, Employee Number is input by the user (or collected from session variable) and sql query is executed. Here hackers can take advantage by input value ‘ OR ‘1’=’1
so query becomes

select * from employee from Employee_ID='" +' OR '1'='1 + "';


This will provide hackers access to all records. This could be worse if hacker input the value as 1;DROP TABLE users

This would result in the following SQL query being run against the database server.

select * from employee from Employee_ID='1;DROP TABLE users';


Application will execute the query without any problem.  Severe damage can happen to the SQL Database and records can be manipulated easily. In order to control this kind of attack, we need to mitigate the risk.

How to Prevent SQL Injection Attacks

• Never trust user input. It may be your user  or any hacker, you can not determine.
• Avoid dynamic sql created using  user Input. Always use prepared sql, parametrized queries and stored procedure as much as possible. All programming languages provide parameter queries or something, for example

Language specific recommendations:

1. SQLite – use sqlite3_prepare() to create a statement object.
2. PHP – use PDO with strongly typed parameterized queries (using bindParam())
3. .NET – use parameterized queries like SqlCommand() or OleDbCommand() with bind variables
4. Java EE – use PreparedStatement() with bind variables

• Filter user input, against data type, strip any special characters, before passing as parameter to sql query.
• Keep your web framework updated with all latest patches.
• Strict control on user permissions, never use one account for every task, always use role based permissions.
• Consult DBA to remove unwanted database tools available to application user role. For example xp_cmdshell etc.
• Encrypt your inter webpages communication.
• Avoid divulging more information in error message.
• Always use encrypted cookies.

Example of Safe Code to mitigate SQL Injection (C#)

 


String query =
"SELECT * FROM user_data WHERE user_name = ?";
try {
OleDbCommand command = new OleDbCommand(query, connection);
command.Parameters.Add(new OleDbParameter("customerName", CustomerName Name.Text));
OleDbDataReader reader = command.ExecuteReader();
// …
} catch (OleDbException se) {
// error handling
}


Safe use of Stored Procedure (VB.net)


Try
Dim command As SqlCommand = new SqlCommand("sp_getAccountBalance", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(new SqlParameter("@CustomerName", CustomerName.Text))
Dim reader As SqlDataReader = command.ExecuteReader()
‘ …
Catch se As SqlException
‘ error handling
End Try


I hope I was able to explain SQL Injection easily to you. This was concluding part of my Application Security Series. You can read entire series here

Application Security

1. Threats and Risks
2. Top Threats
3. Focus Area
4. SQL Injection Attack

in case you need any further information, please feel free to comment , I shall try to reply the comment at the earliest. If you like this blog, please like my Facebook page and advise me for more topics of your interest. Happy Programming!!!

I hope you would find above article informative and  interesting. In case you need any further information, please feel free to comment , I shall try to reply the comment at the earliest. If you like this article, please like my Facebook page and advise/suggest me for more topics of your interest. Happy Coding!