25

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

  • 8
    Maybe I am a bit behind here, but what is "second degree sql injection"? A google search gives me only seven hits. – Anders Dec 28 '16 at 8:06
  • @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 Dec 28 '16 at 8:19
  • So, stored injections. I'd assume the mitigations are similar to stored XSS. Sanitise all inputs, which means all internal inputs, too. – schroeder Dec 28 '16 at 8:20
  • 4
    It looks to me like prepared statements (for example PHP PDO) would also perfectly protect against second order attacks as well. – SGR Dec 28 '16 at 13:54
  • 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 Dec 28 '16 at 19:11
39

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.

  • 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 Dec 28 '16 at 12:17
  • @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 Dec 28 '16 at 12:32
  • ... and why it needs to be done after being read, close to the usage. – AviD Dec 28 '16 at 13:43
  • 26
    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 Dec 28 '16 at 13:49
  • It would be nice if this answer included sanitation techniques, especially if we cannot "only allow alphanumeric characters" for things such as passwords, – XaolingBao Dec 28 '16 at 21:56
1

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.

  • Just like stored xss is xss but not directly retrieved from user input. I guess it's called persistent or stored xss because many security professionals aren't programmers and didn't think to name it second order xss. However the distinction is important when working out where in your application the vulnerable code lives when tasked with writing a patch. – wireghoul Dec 30 '16 at 12:40
  • All fine, except the question was about protecting against 'second order SQL injection', which is fundamentally the same. Have a look at the link in the OPs question. It is poorly written and bound to cause unnecessary confusion. I don't have any issue with identifying different exploits by different names (except for the trend towards sexy sounding vulnerability names done for marketing purposes), but I do dislike poor documentation of this type. – Tim X Dec 31 '16 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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