Discussion on: Securing PHP Applications Against SQL Injection Attacks

[MD ARIFUL HAQUE]

How "OR 1=1" Works:

In SQL, a query can include conditions that must evaluate to true for rows to be selected. Typically, you might have a query like this:

SELECT * FROM users WHERE user_id = 1;

This will return all rows where the user_id is equal to 1.

Now, let's say someone is trying to perform an SQL injection by appending "OR 1=1" to this query, resulting in something like:

SELECT * FROM users WHERE user_id = 1 OR 1=1;

Why "OR 1=1" is Effective in SQL Injection:

• Boolean Logic: The key idea here is that 1=1 is always true. So when an attacker injects "OR 1=1", they are manipulating the logic of the WHERE clause.
• Effect on the Query: The condition OR 1=1 essentially makes the WHERE clause always evaluate to true for every row in the table. This happens because SQL evaluates the entire WHERE clause, and since 1=1 is always true, it returns all rows, regardless of the initial condition user_id = 1.

Thus, instead of the query returning only rows where user_id = 1, the "OR 1=1" part overrides it, returning all rows in the table. The reason for this is that in a logical OR operation, as long as one part of the condition is true (1=1 in this case), the entire condition is true.

To Summarize:

• The query is supposed to filter rows by user_id, like user_id = 1.
• When "OR 1=1" is injected, it turns the condition into something like user_id = 1 OR true, which means the database will return all rows, because the condition always evaluates to true.
• Security Implication: This type of injection can be dangerous, as attackers might use it to bypass authentication or retrieve sensitive data from the entire database.

Why "1=1" Instead of Returning Only user_id Rows?

• In the original query, you'd expect rows with user_id = 1 to be returned.
• But with OR 1=1, the entire condition becomes true for all rows, not just rows with user_id = 1. That’s why the database returns all rows instead of being limited by the specific user_id condition.

This is a typical way attackers try to bypass restrictions and get unauthorized access to data. It’s important to protect your queries against such injections using techniques like prepared statements or parameterized queries.

[Justin]

Ah, thanks for that. I misread the scenario.

And the prepared statements would avert the injection because the input to the query has to be an integer?

[MD ARIFUL HAQUE]

Prepared Statements Prevent SQL Injection:

1. Parameter Binding: In prepared statements, the query and its parameters are handled separately. When using a prepared statement, the SQL query is first sent to the database server without the input parameters. The server parses, compiles, and optimizes the query plan. Then, the user-provided data (like integers, strings, etc.) is sent as separate data and bound to the prepared statement.

2. No Direct Injection into SQL Query: Since the user input is treated as data, not as part of the SQL code, the input cannot alter the structure of the query. Whether it's a string, an integer, or any other data type, the database knows it’s supposed to be a value, not executable code. Thus, even if an attacker tried to insert malicious code (like OR 1=1), it would be treated as just data and not as part of the SQL logic.

3. Data Type Enforcement: Prepared statements enforce data types for parameters. So, if the query expects an integer, the database will treat the input as an integer and reject any input that doesn't match that data type.

Example Without Prepared Statement (Vulnerable to Injection):

$id = $_GET['id']; // Input from user
$query = "SELECT * FROM users WHERE id = $id"; // Vulnerable
$result = $db->query($query);

If the user passes 1 OR 1=1, the query becomes:

SELECT * FROM users WHERE id = 1 OR 1=1;

This would return all rows because 1=1 is always true.

Example With Prepared Statement (Safe):

$id = $_GET['id']; // Input from user
$query = "SELECT * FROM users WHERE id = ?"; // Prepared statement
$stmt = $db->prepare($query);
$stmt->bind_param("i", $id); // Binding the input as an integer
$stmt->execute();
$result = $stmt->get_result();

Even if the user passes 1 OR 1=1, the database will treat it as a simple integer or string, not as part of the SQL logic.

Summary:

Prepared statements prevent SQL injection by:

• Separating query structure from data.
• Treating user input as literal values, not SQL code.
• Enforcing data types, ensuring the query receives the correct type of input.