Watch this week's video on YouTube
One misconception some people have about SQL injection is that it can only happen when concatenating a user input parameter directly into your dynamically built query string:
While this type of injection flaw is easy to spot, there are other less direct ways an injection attack can occur.
Second Order SQL Injection Attacks
SQL injection attacks that delay execution until a secondary query are known as "second order".
This means a malicious user can inject a query fragment into a query (that's not necessarily vulnerable to injection), and then have that injected SQL execute in a second query that is vulnerable to SQL injection.
Let's look at an example.
Doggo Pics
Imagine a website where dog owners can share pictures of their best friends:
The internet is a great place
Now imagine that users of Doggo Pics can set a preference for what order they view pictures when they visit the website:
When a user saves their sorting preference, the sort column and order get saved to a preferences table in the database:
No SQL injection vulnerabilities here
The next time the user goes to view the page, the doggo pictures will be sorted based on their saved preferences. This works because the query pulling the pics/descriptions is dynamically sorting the data based on the user's preference:
We are dynamically sorting based on the user's preference
Viewing doggos sorted by longest tongue length
The above flow is how the website is supposed to function. So how does a malicious user inject SQL code into these queries if the only query they directly save input into is the UpdateSortOrder procedure?
The Attack
So the developer of Doggo Pics was too busy enjoying pictures of doggos to implement any type of input validation on sort order preferences. This means a hacker can do something like inject a SQL statement onto the end of the sort order dropdown:
The hacker modifies the "desc" value to include an INSERT statement
When our dbo.UpdateSortOrder procedure executes on the backend, it looks like this:
See where this is going? Now when our stored procedure that dynamically sorts the picture data executes, the hacker's INSERT statement is going to execute as well:
What happens next is simple: The first time our malicious user goes to view the Doggo Pics, they receive the pictures in their preferred sort order. Additionally an INSERT INTO statement executes back on the server.
The second time the user views the Doggo Pics page, the values from that previously ran INSERT INTO statement are now visible on the screen:
So even though the first query the user encounters (saving sort order preferences) is entirely SQL injection free, our second order SQL injection attack occurs when our second SQL query dynamically executes the injected code that was stored in our user preferences table in the database.
How do I first and second order SQL injection attacks?
I recently presented at the GroupBy conference where I showed exactly how to protect your data from these types of attacks.
My presentation was recorded and is available for you to watch on YouTube:
https://www.youtube.com/watch?v=qrOLg3wjjOs
You can also read more about different types of SQL injection attacks and preventative solutions by reading through my blog archives.