Someone told me the other day that “prepared statements can be 100% against SQL Injection”. But, is it true?

In this section, we’ll explain how prepared statement can against SQL injection in some case and explain how prepared statement can NOT against in other cases...

What is prepared statement?

Prepared statements are a way to execute SQL statements more securely in web applications or systems that use the SQL language to access databases.

when using prepared statements, SQL statements are prepared in advance by separating the statement into two parts: the first part is the SQL statement containing the parameters (placeholder), and the second part is the actual value of those parameters.

It has to pass through below the left phases, other hand right phase is a normal SQL query.

1. Parsing and Normalization Phase

• Syntax check

• Semantic check

• Check Table and Column in Query exist

2. Compilation Phase

• Convert Query into Machine understandable format

3. Query Optimization Plan

• Checking possible

4. Cache

• Stored best optimized way

5. Replacement

After placeholders are replaced with user data, query is not compiled/interpreted again and the SQL Server engine treats user data as pure data and not a SQL that needs to be parsed or compiled again and that is beauty of PreparedStatement.

6 . Execution Phase

Can prepared statements prevent SQL Injection within ORDER BY clause?

Before writing this post, I have researched on many various forums and quest some AI bots to this question

The answer is pretty vague 🥲

So..., let's start explain with 'order by'

In SQL, the "ORDER BY" clause is used to sort the result set of a query in ascending or descending order based on one or more columns

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

And the column name here is an identifier, while a placeholder can be used to represent a value, it cannot be used to substitute a column name (column1, column2) because placeholders represent values such as the number 42, the string "Hello, World!", and the date "2023-04-18",... In other words, placeholders are used to substitute actual data values, not the names of columns or tables.

SELECT id, name, salary
FROM employees
ORDER BY ? DESC;

In this query, the placeholder represents a value that will be supplied later. When the query is executed, the database will replace the placeholder with the actual value provided by the caller.

However, if substitute this column name with a placeholder, the database will not understand any action query and we will receive an error message.

The "ORDER BY" clause requires a valid column name like this:

SELECT id, name, salary
FROM employees
ORDER BY salary DESC;

In this query, we are explicitly specifying the "salary" column name in the "ORDER BY" clause to sort the result set in descending order based on the "salary" column.

From 2 examples, we get any part of the reason prepared statements prevent SQL Injection within the ORDER BY clause. The next module is solution.

Solution

To prevent SQL injection attacks in an ORDER BY statement is to use a white list. In this approach, you define a list of acceptable values for the ORDER BY clause and only allow those values to be used in your queries.

// Define a white list of allowed column names
$allowed_columns = array('product_name', 'price', 'created_at');

// Get the user's selected column name from the request
$column = isset($_GET['column']) ? $_GET['column'] : '';

// Check if the selected column name is in the white list
if (in_array($column, $allowed_columns)) {
    // Construct the query using the selected column name
    $query = "SELECT * FROM products ORDER BY $column";
    // Execute the query...
} else {
    // Handle invalid input...
}

In the above example, we define a white list of allowed column names ($allowed_columns) that can be used in the ORDER BY clause.

We then get the user's selected column name from the request ($column) and check if it is in the white list using the in_array() function.

It only checks the column name for SQL injection. It does not check other parts of the SQL statement, such as the ORDER BY clause, for potential injection vulnerabilities. It's always a good practice to sanitize and validate all user inputs and use parameterized queries whenever possible, to avoid SQL injection vulnerabilities.