PHP SQL Injection Failing

Question

I work as a coding school mentor and I am introducing my students to PHP and concepts like PDO and prepared statements. To show them how prepared statements(among other things) can be useful in protecting against SQL injection, I am trying to do some SQL injection of my own on my own form. I have tried tens of variants of a DELETE FROM users query to no success even though these queries give the intended results when i run them on PHPMyAdmin. This begs the question: did PHP introduce something extra for security against SQL injection recently? Or maybe I am doing something wrong?

Below is the code that I am using to insert a new user:

try {
  $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";

  $this->conn->exec($sql);
  echo "New record created successfully";
} catch (PDOException $exception) {
  echo $exception;
}

Whenever i try any input like Bob'); DELETE FROM users where ('1' = '1 it gives me

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 'DELETE FROM users where ('1' = '1')' at line 1

Even though the same query works on PHPMyAdmin

Any thoughts?

Assuming `$name == "Bob'); DELETE FROM users where ('1' = '1"`: it doesn't work because your first query excepts 2 inputs ... If you would debug it (for example `echo $sql`) you would have seen that your query acutally looks like this: `INSERT INTO users (name, email) VALUES ('Bob'); ... /* Rest doesnt matter */`, you say your insert query to insert name **AND** email, but you only provide the name => syntax error. Try instead `$name = "someName', 'someEmail'); DELETE FROM users; --"` (-- to comment out the rest.) — Definitely not Rafal, Jul 25 '21 at 18:07
@DefinitelynotRafal yes the input i am showing was actually for the email. The name i fiilled normally. I also tried using the ```--``` but that also didn't work. The query is breaking after the semicolon directly. I guess as the answer below mentioned, PDO is not letting me inject a 2nd query after the ; — Khaldoun Nd, Jul 25 '21 at 18:12

Bill Karwin · Answer 1 · 2021-07-25T18:46:07.077

Edited answer:

As noted in the comments above, your try at SQL injection is actually an error because you aren't passing two values to the VALUES clause, even though the INSERT expects two values.

PDO does actually support multi-query by default, but this is unusual for MySQL query interfaces. The MySQL C API requires a special connection option to enable multi-query, and most connectors don't set the option. See https://dev.mysql.com/doc/c-api/8.0/en/c-api-multiple-queries.html

It's not a question of whether PDO has matured to prevent it; PDO enables multi-query by design. This is unfortunate, because it enables a whole category of SQL injection exploits, and it's unnecessary. It's better to leave the multi-query option disabled, and instead just run one query at a time. As the former director of MySQL engineering once told me, "there's no good reason for the multi-query feature to exist."

But since this is unusual for MySQL connectors in general, it may not be a good example of SQL injection.

Try it with a $name value of "O'Reilly".

Running multiple statements separated by semicolons doesn't work in most query APIs. So the venerable Little Bobby Tables cartoon doesn't work most of the time.

But SQL injection can be more subtle. The injected syntax can cause other effects even though it is limited to one statement. For example, in this case an INSERT statement could enter multiple rows.

But most often, SQL injection is not a deliberate attack by a malicious user. It's just a syntax error.

$sql = "INSERT INTO users (name, email) VALUES ('O'Reilly', 'radar@4077mash.mil')";
                                                  ^

See the problem here? The extra quote is going to cause this INSERT to fail.

Sadly, a couple of years ago when I was interviewing for a company, the engineering manager found a form input that i had missed and used the classic ```Drop table users``` and broke my docker database, ruining my whole app. This is why I am trying to replicate this particular scenario. But yes i totally agree, without proper sanitation and handling of special characters like ' it would throw an error. Also as you said i believe PDO now has matured enough to prevent multiple queries. — Khaldoun Nd, Jul 25 '21 at 18:10
I got a downvote, which is fair because I had made a mistake. I've rewritten a new intro to my answer. — Bill Karwin, Jul 25 '21 at 18:47

PHP SQL Injection Failing

1 Answers1