SecurePHPWebAppCoding - SQL Injection

[Nytro]

SecurePHPWebAppCoding - SQL Injection - what is it and how to stop it?

Abani Kumar Meher, 14 Sep 2014

Introduction

In this article I have tried to cover some basic info about SQL injection, how we write code while developing a web application which results in SQL injection vulnerability, how attacker uses this flaw to gain unauthorized access and how can we change code little bit to overcome our mistakes and prevent attackers from using SQL injection in web application which makes our application more secure. This articles uses PHP and MySQL to show example but other languages have also similar function to prevent SQL injection. So lets see what SQL injection is.

What is SQL Injection?

SQL injection is a type of web application vulnerability using which an attacker can manipulate and submit a SQL command to retrieve unauthorized information from database. This type of attack mostly occurs when a web application executes data provided by user without validating or escaping it. SQL injection can give access to sensitive information such as financial data, credit card information or users personal information to the attacker and allows the attacker to manipulate data stored in database. It is not a database or web application server issue but it is a web application programming issue and most of the developers are not aware of this.

What can an attacker achieve using SQL injection?

Based on the application and how user data is handled by application, SQL injection attack is used for following. There are other scenario also.

Unauthorized login:- Attacker can use SQL injection to get unauthorized access to users account and perform any action they want on that account.

Privileges escalation:- A user with less privilege can use sql injection to login to an account with more privileges than his account and add more privileges to his account so that attacker can access more data/features of that application.

Tamper with database data:- Attacker can update database data to change other profile details, change password which will result in problem for the other user.

Dumping database:- Attacker can use SQL injection to dump all data from database and expose it with sensitive information like logins, credit card information etc of users.

Deletion/destruction of data:- SQL injection can be used to delete data from database making website loose all records of user and all their details.

Read files of web server:- Attacker can use SQL injection to load file present in web server and read the application code, configuration files etc.

Damage company's reputation:- SQL injection can be used to dump all data and can be made it available publicly. No user likes their personal/sensitive data leaked.

How can we prevent SQL injection?

• Never believe in user input and client side validation. Always validate user input on server end for specific data type or convert data to specific data type before using it in query.
  
• For string data, escape single quotes and double quotes or convert string to html entities(this will increase length of string, so depending upon the field type/length, use it).
  
• Try to avoid creating query using string concatenation. It is one of the main reason which makes a web application vulnerable to SQL injection but most of the developers use this approach to generate query because they find it easy without thinking or knowing about the mistake they are making.
  
• Use prepared statement and parameter binding.
  
• Whenever possible replace potentially dangerous characters for database from user input data.
  

[TABLE=width: 500]

[TR]

[TD=align: center]Special Database Characters[/TD]

[TD=align: center]Function in database[/TD]

[/TR]

[TR]

[TD=align: center];[/TD]

[TD=align: center]Query Delimiter[/TD]

[/TR]

[TR]

[TD=align: center]'[/TD]

[TD=align: center]Character data string delimiter[/TD]

[/TR]

[TR]

[TD=align: center]--[/TD]

[TD=align: center]Single line comment[/TD]

[/TR]

[TR]

[TD=align: center]/* */[/TD]

[TD=align: center]Multiline comment[/TD]

[/TR]

[TR]

[TD=align: center][/TD]

[TD][/TD]

[/TR]

[/TABLE]

NOTE: Special database characters may vary from database to database.

• Use account with less permissions for web application to execute query.
  

Now lets do some real work. Lets see how we write code which allows hacker to use SQL injection in website and with that we will see how can we write few more lines of code with that code to prevent SQL injection in website. We will see it using PHP but the same thing can be done to/using application written in other programming language. So lets begin. Lets see the classic example first which everyone says when you ask about SQL injection.

Articol: SecurePHPWebAppCoding - SQL Injection - what is it and how to stop it? - CodeProject