SQL injections

Overview

SQL queries are often used retrieve or update data on a database server, which means that if attackers could in any way manipulate such query, they could access or alter data which could lead to a loss in confidentiality and integrity of data. In certain cases, it could even lead to remote code execution on the underlying systems. For this reason, it is important to defend against SQL injections. 

Where SQL injections happen?

SQL injections tend to occur when user input is not properly filtered when entered into SQL queries.

Here is a short snippet of vulnerable python code:

query = f"SELECT * FROM products WHERE name LIKE '%{user_input}%'"

The vulnerability is present in the sense that user_input is directly inserted into the String of the query. In other words, the input is inserted directly into the query. This means that input such as a quote ' could interfere with the original intended query.
For example, if the query were to be ' OR '1'='1 then the query would be changed to the following:

SELECT * FROM products WHERE name LIKE '%' OR '1'='1'%';

In this case, 1=1 would equal true and thus return all the data in that particular table. This is a simplification of the vulnerability, simply to illustrate the issue in itself. However, attackers will often perform more context-based injections to achieve more impactful results.

How to prevent it?

Generally, a layered approach is preferred. Yet, at the core of the issue the usage of “parameterised queries”, which tends to be available in most programming languages. Observe a snippet of python code with such protections:

query = "SELECT * FROM products WHERE name LIKE ?"
cursor.execute(query, ('%' + user_input + '%',))

The ‘?’ becomes a placeholder for the input that indicates to the code that this input should be taken as text and not a literal String. In other words, it prevents user input from interfering with the actual query format. Thus, protecting from injections. This tends to be the best solution, yet it may still fail due to a vulnerability within the code itself of the parameterisation function. For this reason, it is advised to add various layers of defence against SQL injections.

Example of layers of defence against SQL injections

Below are a few examples of considerations when implementing defensive layers against SQL injections:

• Input validation: Ensure that all user inputs are validated and sanitized. Use an allow-list of allowed input formats and reject anything that does not conform.
• Parameterised queries: Use parameterised queries or prepared statements to ensure that user inputs are treated as data, not executable code. This prevents attackers from injecting malicious SQL code.
• Stored procedures: Use stored procedures for database operations instead of dynamic SQL queries. Stored procedures can help encapsulate SQL logic and reduce the risk of injection.
• Least privilege principle: Limit database user permissions to the minimum necessary for the application to function. Avoid using administrative accounts for application access.
• Error handling: Implement proper error handling to avoid exposing database error messages to users. Detailed error messages can provide attackers with valuable information about the database structure.
• Web Application Firewalls (WAF): Deploy a WAF to detect and block SQL injection attempts. WAFs can provide an additional layer of protection by filtering out malicious traffic.
• Regular security audits: Conduct regular security audits and code reviews to identify and fix vulnerabilities. Use automated tools to scan for SQL injection vulnerabilities.
• Database security features: Utilise database security features such as query parameterization, input sanitization functions, and built-in security mechanisms.
• Education and training: Educate developers and staff about SQL injection risks and best practices for secure coding. Regular training can help maintain a security-aware culture.

What about Object-Relational Mapping (ORM)?

Frameworks that include ORMs can be used as they often provide the following benefits:

• Parameterised queries: ORMs typically use parameterised queries by default, which ensures that user inputs are treated as data rather than executable code. This prevents attackers from injecting malicious SQL code.
• Automatic escaping: ORMs often automatically escape special characters in user inputs, reducing the risk of SQL injection.
• Abstraction layer: By abstracting the database interactions, ORMs reduce the need for developers to write raw SQL queries, which are more prone to injection vulnerabilities.

As observed above, ORMs can be a great addition to mitigating against SQL injections, yet as with any piece of code, it may have vulnerabilities. Therefore, it is also important to monitor the ORMs, or any software, for updates and CVEs.

Conclusion

There are a variety of steps that can be taken to prevent SQL injections and minimise its impact. It is important to take these steps as SQL injections if left unresolved can certainly lead to a compromise of confidentiality and integrity of data with potential to also leading to access of underlying systems.