Second order SQL injection protection

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.