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

SQL Injection: 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.