Normal SQL injections are no problem since I always use prepared statements, but how to protect oneself from second order SQL injections?
A second order SQL injection is an injection where the payload is already stored in the database (instead of say being delivered in a GET parameter). In that sense it is somewhat similar to stored XSS (and ordinary "first order" SQL injection would be analogous to reflected XSS).
How does it work? Lets say you let users pick any username. So an attacker could choose the name
'; DROP TABLE Users; --. If you naively concatenate this username into your SQL query to retrieve information about that user you have a problem:
sql = "SELECT * FROM Users WHERE UserName = '" + $username + "'";
So, how do you deal with this?
Always use parametrized querires, always, always, always. Treat all variables as untrusted user data even if they originate from the database. Just pretend everything is GET parameters, and behave accordingly by binding them as parameters.
You can also sanitize and limit the input (e.g. only allow alphanumeric usernames) before it is stored in the database as well as after it is retrieved from the database. But I would not rely on that as my only line of defence, so use parametrized queries as well.
There is nothing 'special' here. The so called 'second order' SQL injection is just the same SQL injection with the minor difference that the content is coming from within the database rather than from data entered directly by the user. The same rules apply
always sanitise input data regardless of where it comes from (the user, a file a database etc)
Never use string concatenation to build up executable commands. Use prepared statements etc.
The rule of thumb is to never trust any input data regardless of how secure you think it might be. You cannot trust what the user might enter and you need to assume that even your own data repositories (i.e. your database) may have been compromised in some way or have had 'bad data' etnered into it. Write your code with the assumption your running in a hostile environment as the reality is, you are.
BTW I see that Oracle's documentation has not improved! That is a really badly worded and poorly explained blurb regarding SQl injection.