How can I prevent 2nd order SQL attacks?

I’m using PHP PDO for my queries, everywhere, but I read that in very rare cases there could still be “second order injections” where an unsafe variable is stored then executed when used in another statement.

Will prepared statements still protect against this? As long as I make sure I always use them? Or do I have to take more precautions? Am I still vulnerable to XSS attacks?

I also have a couple more questions, just out of curiosity, if you all don’t mind:

1. Is it possible to have an SQL Injection with only alphanumeric characters, spaces, and one dash? Like select * from something where name='$some_variable'. All the examples I’ve seen seem to require other characters like semicolons, quotes, or double dashes.

2. I’ve read many SQL examples where the unsafe variable could be set to form another statement, eg

   $foo = "foo'); INSERT INTO users (name) VALUES ('hi";
   $bar = ("INSERT INTO users (name) VALUES ('$foo')");
   

But I just tested and mysql_query doesn’t even allow multiple statements. I know you can still have injections within 1 statement, but can I confirm that you won’t have problems with multiple statements in PHP?

Answer

Not to beat a dead (or is it a very alive?) horse, but…

Injection can only happen when data is read by the SQL engine as commands. In a very simple case, if you allow unescaped " characters in your data, and your data is encapsulated by " characters in SQL, they you have enabled an SQL injection attack.

The key to preventing any SQL injection is to properly validate and escape incoming data EVERY time, at the time it goes into the SQL statement. An easy way to do this is to just use prepared statements, which take care of it for you, allowing you to safely pass parameters to an SQL statement.

Each database library has it’s own way of escaping or using prepared statements. In MySQL and PHP, you have mysqli_real_escape_string(), which should be used EVERY TIME PERIOD, when you are using the mysqli library.

The PDO library has it’s own way, but if I recall correctly, prepared statements were a big part of PDO — use them 100% of the time, and you will be OK in that regard.