26

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

Anders
  • 64,406
  • 24
  • 178
  • 215
J. Smith
  • 385
  • 1
  • 3
  • 6
  • 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 08: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 http://download.oracle.com/oll/tutorials/SQLInjection/html/lesson1/les01_tm_attacks2.htm and https://stackoverflow.com/questions/12952187/how-do-i-demonstrate-a-second-order-sql-injection for more information. – J. Smith Dec 28 '16 at 08: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 08: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

2 Answers2

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.

Anders
  • 64,406
  • 24
  • 178
  • 215
  • 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
  • @XaolingBao That is way out of scope for this question. Plus, if you use parametrized queries you do not need to sanitize for SQL. – Anders Dec 28 '16 at 22:02
  • @Kos Your comment overdoes it, sometimes it's unavoidable. For instance, when you have a dynamic filter option, you may build a query dynamically as (pseudo-code) `$sql = "SELECT * FROM users WHERE 1=1"; $params = []; if ($nameFilter) { $sql .= " AND name LIKE ?"; $params[] = $nameFilter; } if ($countryFilter) { $sql .= " AND country = ?"; $params[] = $countryFilter; }` Now, the query string is built by concatenating strings, exactly what your comment says you shouldn't do. But: the query string is built by concatenating *trusted* strings. All untrusted data is in the parameters. – hvd Dec 28 '16 at 23:05
  • @Kos That's a bad analogy except from a type systems perspective. For example, you can't add kilograms to meters and get anything useful, and most people can't think of a situation where it would possibly be a useful thing to do. However, you can get something useful from concatenating escaped strings and SQL code - specifically you get a valid SQL query. – user253751 Dec 29 '16 at 02:31
  • @immibis hold on, my point is that you first need to do a type conversion (from raw string to SQL literal representing that string) before you're allowed to add. The type conversion is done by quoting and escaping. Does that sound better? – Kos Dec 29 '16 at 09:07
  • @Kos But you used the analogy to explain that "queries should never be assembled by concatenating strings", not "queries should never be assembled by concatenating unescaped strings". – user253751 Dec 30 '16 at 02:55
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.

Tim X
  • 3,242
  • 13
  • 13
  • 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 07:45