Prevent SQL injection
SQL Injection Examples and ways to prevent SQL Injection Attacks on Web Applications
While testing a website or a system, the tester’s aim is to ensure if the tested product is as much protected, as possible.
Security Testing is usually performed for this purpose. In order to perform this type of testing, initially, we need to consider, which attacks are most likely to happen. SQL Injection is one of those attacks.
SQL Injection is considered as one of the most common attacks as it can bring serious and harmful consequences to your system and sensitive data.
What You Will Learn:
What Is SQL Injection?
Some of the user inputs might be used in framing SQL Statements that are then executed by the application on the database. It is possible for an application NOT to handle the inputs given by the user properly.
If this is the case, a malicious user could provide unexpected inputs to the application that are then used to frame and execute SQL statements on the database. This is called SQL Injection. The consequences of such an action could be alarming.
As the name itself implies, the purpose of the SQL Injection attack is to inject the malicious SQL code.
Each and every field of a website is like a gate to the database. In the login form, the user enters the login data, in the search field the user enters a search text, in the data saving form the user enters data to be saved. All these indicated data goes to the database.
Instead of correct data, if any malicious code is entered, then there are possibilities for some serious damage to happen to the database and the whole system.
SQL Injection is performed with the SQL programming language. SQL (Structured Query Language) is used for managing the data held in the database. Therefore during this attack, this programming language code is being used as a malicious injection.
This is one of the most popular attacks, as databases are used for almost all the technologies.
Many applications use some type of Database. An application under test might have a user interface that accepts user input that is used to perform the following tasks:
#1) Show the relevant stored data to the user e.g. the application checks the credentials of the user using the login information entered by the user and exposes only the relevant functionality and data to the user
#2) Save the data entered by the user to the database e.g. once the user fills up a form and submits it, the application proceeds to save the data to the database; this data is then made available to the user in the same session as well as in subsequent sessions
Risks Of SQL Injection
Nowadays, a database is being used for almost all the systems and websites, as data should be stored somewhere.
As sensitive data is being stored in the database, there are more risks involved in the system’s security. If any personal website or blog’s data would be stolen, then there won’t be much damage when compared to the data that would be stolen from the banking system.
The main purpose of this attack is to hack the system’s database, therefore this attack’s consequences can really be harmful.
The following things might result from SQL Injection
- Hacking other person’s account.
- Stealing and copying website’s or system’s sensitive data.
- Changing the system’s sensitive data.
- Deleting system’s sensitive data.
- The user could log in to the application as another user, even as an administrator.
- The user could view private information belonging to other users e.g. details of other users’ profiles, transaction details, etc.
- The user could change application configuration information and the data of the other users.
- The user could modify the structure of the database; even delete tables in the application database.
- The user could take control of the database server and execute commands on it at will.
The above-listed risks can really be considered serious, as restoring a database or its data can cost a lot. It can cost your company a reputation and money to restore the lost data and system. Therefore it is highly recommended to protect your system against this type of attack and consider Security Testing as a good investment in your product’s and company’s reputation.
As a tester, I would like to comment, that testing against possible attacks is a good practice even if Security Testing was not planned. This way you can protect and test the product against unexpected cases and malicious users.
The Essence Of This Attack
As mentioned earlier, the essence of this attack is to hack the database with malicious purpose.
In order to perform this Security Testing, initially, you need to find the vulnerable system parts and then send malicious SQL code through them to the database. If this attack is possible for a system, then appropriate malicious SQL code will be sent and harmful actions may be performed in the database.
Each and every field of a website is like a gate to the database. Any data or input that we usually enter in any field of the system or website goes to the database query. Therefore, instead of correct data, if we would type any malicious code, then it may be executed in the database query and bring harmful consequences.
For performing this attack, we have to change the act and purpose of an appropriate database query. One of the possible methods to perform it is to make the query always true and after that insert your malicious code. Changing the database query to always true can be performed with simple code like ‘ or 1=1;–.
Testers should keep in mind, that while checking if changing the query to always true can be performed or not, different quotes should be tried – single and double. Therefore, if we have tried code like ‘ or 1=1;–, we should also try the code with double quotes “ or 1=1;–.
For Example, let’s consider that we have a query, that is searching for the entered word in the database table:
select * from notes nt where nt.subject = ‘search_word‘;
Therefore instead of the search word, if we enter a SQL Injection query ‘ or 1=1;–, then the query will become always true.
select * from notes nt where nt.subject = ‘ ‘ or 1=1;–
In this case, the parameter “subject“ is closed with the quote and then we have code or 1=1, which makes a query always true. With the sign “–“ we comment on the rest of the query code, which will not be executed. It is one of the most popular and easiest ways to start controlling the query.
Few other codes may also be used to make the query always true, like:
- ‘ or ‘abc‘=‘abc‘;–
- ‘ or ‘ ‘=‘ ‘;–
The most important part here is that after comma sign we can enter any malicious code, that we would like to be executed.
For Example, it may be ‘ or 1=1; drop table notes; —
If this injection is possible, then any other malicious code may be written. In this case, it will only depend on the malicious user’s knowledge and intention. How to Check SQL Injection?
Checking for this vulnerability can be performed very easily. Sometimes it is enough to type ‘ or “ sign in the tested fields. If it returns any unexpected or extraordinary message, then we can be sure, that SQL Injection is possible for that field.
For Example, if you get an error message like ‘Internal Server Error‘ as a search result, then we can be sure, that this attack is possible in that part of the system.
Other results, that can notify possible attack include:
- Blank page loaded.
- No error or success messages – functionality and page do not react to the input.
- Success message for malicious code.
Let’s look around at how this works in practice.
For Example, Let’s test if an appropriate login window is vulnerable for SQL Injection. For this purpose, in the email address or password field, we just type sign as shown below.
If such input returns result like error message ‘Internal Server Error‘ or any other listed inappropriate result, then we can almost be sure, that this attack is possible for that field.
A very tricky SQL Injection code may also be tried. I would like to mention, that in my career I have not encountered any case when there was ‘Internal Server Error’ message as a result of the sign, but at times the fields did not react for more complicated SQL code.
Therefore checking for SQL Injection with a single quote ‘ is quite a trustworthy way to check if this attack is possible or not.
If the single quote does not return any inappropriate result, then we can try to enter double quotes and check the results.
Also, SQL code for changing the query to always true can be considered as a way to check if this attack is possible or not. It closes the parameter and changes the query to ‘true‘. Therefore if not being validated, such input can also return any unexpected result and inform the same, that this attack is possible in this case.
Checking for possible SQL attacks can also be performed from the website‘s link. Suppose we have a website‘s link as http://www.testing.com/books=1. In this case ‘books‘ is a parameter and ‘1‘ is its value. If in the provided link we would write ‘ sign instead of 1, then we would check for possible Injection.
Therefore link http://www.testing.com/books= will be like a test if the SQL attack is possible for the website http://www.testing.com or not.
In this case, if link http://www.testing.com/books= returns an error message like ‘Internal Server Error‘ or a blank page or any other unexpected error message, then also we can be sure, that SQL Injection is possible for that website. Later, we can try to send more tricky SQL code through the website‘s link.
To check if this attack is possible through the website‘s link or not, code like ‘ or 1=1;– can also be sent.
As an experienced software tester, I would like to remind, that not only the unexpected error message can be considered as a SQL Injection vulnerability. Many testers check for possible attacks only in accordance with error messages.
However, it should be remembered, that no validation error message or success message for malicious code can also be a sign, that this attack is possible.
Security Testing Of Web Applications Against SQL Injection
Security testing of web applications explained with simple examples:
Since the consequences of allowing this vulnerability technique could be severe, it follows that this attack should be tested during the security testing of an application. Now with an overview of this technique, let us understand a few practical examples of SQL injection.
Important: This SQL Injection Test should be tested only in the test environment.
If the application has a login page, it is possible that the application uses dynamic SQL such as the statement below. This statement is expected to return at least a single row with the user details from the Users table as the result set when there is a row with the username and password entered in the SQL statement.
SELECT * FROM Users WHERE User_Name = ‘” & strUserName & “‘ AND Password = ‘” & strPassword & “’;”
If the tester would enter John as the strUserName (in the textbox for username) and Smith as strPassword (in the textbox for password), the above SQL statement would become:
SELECT * FROM Users WHERE User_Name = 'John' AND Password = 'Smith’;
If the tester would enter John’– as strUserName and no strPassword, the SQL statement would become:
SELECT * FROM Users WHERE User_Name = 'John'-- AND Password = 'Smith’;
Note that the part of the SQL statement after John is turned into a comment. If there were any user with the username of John in the Users table, the application could allow the tester to log in as the user John. The tester could now view the private information of the user John.
What if the tester does not know the name of any existing user of the application? In such a case, the tester could try common usernames like admin, administrator, and sysadmin. If none of these users exists in the database, the tester could enter John’ or ‘x’=’x as strUserName and Smith’ or ‘x’=’x as strPassword. This would cause the SQL statement to become like the one below.
SELECT * FROM Users WHERE User_Name = 'John' or 'x'='x' AND Password = 'Smith’ or ‘x’=’x’;
Since ‘x’=’x’ condition is always true, the result set would consist of all the rows in the Users table. The application could allow the tester to log in as the first user in the Users table.
Important: The tester should request the database administrator or the developer to copy the table in question before attempting the following attacks.
If the tester would enter John’; DROP table users_details;’—as strUserName and anything as strPassword, the SQL statement would become like the one below.
SELECT * FROM Users WHERE User_Name = ‘John’; DROP table users_details;’ –‘ AND Password = 'Smith';
This statement could cause the table “users_details” to be permanently deleted from the database.
Though the above examples deal with using the SQL injection technique only the login page, the tester should test this technique on all the pages of the application that accept user input in textual format e.g. search pages, feedback pages, etc.
SQL injection might be possible in applications that use SSL. Even a firewall might not be able to protect the application against this technique.
I have tried to explain this attack technique in a simple form. I would like to re-iterate this attack should be tested only in a test environment and not in the development environment, production environment or any other environment.
Instead of manually testing whether the application is vulnerable to SQL attack or not, one could use a Web Vulnerability Scanner that checks for this vulnerability.
Related reading: Security testing of the Web Application. Check this for more details on different web vulnerabilities.
Vulnerable Parts Of This Attack
Before starting the testing process, every sincere tester should more or less know which parts would be most vulnerable to possible this attack.
It is also a good practice to plan which field of the system is to be tested exactly and in what order. In my testing career, I have learned, that it is not a good idea to test fields against SQL attacks randomly as some fields can be missed.
As this attack is being performed in the database, all data entry system parts, input fields, and website links are vulnerable.
Vulnerable parts include:
- Login fields
- Search fields
- Comment fields
- Any other data entry and saving fields
- Website‘s links
It is important to notice that while testing against this attack it is not enough to check only one or a few fields. It is quite common, that one field may be protected against SQL Injection, but then another does not. Therefore it is important not to forget to test all the website’s fields.
Automating SQL Injection Tests
As some tested systems or websites can be quite complicated and contain sensitive data, testing manually can be really difficult and it takes a lot of time too. Therefore testing against this attack with special tools can be really helpful at times.
One such SQL Injection tool is SOAP UI. If we have automated regression tests at the API level, we can also switch checking against this attack using this tool. In the SOAP UI tool, there are already prepared code templates for checking against this attack. Those templates can also be supplemented by your own written code.
It is a quite reliable tool.
However, a test should already be automated at the API level, which is not that easy. Another possible way to test automatically is by using various browser plugins.
It should be mentioned, that even if automated tools save your time, they are not always considered to be very reliable. If we are testing a banking system or any website with very sensitive data, it is highly recommended to test it manually. Where you can see the exact results and analyze them. Also, in this case, we can be sure, that nothing was skipped.
Comparison With Other Attacks
SQL Injection can be considered as one of the most serious attacks, as it influences the database and can make serious damage to your data and the whole system.
For sure it can have more serious consequences than a Javascript Injection or HTML Injection, as both of them are performed on the client-side. For comparison, with this attack, you can have access to the whole database.
It should be mentioned, that to test against this attack, you should have quite good knowledge of SQL programming language and in general, you should know how databases queries are working. Also while performing this injection attack you should be more careful and observant, as any inaccuracy can be left as SQL vulnerabilities.
Conclusion
I hope you would have got a clear idea of what a SQL Injection is and how should we prevent these attacks.
However, it is highly recommended to test against this type of attack every time when a system or website with a database is being tested. Any left database or system vulnerabilities can cost a company‘s reputation and a lot of resources to restore the whole system.
As testing against this injection helps to find the most important security vulnerabilities, it is also recommended to invest in your knowledge and testing tools.
If Security Testing is planned, then testing against SQL Injection should be planned as one of the first testing parts.
Have you come across any typical SQL Injection? Feel free to share your experiences in the comments section below.
Comments
Post a Comment