I rode something about sql 2nd order injection, from what I understood they happen when you retrieve the data from the DB and a "var (row cell data)" contain an SQL code that will execute(? why will it be executed?).
My question is: in order to prevent from these attacks, is there a way to tell PDO that the data I'm querying is ONLY PURE DATA and no SQL code to execute (like when you use prepared statements to bind values...)?
Second order SQL injection is not a vulnerability of the database, or PDO, etc., where SQL code stored in the database is unexpectedly executed by PDO or the database when you fetch it.
A second order SQL injection vulnerability is very similar to the classic vulnerability, but is caused by your code naively handling or trusting the data that you have already stored in the database, instead of naively handling external/user-supplied data.
If I try to log in to your web site with username michael ' OR 1=1 -- and you are naively crafting a query vulnerable to SQL injection, I might just be able to log in because your query becomes...
SELECT * FROM user
WHERE username = 'michael' OR 1 = 1 --';
You already understand this part.
So, you're using prepared statements and avoiding this kind of basic programming flaw.
However, if I'm able to create such an account, now you have potentially unsafe data stored in your database. But it isn't unsafe because PDO or the server will allow it to later spontaneously execute.
It's unsafe if you, correctly but naively, later in your code assume that my account information "came from the database" rather than "came from the an external source" and you lazily build a query...
... "WHERE username = '" + $stored_username + "';" ...
...this is a second order SQL injection vulnerability. I might be able to, for example, trick your code into setting all users' passwords to a single password, by signing into my account and changing my password.
If you always use prepared statements and your code does not naively handle data that came from the database as if it were somehow "already safe," then the second-order vulnerability can be avoided.
It isn't a fundamentally-different case, it just happens "later" in your code.
