SQL Injection is a well-known attack technique in which malicious code is injected into SQL queries via client-side input, altering the original query and leading to devastating results such as reading sensitive information from the database or updating, deleting, or inserting data. This technique stems from the server-side’s reliance on user input to fully execute database queries, endangering organizations of all sizes. SQL Injection attacks are a common finding in application penetration tests (PT) performed by IPV Security’s team of experts, and its severity is consistently rated as critical. Hence, it is vital to understand how to defend against and prevent it within your application.
How is an SQL Injection attack carried out?
Let’s take an example of a simple query executed on the server:
`“SELECT * FROM users WHERE username = ‘” .$username. “’” ;`
This query is incomplete due to its dependence on the `$username` variable. An attacker can exploit this by injecting malicious SQL code:
Enter Username: `[ shay’;delete from users where id=’1 ]`
The statement sent to the server will be:
`“SELECT * FROM users WHERE username =’shay’; delete from user where id=’1’”;`
This manipulation allows the execution of unauthorized actions, such as deleting records from the database.
Preventing SQL Injection using Prepared Statements
Prepared Statements are a powerful defense against SQL Injection attacks. They use parameters as placeholders in SQL queries, forcing the developer to define all SQL code in advance. The code is compiled once, and any client-side input is bound to the parameters without the input being interpreted as SQL commands.
Why Prepared Statements are Effective
The effectiveness of Prepared Statements stems from the pre-compilation process, which is performed only once. Since the code is compiled before the client data is introduced, any SQL commands within the input will not be interpreted as such; instead, they are treated as simple text. Therefore, the client input will not be executed as an SQL command.
The Prepared Statements Workflow
Prepared Statements follow a structured process:
1. Parsing and Normalization
2. Compilation
3. Query Optimization
4. Caching
For example: `UPDATE user SET username=? AND password=? WHERE id=?`
This query with placeholders will undergo compilation and optimization and will be stored in the cache. During execution, the placeholders are replaced with the user data, and the server treats the input strictly as text.
A similar analogy can be found in XSS (Cross-Site Scripting) vulnerabilities. In cases where HTML and JS snippets are injected by an attacker but displayed with an `application/json` Content-Type, this setting causes the browser not to treat the injected code as HTML, preventing it from executing. Similarly, injected SQL code is not treated as SQL code and is not executed within the query.
Example of a Secured Prepared Statement Implementation (using placeholders):
“`php
prepare(“SELECT * FROM users WHERE username=? AND password=?”);
$compile->execute([$username,$password]);
while($row = $compile->fetch()) {
var_dump($row);
}
?>
“`
Example of an Ordinary Vulnerable Query (direct string concatenation):
“`php
“`
Conclusion
The use of Prepared Statements is essential for protecting applications from SQL Injection attacks. By pre-compiling SQL queries and treating user input as pure data, they provide robust protection against unauthorized access and database manipulation. Implementing Prepared Statements not only enhances security but also improves query performance and efficiency, making them a best practice in all application development.
By adopting Prepared Statements, organizations can significantly mitigate the risk of SQL Injection attacks and ensure the integrity and security of their databases. Be proactive in application security by integrating this essential technique into your development practices.
Need to consult with an application security expert or order an application penetration test?