current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Information Security Stack Exchange is a question and answer site for information security professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 18 down vote favorite
1

Normal SQL injections are no problem since I always use prepared statements, but how to protect oneself from second order SQL injections?

share|improve this question
6  
Maybe I am a bit behind here, but what is "second degree sql injection"? A google search gives me only seven hits. – Anders yesterday
    
@Anders I'm not 100% sure myself, but from what I understand is that the SQL injection code stays stored in the database. That code gets used in the a later point of time and somehow injects itself successfully. See download.oracle.com/oll/tutorials/SQLInjection/html/lesson1/‌​… and stackoverflow.com/questions/12952187/… for more information. – J. Smith yesterday
    
So, stored injections. I'd assume the mitigations are similar to stored XSS. Sanitise all inputs, which means all internal inputs, too. – schroeder♦ yesterday
4  
It looks to me like prepared statements (for example PHP PDO) would also perfectly protect against second order attacks as well. – SGR 19 hours ago
1  
The term makes me think that if we somehow differentiate an ordinary SQL injection, we'll have second-order SQL injections. Later we'll find a generic solution to n-th order SQL injections. – kevin 13 hours ago
up vote 30 down vote accepted

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.

share|improve this answer
3  
Re #1, validation and sanitization should always be done before using it in a query - even if it was retrieved from the database, and even if it was sanitized before storing in the database, it should still be re-validated and re-sanitized (according to usage) after reading from db. – AviD♦ 20 hours ago
    
@AviD In this example you might want to allow users to use sensitive characters in usernames, so there would be no sanitation - but I guess that is where "according to usage" comes in. – Anders 20 hours ago
    
... and why it needs to be done after being read, close to the usage. – AviD♦ 19 hours ago
22  
Option two is not an option, it's just correct coding. Queries should never be assembled by concatenating strings. The way I use to explain that is "raw strings" and "SQL code" are logically different types and adding them is like adding kilograms to meters. – Kos 19 hours ago
    
@AviD Thanks for the feedback! Hopefully my edit reflects your point, otherwise feel free to suggest improvements. – Anders 18 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.