SQL injection attack is a threat to even the most secured database in the world. Even the most secure database of CIA, Citi bank, MI6 were subjected to SQL attack. This article will guide through the steps to prevent your databases from basic SQL injection attacks.
- Read: More Interview Questions
Input Sanitization
The basic step to prevent the database from SQL attack is sanitizing the user input. Let us consider an e-mail as the input provided by the user. The process of omitting the character other than the ones used for email-id is known as input sanitization. The characters other than the one shown below can be safely sanitized from user inputs.
”
abcdefghijklmnopqrstuvwxyz
ABCDEFGHIJKLMNOPQRSTUVWXYZ
0123456789
! $ & * - = ^ ` | ~ # % ‘ + / ? _ { } @ .
”
Escaping Strings
Sanitizing the input is now a dormant method and hackers are now using advanced tricks like escaping strings. Escaping strings can be defined as the process of using characters like quotes, numerical signs and slashes in the form of strings. These strings are not misinterpreted by database security because they are also part of programming context. For example: if you use a string like “Go//od Mor’’’//ning” in a php server attack; it will be displayed as Good Morning. If the above string is used without characters then it will result in PHP error. The escaping strings can be used along with the email id to thrown an error. The error will eventually reveal the loopholes in database security.
Including the escaping strings in the database security will help you to prevent the SQL injection attack. Hackers are smart and they will try to go around the database security by using the strings in various combination. It is literally impossible to include all escaping strings in your database.
Let us consider that you want to retrieve the name “O’Connell” from a database. The SQL query to retrieve the name will resemble like this
SELECT *
FROM customers
WHERE name = ‘O”Connell’; — this works great
The above code will work fine because the SQL will identify the double quotes in the name as two single quotes and allow the hacker to search through the database. Now the hacker fools the system by interpreting a double quote as two single quotes. Escaping quotes will allow the user to close one statement and start running another according to his/her choice.
The hacker takes advantage of following system vulnerabilities
- The application developer is trying to escape quotes himself by just appending an extra quote.
- MySQL supports escape mechanisms other than just appending a quote. In this case, the hacker also used the backslash escape mechanism to run his malicious code.
We have already seen that preventing escaping string manually is hectic process so many languages provide database library. These database libraries have in built functions to prevent escaping strings. Both parsing and quote safing of strings are possible with the help of database libraries.
SQL injection attack can be prevented by following these simple processes
Data Sanitization
Databases must filter all inputs from the user and the filtered inputs should be checked for malicious contexts. Example: for email addresses only the characters allowed in email id are permitted. Likewise only numbers from 0 to 9 are allowed for phone numbers; a character used as a phone number will be filtered by the data sanitization process.
Web Firewall Applications
This most popular form of security against SQL injection attacks. Web based firewall applications are available for free in the internet. However if you want some extra security you can opt for paid version of the application. Applications like Apache, Microsoft IIs, Mod security and nginx web servers are the most popular web based firewall applications used to prevent SQL injection.
- Recommended: Final year Academic projects download
Limiting Privileges in Database
Creating multiple user accounts and limiting the privilege of user environment is one of the best ways to prevent SQL injection attack. For example: If you are using Gmail account; your account can be accessed by providing username and password. In the webpage you will be first asked to provide username and a separate page will opened for providing password. The password will be opened only if you provide correct username otherwise an error message will be thrown only username. This process will prevent the hackers from exploiting the loopholes present in the database.