Products

Solutions

Resources

Search for API Security Tests

SQL Injection Prevention Cheat Sheet

This blog is a guide that provides best practices and techniques for preventing SQL Injection, which is a common web application vulnerability where an attacker can manipulate SQL queries in order to gain unauthorized access to a database. Learn more about SQL Injection.

SQLInection Prevention Cheat Sheet
SQLInection Prevention Cheat Sheet
SQLInection Prevention Cheat Sheet
Medusa Author

Medusa

8 Mins

Here are some practices you can implement to prevent SQL Injection.

  • Prepared Statements

  • Do not rely on client-side input validation

  • Restrict Privileges

  • Scan your API for SQLi

  • Use an ORM Layer

  • Input Validation

  • Stored Procedures

Prepared Statements

Prepared statements, also known as parameterized queries, prevent SQL injection by separating SQL code from data, which denies attackers the ability to manipulate the query structure. When you use prepared statements, you create SQL queries with placeholders for input instead of directly inserting user input into the query. You then send the input to the query via a separate mechanism and never actually insert it into the SQL query directly. This ensures that even if an attacker tries to inject SQL code into the input fields, the code will be treated as data and not as part of the SQL command, thus preventing SQL injection attacks.

Here's an example that demonstrates how to prevent SQL injection attacks using prepared statements in Java with JDBC:

String selectSQL = "SELECT USER_ID FROM USERS WHERE USERNAME = ?";
PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
preparedStatement.setString(1, "user123");
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
    String userid = rs.getString("USER_ID");
    System.out.println("User ID: " + userid);
}

In this example, the user input is "user123". This input is separated from the SQL command, ensuring that it's treated as a literal string and not part of the SQL command, thus preventing it from manipulating the query.

To prevent SQL injection in different programming languages and frameworks, consider the following suggestions:

  • In Java EE, you should use PreparedStatement() and bind variables.

  • In .NET, you should employ parameterized queries such as SqlCommand() or OleDbCommand(), and bind variables.

  • In PHP, you should utilize PDO with strongly typed parameterized queries, employing bindParam().

  • In Hibernate, you should apply createQuery() along with bind variables (referred to as named parameters in Hibernate).

  • For SQLite, you should use sqlite3_prepare() to create a statement object.

One of the main disadvantages of prepared statements is that they can add complexity to the code, making it harder to write and understand. Furthermore, they can lead to performance issues if not used correctly. For instance, if the database does not efficiently manage prepared statements, it could lead to increased memory usage.

In such cases, the optimal approach would be to either conduct rigorous data validation or escape all user inputs using an escape routine that is specific to your database vendor, rather than relying solely on prepared statements.

Do not rely on client-side input validation

Client-side input validation provides a first layer of security by helping filter out malicious input before it reaches the server. It prevents simple attacks and improves user experience by providing immediate feedback. However, relying solely on client-side input validation is not recommended as attackers can easily bypass it.

Attackers can bypass client-side input validation in several ways. For example, they can intercept data as it travels from the client to the server and modify it using tools like Burpsuite or Postman. They can also disable JavaScript validation in the browser or use curl commands to manipulate the HTTP request. If the server doesn't validate input because it trusts the client side, serious issues like SQL Injection can arise.

Restrict Privileges

Restricting the privileges of database users reduces the damage caused by SQL injection attacks. SQL injection attacks occur when attackers inject malicious input into an application's SQL query, allowing them to manipulate the query and potentially gain unauthorized access to, modify, or delete data in a database.

Let's consider an example where we have a database with two tables: users and orders. We want to restrict the privileges of a user to only allow them to perform SELECT operations on the users table and INSERT operations on the orders table.

Here's how you can achieve this in SQL:

-- Create a new user with restricted privileges
CREATE USER 'restricted_user'@'localhost' IDENTIFIED BY 'password';
-- Grant SELECT privilege on the 'users' table
GRANT SELECT ON database_name.users TO 'restricted_user'@'localhost';
-- Grant INSERT privilege on the 'orders' table
GRANT INSERT ON database_name.orders TO 'restricted_user'@'localhost';
-- Revoke all other privileges
REVOKE ALL PRIVILEGES ON database_name.* FROM 'restricted_user'@'localhost'

In this example, we create a new user called restricted_user with the password password. We then grant the SELECT privilege on the users table and the INSERT privilege on the orders table. Finally, we revoke all other privileges to ensure that the user has only the necessary permissions.

It is important to note that the exact syntax and commands may vary depending on the specific database management system you are using. Refer to the documentation for your specific database system for detailed instructions on how to restrict privileges.

By following the principles outlined below, you can effectively mitigate the impact of such attacks:

  1. Adhere to the principle of least privilege: Grant each database user only the minimum privileges required to perform their specific tasks. This approach limits the capabilities of potential attackers, as they are less likely to possess the necessary privileges to execute harmful actions. For instance, a user with read-only access to specific tables will be unable to modify or delete data.

  2. Isolate privileges: Restrict privileges to contain the potential damage caused by SQL injection attacks. If an attacker successfully exploits a vulnerability in one part of your application, their access will be limited to a restricted set of resources and data, rather than compromising the entire database. This containment reduces the overall impact of the attack.

  3. Protect data: Use restricted privileges to store critical and sensitive data in separate database accounts with stricter access controls. By doing so, even if an attacker gains access to a less privileged account, they will be unable to access sensitive data or modify critical tables. This reduces the risk of data breaches or data tampering.

  4. Apply execution constraints: Limit the privileges of database users to restrict their ability to execute certain types of SQL statements or commands that could be exploited for malicious purposes. For example, you can limit the use of statements like DROP TABLE or DELETE for certain users, making it more difficult for attackers to delete data or manipulate the database structure.

  5. Maintain audit trails: Enforce restricted privileges and maintain detailed audit logs to effectively track and identify unauthorized activities within your database. This enables you to detect and respond to SQL injection attacks more efficiently and facilitates forensic analysis in the event of a breach.

Scan your API for SQLi

Manually checking APIs for SQL Injection vulnerabilities can be challenging. Akto can assist in automating this vulnerability detection process - Akto - Open Source API Security platform.

If you plan on testing custom payloads or conducting manual testing in the future, consider reviewing this SQL injection cheat sheet.

Use an ORM layer

Using an Object-Relational Mapping (ORM) layer is another strategy that you should consider for mitigating SQL injection risks. An ORM layer acts as an intermediary between your application code and the database and transforms data retrieved from the database into objects that your code can work with and vice versa, without requiring you to write explicit SQL queries. This approach reduces the likelihood of SQL injection vulnerabilities for several reasons.

  1. Abstraction of SQL: ORM libraries abstract away the low-level SQL queries, which reduces the risk of inadvertently introducing SQL injection vulnerabilities in your code because you're not directly constructing SQL statements. This abstraction minimizes the risk of using insecure SQL syntax or failing to properly sanitize inputs.

  2. Prepared Statements: ORM libraries typically use prepared statements or parameterized queries under the hood. Prepared statements separate SQL code from data inputs, making it difficult for an attacker to inject malicious SQL into your queries.

  3. Automatic Data Validation: ORM frameworks often provide automatic data validation and type casting, ensuring that data retrieved from the database is treated as the correct data type, reducing the chances of unexpected SQL injection.

  4. Custom Query Handling: While ORM libraries provide an abstraction for most common database operations, they also allow you to create custom queries when needed. However, when constructing custom queries using ORM libraries, you should still follow best practices for parameterization and avoid directly interpolating user input into queries.

For example, Hibernate (Java), Entity Framework (C#), and Sequelize (JavaScript) are popular ORM libraries that generate SQL queries on your behalf based on your application's object-oriented model, which helps prevent SQL injection vulnerabilities in your code. However, it's essential to trust that these libraries have implemented proper escaping and parameterization techniques to protect against SQL injection.

Whitelist Input Validation

Whitelisting prevents SQL injection by explicitly defining a set of trusted input values and allowing only those values to be used in specific contexts. This security practice ensures that user input conforms to expected criteria and eliminates the risk of unauthorized data manipulation in SQL queries.

Here's how whitelisting prevents SQL injection:

Example: Let's consider a scenario where a user provides input to determine the sorting order of a list of products in an e-commerce application. The input could be either "ascending" or "descending" for ascending and descending order, respectively.

Without whitelisting:

String userInput = "ascending"; // User-provided input
// Vulnerable to SQL injection
String sqlQuery = "SELECT * FROM Products ORDER BY Name " + userInput;

In this case, an attacker could manipulate the userInput variable to inject malicious SQL code, potentially compromising the integrity of the database.

You can whitelist like this:

String userInput = "ascending"; // User-provided input
// Whitelisting validation
if (userInput.equals("ascending")) {
    // Safe SQL query
    String sqlQuery = "SELECT * FROM Products ORDER BY Name ASC";
} else if (userInput.equals("descending")) {
    // Safe SQL query
    String sqlQuery = "SELECT * FROM Products ORDER BY Name DESC";
} else {
    // Handle invalid input (e.g., log an error or provide a default sorting option)
}

In this whitelisting example, only "ascending" and "descending" inputs are accepted. Any other input is handled as an error or default behavior, ensuring that only predefined, safe values are used in the SQL query.

Want the best proactive API Security product?

Our customers love us for our proactive approach and world class API Security test templates. Try Akto's test library yourself in your testing playground. Play with the default test or add your own.

Stored Procedures

Stored procedures are a feature of many relational databases that allow you to store and execute SQL statements on the server. They help prevent SQL injection attacks by allowing you to pass parameters in a way that prevents them from being interpreted as SQL code.

Using stored procedures is a common practice to mitigate SQL injection risks. However, it's important to be cautious and implement them correctly to avoid vulnerabilities. Here's why you should exercise caution and how to secure stored procedures:

  1. Stored Procedure Injection: Malicious input can manipulate a stored procedure, leading to SQL injection vulnerabilities. If you construct stored procedures improperly, attackers can inject malicious code.

  2. Parameterization: To prevent SQL injection in stored procedures, it's crucial to parameterize your queries instead of directly concatenating user inputs into the SQL statement. This involves using placeholders or input parameters in your stored procedure and binding them to values when executing the procedure.

    Incorrect Implementation Example (MySQL):

DELIMITER //
CREATE PROCEDURE `FindUsers`(
    IN Username VARCHAR(50)
)
BEGIN
    SET @Statement = CONCAT('SELECT * FROM User WHERE username = ', Username, ' );
    PREPARE stm FROM @Statement;
    EXECUTE stm;
END //
DELIMITER ;


Correct Implementation Example (MySQL):

DELIMITER //
CREATE PROCEDURE `FindUsers`(
    IN Username VARCHAR(50)
)
BEGIN
    PREPARE stm FROM 'SELECT * FROM User WHERE username = ?';
    EXECUTE stm USING Username;
END

  1. Database-Specific Implementation: It is important to note that different database systems have varying implementations of stored procedures. Ensure that you understand the specific syntax and best practices for implementing stored procedures in your chosen database.

  2. Alternative Solutions: While stored procedures can help prevent SQL injection, consider whether your application code can achieve the same level of security. Prepared statements and parameterized queries in your application code are often more straightforward and easier to maintain, especially if your programming language supports them.

Firewalls

Firewalls can play a role in preventing SQL injection attacks by acting as a barrier between potential attackers and your APIs. Here's how a firewall can help in this context:

  1. Web Application Firewall (WAF): A Web Application Firewall is a security appliance or service specifically designed to protect web applications from various online threats, including SQL injection attacks. Here's how a WAF can help prevent SQL injection:

    • Request Filtering: WAFs inspect incoming HTTP requests and analyze the parameters and data being sent to your web application. They employ signature-based or behavior-based analysis to identify potentially malicious SQL injection patterns.

    • Pattern Recognition: WAFs can be configured to recognize known SQL injection attack patterns, such as common SQL keywords or special characters used in SQL injection payloads. When WAFs detect such patterns, they can block or sanitize the malicious requests.

    • Parameter Validation: WAFs validate input parameters against expected formats and patterns. For example, they can check that user-provided input conforms to specific data types, reducing the risk of accepting malicious SQL payloads.

    • Rate Limiting: WAFs can impose rate limits on requests to prevent attackers from flooding your application with numerous SQL injection attempts in a short period.

  2. Positive Security Model: Some advanced WAFs employ a positive security model, which allows only known legitimate traffic to pass through and blocks everything else. This approach is highly effective at blocking unexpected and potentially malicious input, including SQL injection payloads.

  3. Signature-Based Protection: Many WAFs maintain a database of known SQL injection attack signatures. They compare incoming requests against these signatures and block or alert when a match is found.

  4. Regular Updates: To be effective, a WAF should regularly update with the latest attack signatures and security rules to stay current with emerging threats.

Conclusion

In conclusion, SQL Injection remains a prevalent threat to web application security. However, by adhering to best practices such as using prepared statements, implementing an ORM layer, validating input, and restricting database privileges, developers can significantly mitigate the risk of SQL Injection attacks. To make this task easier, you can use Akto to identify vulnerabilities in your APIs.

Share this post

Share this post

Share this post

Monthly product updates in your inbox. No spam.

Table of contents