SQL Injection Testing Tutorial (Example and Prevention of SQL Injection Attack)

Examples of SQL Injection and Measures to Prevent SQL Injection Attacks on Web Applications

When testing a website or system, the primary goal of the tester is to ensure maximum protection for the product being tested.

Security testing is commonly conducted for this purpose. Before conducting this type of testing, it is important to consider which attacks are most likely to occur. SQL Injection is one of those attacks.

SQL Injection is considered to be one of the most prevalent attacks due to its potential to cause serious and detrimental consequences to your system and sensitive data.

Understanding SQL Injection

SQL Statements that are used by an application to interact with the database may incorporate user inputs. However, it is often difficult for an application to handle user inputs appropriately.

A malicious user can exploit this by providing unexpected inputs that will be used to execute SQL statements on the database. This attack is known as SQL Injection and can have serious consequences.

As the name suggests, SQL Injection involves injecting malicious SQL code.

Every field of a website represents a gateway to the database. For instance, in a login form, the user enters login data, in a search field, the user enters search text, and in a data saving form, the user enters data to be saved. All of this data is sent to the database.

If malicious code is inputted instead of correct data, serious damage can occur to the database and the entire system.

SQL Injection is performed using the SQL programming language, which is used for managing data stored in the database. During this attack, this programming language code is used as a malicious injection.

SQL Injection is one of the most popular attacks, as databases are used across various technologies.

Most applications use databases. In a tested application, the user interface might accept user input for the following purposes:

#1) Display relevant stored data to the user such as checking user credentials using the entered login information and showing relevant functionality and data to the user.

#2) Save user-entered data to the database, such as saving data from a form submission to make it available to the user in the same session and subsequent sessions.

---

Recommended Tools

#1) Acunetix

Acunetix is a web application security scanner that helps manage the security of web assets. It can identify over 7000 vulnerabilities, including SQL injection. It utilizes advanced macro recording technology to scan complex multi-level forms and password-protected areas of a site.

It is an intuitive and easy-to-use tool that requires no lengthy setup or onboarding time. It performs scans at a fast speed and offers features such as scheduling, prioritizing scans, and automatically scanning new builds.

---

#2) Netsparker

Netsparker offers a SQL Injection Vulnerability Scanner that automatically detects various injection vulnerabilities. It uses Proof-Based Scanning™ Technology and provides functionalities for penetration testing, remote file inclusions, checking web server configurations, cross-site scripting, and more. Netsparker can be integrated seamlessly with existing systems.

---

Risks of SQL Injection

In today’s world, databases are used in almost all systems and websites to store data.

Since sensitive data is stored in databases, the security risks to the system are higher. While the theft of data from a personal website or blog may not cause significant damage, the theft of data from a banking system can have severe consequences.

The main objective of this attack is to hack the database of a system, which can result in harmful consequences.

SQL Injection can result in:

• Hacking into another user’s account.
• Theft and copying of sensitive data from a website or system.
• Modification of sensitive data in the system.
• Deletion of sensitive data in the system.
• Unauthorized access to the application as another user, including administrator privileges.
• Access to private information of other users, such as profile details and transaction information.
• Modification of application configuration and other users’ data.
• Modification of the database structure, including deletion of tables.
• Seizing control of the database server and executing commands at will.

The risks mentioned above are serious, as restoring a database or its data can be costly. It can also damage a company’s reputation and require significant resources to recover lost data and systems.

Therefore, it is highly recommended to protect your system against SQL Injection attacks and to consider Security Testing as a valuable investment in your product and company reputation.

As a tester, I would like to emphasize that testing for potential attacks is a good practice even if Security Testing was not initially planned. This approach ensures that the product is protected against unexpected scenarios and malicious users.

The Essence of SQL Injection

As mentioned earlier, the essence of SQL Injection is to exploit the database with malicious intent.

To conduct Security Testing, it is crucial to identify vulnerable parts of the system and send malicious SQL code through them to the database. If this attack is possible for a system, a malicious SQL code can be sent and harmful actions can be performed in the database.

Every field of a website can be considered as a gateway to the database. Any input or data entered into these fields can be used in database queries. Instead of valid data, if malicious code is entered, it can be executed in the database query and result in harmful consequences.

To conduct this attack, the query act and purpose need to be altered. One method is to make the query always true and insert malicious code after that. Changing the query to always true can be achieved with simple code like ‘ or 1=1;–.

Testers should keep in mind that when checking if the query can be changed to always true, different types of quotes should be tried, such as single and double quotes. For example, if we have tried the code ‘ or 1=1;–, we should also try the code with double quotes ” or 1=1;–.

For example, let’s consider a query that searches for a word in a database table:

select * from notes nt where nt.subject = ‘search_word’;

Instead of the search word, if we enter a SQL Injection query ‘ or 1=1;–, the query will always evaluate to true.

select * from notes nt where nt.subject = ‘ ‘ or 1=1;–

In this case, the parameter “subject” is closed with a quote, and then we have the code or 1=1, which makes the query always true. The “–” sign is used to comment out the rest of the query code, which will not be executed. This is one of the most popular and simplest ways to gain control over the query.

Other codes that can be used to make the query always true include:

• ‘ or ‘abc’=’abc’;–
• ‘ or ‘ ‘=’ ‘;–

The important part is that after the comma sign, any malicious code can be entered and executed.

For example, ‘ or 1=1; drop table notes; —

If this injection is possible, any other malicious code can be written. It depends on the knowledge and malicious intent of the attacker. How to Check for SQL Injection?

Checking for this vulnerability can be relatively easy. Sometimes, simply typing a single quote ‘ or ” in the tested fields is enough. If it returns any unexpected or abnormal message, then it can be concluded that SQL Injection is possible for that field.

For example, if an error message like ‘Internal Server Error’ is returned as a search result, it can be inferred that this attack is possible in that part of the system.

Other results that may indicate a 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 see how this works in practice.

For example, let’s test whether a login window is vulnerable to SQL Injection. Simply enter the single quote ‘ in the email address or password field as shown below.

If such input returns an error message like ‘Internal Server Error’ or any other inappropriate result, it can be almost certain that this attack is possible for that field.

A more complex SQL Injection code can also be attempted. It is important to note that in my experience, I have not encountered cases where entering a single quote resulted in an ‘Internal Server Error’ message. However, there have been instances when the fields did not respond to more complex SQL code.

Therefore, checking for SQL Injections with a single quote ‘ is a reliable way to determine if this attack is possible or not.

If the single quote does not produce any inappropriate results, double quotes can be entered to verify the vulnerability.

Another way to check if this attack is possible is by using SQL code to change the query to always evaluate to true. This closes the parameter and changes the query to ‘true’. If the input is not properly validated, it may produce unexpected results and indicate the possibility of this attack.

Checking for possible SQL attacks can also be done through website links. For example, if a website’s link is http://www.testing.com/books=1, replacing the value after the equal sign with a single quote can test for possible injections.

Therefore, the link http://www.testing.com/books= can be used to test the possibility of SQL Injection on the website http://www.testing.com.

In this case, if the link http://www.testing.com/books= returns an error message like ‘Internal Server Error’ or a blank page or any other unexpected error message, it indicates the possibility of SQL Injection on that website. More complex SQL code can then be sent through the website link.

To check if this attack is possible through a website link, code like ‘ or 1=1;– can also be sent.

As an experienced software tester, I would like to remind everyone that not only unexpected error messages can indicate SQL Injection vulnerabilities. Testers often only check for potential attacks based on error messages. However, it should be noted that the absence of validation error messages or successful messages for malicious code can also indicate the possibility of this attack.

Security Testing of Web Applications Against SQL Injection

Simple examples explaining security testing of web applications:

Considering the potential severe consequences, SQL Injection attacks should be tested during the security testing of an application. To better understand this technique, let’s review a few practical examples of SQL Injection.

Important note: SQL Injection testing should only be performed in a testing environment.

If the application has a login page, it is possible that the application uses dynamic SQL statements like the one below. This statement is expected to return at least one row with user details from the Users table when there is a row with the entered username and password:

SELECT * FROM Users WHERE User_Name = ‘strUserName’ AND Password = ‘strPassword’;

If the tester enters ‘John’ as strUserName and ‘Smith’ as strPassword, the SQL statement would become:

SELECT * FROM Users WHERE User_Name = 'John' AND Password = 'Smith';

If the tester enters ‘John’– as strUserName and leaves strPassword empty, 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 commented out, and if there are any users with the username of ‘John’ in the Users table, the application will allow the tester to log in as the user ‘John’. The tester can then access the private information of this user.

What if the tester does not know the name of any existing user? In this case, the tester can try common usernames such as ‘admin’, ‘administrator’, and ‘sysadmin’.

If none of these users exist in the database, the tester can enter ‘John’ or ‘x’=’x’ as strUserName and ‘Smith’ or ‘x’=’x’ as strPassword. This would result in the following SQL statement:

SELECT * FROM Users WHERE User_Name = 'John' or 'x'='x' AND Password = 'Smith' or 'x'='x';

Since the condition ‘x’=’x’ is always true, the result set would consist of all the rows in the Users table. The application will allow the tester to log in as the first user in the Users table.

Important note: The tester should request the database administrator or developer to create a backup of the table before attempting these attacks.

If the tester enters ‘John’; DROP table users_details;– as strUserName and anything as strPassword, the SQL statement would be:

SELECT * FROM Users WHERE User_Name = 'John'; DROP table users_details;-- AND Password = 'Smith';

This statement could result in the permanent deletion of the table ‘users_details’ from the database.

Although the above examples focus on using SQL Injection solely on the login page, it is important to test this technique on all application pages that accept textual user input, such as search pages and feedback forms.

SQL Injection can be possible even in applications that use SSL and might not be mitigated by a firewall.

It is crucial to understand that the above examples simplistically explain the attack technique. SQL Injection testing should only be performed in a testing environment and not in the development or production environments.

Instead of manually testing for SQL Injection vulnerabilities, specialized web vulnerability scanners can be used to check for this vulnerability.

Related reading: Security Testing of Web Applications – Learn more about different web vulnerabilities.

Vulnerable Parts of SQL Injection Attack

Prior to initiating the testing process, it is important for a diligent tester to understand which parts of the system are most vulnerable to this attack.

Planning the exact fields to be tested and the order of testing is a good practice. In my testing career, I have found that randomly testing fields for SQL attacks can result in missed vulnerabilities.

Since this attack is performed in the database, all data entry system parts, input fields, and website links are potential targets.

Vulnerable parts include:

• Login fields
• Search fields
• Comment fields
• Any other data entry and saving fields
• Website links

It is important to note that while testing for this attack, checking only one or a few fields is not sufficient. It is common for some fields to be protected against SQL Injection while others are not. Therefore, testing should cover all fields on the website.

Automating SQL Injection Tests

Testing certain systems or websites can be complex and involve sensitive data, making manual testing challenging and time-consuming. In such cases, using specialized tools for testing SQL Injection can be helpful.

One tool for SQL Injection testing is SOAP UI. If regression tests are automated at the API level, SQL Injection checks can be incorporated using this tool. SOAP UI provides code templates for checking SQL Injection, which can be supplemented with custom code. It is a reliable tool.

However, API-level automation is not always straightforward. Another option for automated testing is browser plugins.

It is worth mentioning that while automated tools can save time, they may not always be fully reliable. When testing a banking system or any website with highly sensitive data, manual testing is highly recommended. Manual testing allows for precise results analysis and ensures completeness of the test coverage.

Comparison with Other Attacks

SQL Injection is considered one of the most serious attacks due to its ability to compromise databases and cause significant damage to data and systems.

Compared to JavaScript Injection or HTML Injection, which are performed on the client-side, SQL Injection can have more severe consequences as it provides access to the entire database.

To conduct SQL Injection testing, a tester should possess a good understanding of SQL programming language and database query mechanisms. Attention to detail is crucial, as any mistakes can result in SQL vulnerabilities.

Conclusion

We hope this tutorial has provided a clear understanding of SQL Injection and the techniques to prevent these attacks.

However, it is highly recommended to test for SQL Injection vulnerabilities whenever a system or website with a database is being tested. Neglecting database and system vulnerabilities can result in significant damage to a company’s reputation and resources.

Testing for potential SQL Injections helps identify critical security vulnerabilities and should be planned as an integral part of Security Testing. Even if Security Testing was not initially planned, testing for potential attacks is a good practice to ensure protection against unexpected scenarios and malicious users.

Have you encountered any notable SQL Injections? Share your experiences and insights in the comments section below.