By the time this is posted I will be 1 year into this journey of learning on my path to the OSCP certification. It’s kind of amazing how much I have learned already, and I have so much more to learn. I didn’t expect to enjoy attacking applications and servers. I am a paranoid and cautious person, and I really don’t like breaking things. I wanted to be a protector, not an attacker. I wanted to get better at defensive security, and I figured it would be useful to look at the offensive tactics to understand how they work and what they look like. And then it occurred to me, pentesting and red teaming is not about breaking things. Well, it is, but it’s about breaking things in controlled fashion before an adversary can break things and cause harm. This is all a rambling way of saying; I really love doing this stuff and I have found a way to rationalize it with my core beliefs and values. This is part 6, if you are just joining the series, you can catch the last post here.
Today I want to talk about what I have learned about SQL injection. SQL injection is part of the injection category of the OWASP top 10 web application security risks. It’s one of the oldest attack types out there and will likely continue to be a problem as long as there are databases behind applications. What makes SQL injection such a challenge is that it can show up in a lot of different ways.
SQL injection can be used to bypass authentication on a vulnerable login page by tricking the application code to skip the verification of a username or password. If you see ‘ OR 1=1 — in your logs, someone is trying to do a bypass. That weird looking string might be the first SQL injection that hackers learn and commit to memory, it’s a beauty. When passed as a password, if the developer was not careful about sanitizing their inputs, hashing values on the client side, and parameterizing their queries, that magical string can tell the SQL engine to return the records where the password equals something or true. Because of the wonders of Boolean logic, that string of characters can mean “let me in without knowing a password”.
In other situations, SQL injection can be used to extract data from a database. In much the same way that it is possible to get code execution through template injection, it is possible to combine commands in the SQL engine to get an applications to dump information about itself that is not meant to be seen, or the entire database. This is the kind of exploit that you read about in the news from time to time.
Some time back I was doing a CTF challenge and got to try out sqlmap for the first time. Sqlmap is a fantastic tool that automates SQL injection attacks. SQL injection can be done manually of course, but it’s very tedious. To use sqlmap I just feed it the URL for the page I want to attack, push a couple buttons, and watch it work, a few moments later, it tells me everything I need to know about the database and either dumps the database, or maybe I get shell execution. Sqlmap can cause serious damage to the database. ALWAYS have permission before pushing these buttons, and maybe try it out on a developer or testing version of the application instead of production. Good? Everyone understand that playing with the toys is fun, but it must be done in a controlled manner? Awesome, onward!
Most of the time, SQL injection is about using whatever information the system returns after some action to inform the next action. It might be error messages, or the results of the query in a web page, or something like that. Sometimes though, there is no feedback. I can tell that there is some injection vulnerability, but I can’t tell what it is doing. A clever developer might think that they have defeated the hackers by not providing any feedback. But hackers are a clever bunch and can use a time-based attack to figure out what is going on. It takes longer but can be done.
Time-based Blind SQL Injection
Most database engines have sleep commands built into them for some crazy reason. I cannot think of a good reason why I want my query to just stop and wait for a few seconds during normal operations, but I can think of ways to use that for an attack. Let’s say I am trying to figure out the password for a user. I know the table and column where the password is stored, but I can’t see it. I can put together a query that checks every single letter of that password to see if it matches a particular letter that I specify. If the letter matches, pause for 5 seconds before completing the process.
This is an attack of patience. Doing this kind of attack manually is not practical, or fun. Thankfully I know Python. Any programming language should work, but Python is my go-to language. It helps to know what kind of characters are being used, but this is a brute force attack so just trying all the characters can work. Now feels like an excellent time to build some stuff. Also, I’m just going to put it out there that Set Solutions is going to be hosting a CTF in October for Cyber Security Awareness Month, and I am helping to write some of the challenges. Wink, wink.
I added some new logic to the ContentOverload application from a few posts back, which I haven’t touched in a few months. It’s been a while since I worked with databases in Python, so I had to look up the libraries and tutorials to see how to get everything working. By just following the tutorials I accidentally blocked myself from my SQL injection attacks, so I had to go back deliberately break things to make this work.
Once everything is pushed out to the server, I can get to work putting the attack together. I am writing this as I develop the attack. I have only done a few proof-of-concept things so far to make sure I don’t end up in one of those situations where I have to pivot off topic because I messed something up. *fingers crossed*.
So, I have this application, I can register a new user. I want to get the password for the “admin” user for this system. I think there is an admin user, and I know that when I log in with the wrong password, I get this error message:
So, the IP is being logged. That is probably coming from the headers for the request, so that is where I am going to start. In all attack scenarios, the first step is recon. What is the server type, what are the tables and those other pesky details that make this process work? The syntax of SQL Server, MySQL, Postgres and all the others are just different enough to matter. The good news is that the lazy developer that coded this app (me) left some error messages that can help point the way.
I first need to get the system to either change its response or give me an error message. I’m going to start with the assumption that the X-Forwarded-For header value is vulnerable for the attack. I could fuzz this, but I don’t want the post to be too long and there is a lot to get through. I fire up Burp Suite Community Edition capture the login post request and make a tiny adjustment, adding “X-Forwarded-For: 10.0.0.1’” that single-quote is meant to provoke an error. For most things, a HTTP 500 Server Error is bad news, for an attacker though it very helpful even if it doesn’t contain stack traces. That error means I broke something, in this case, that something about the header value is being used in the code, and I managed to break that code.
The single quote ends a “string” value. I would expect that there is some query being run, and the IP address is passed to that query. The next step is to try to get this injected value to NOT return an error message. This is where experience and experimentation come into play. I added a single quote to my value, which means I probably need to leave room for the system to add another quote at the end of whatever I type. So, I send a payload like this “10.0.0.1′ UNION SELECT NULL WHERE ‘1’=’1”. Note the missing single quote at the end. The request completes without the HTTP 500 error, so we are in business. Again, I don’t know what they system is doing, but it’s kind of working so I’m not going to argue.
I am after an admin password, as you may remember, so I need to find a users table. I’m going to add a small change to the payload to select NULL from users. If the users table exists, this should complete without error. If I try to select NULL from a table that doesn’t exist, I will get a HTTP 500 error, like this:
I don’t get an error, so I know there is a users table. I can use this type of trial-and-error process to work through column names as well. As this is a modern app, the person who wrote it may have read tutorials about how usernames and passwords should be stored. A little extra attention to recon reveals that there is a column called “salt”. Uh oh.
I’m going to have to be extra clever here. Not only do I have to extract a password value, but I also have to extract a salt value as well. If this were a live CTF challenge, I would be more than a little nervous at this point. Salt could mean hashed passwords, but it could also mean that these passwords are encrypted. I’ll have to keep digging to find out the answer to that question.
Next, I need to confirm the target exists. This is the first time where a time-based query is going to be needed. I need some way to compare values and return a yes or no answer that I can see. What I want to create is a query that checks for the existence of a record in the users table where the name of the user is “admin”. To determine the results of the query I am going to add a delay for a few seconds if the admin user is found.
The payload looks like this “10.0.0.1′ UNION SELECT sleep(5) FROM users WHERE name=’admin’ AND ‘1’=’1”, and sure-enough I get a 5 second delay on the response. Just to make sure the logic is working I do a quick check for admin2, and the query comes back instantly. I’m good to go. I am going to need to write some code to automate the extraction process. I am pretty sure I am working with MySQL, and I know that there is a users table, with a password field, and a salt. I can also confirm that the password I want for the admin user should be available.
Automating stuff with Python
Up until this point I have been manually editing the request in Burp Suite and sending it along to the server to process. The next step must be done with code. I am going to extract the password and salt by asking the if the first letter matches A, then B, then C, all the way through all the letters and numbers in the key space, then repeat that for each letter in the password and salt. I do not know how many characters there are in these values. The code for this extraction is below. (It may be a little mangled … but I have to leave some work for others to sort out).
After the script runs, I get two values. The password is: “fd91f0a79814523f3cfc4f9165f16fb8517166b99ecd2845282913373d0aa3ec”. The salt is “40fad6c1b4f796ab5bb525d4bc927db8690ae07ed59eec9db8de3e1f80442b7f”. Those are hex values, and if I plug those into the Name-That-Hash tool, it tells me that the password is a SHA-256 hash.
Maniacal giggling commences.
Alright, I am off the reservation with this challenge. In fact, this entire post is a bit of an OSINT challenge for anyone who might be playing our CTF later this year. When the blind SQL injection challenge shows up there, it’s going to look a little different, but the concept will be the same. We want the CTF to be approachable for newcomers, but I want a few of the challenges to be a bit of a head-scratcher as well. Also, I think it is good to talk about how this might be done in the real world. Developers aren’t just dumping plain text passwords in their databases, are they? They aren’t, right?
Salting user passwords adds a little bit of complexity and protection against a user’s password being compromised in bulk. The idea is that a bit of random gibberish, the salt, is added to the user’s password. The combined value of password + gibberish is hashed and stored in the database. The salt is not sensitive and is stored right next to the password usually. This extra randomness makes sure that if two people have the same password they will not have the same hashed value stored in the database. The user doesn’t know it’s happening and neither does an attacker in most cases.
I have the salt, and I have the hash, I need to figure out how to crack the original password, and for that I am going to use HashCat. I have cracked salted hashes before, but it is usually hashes where the salt is part of the hash itself. I need to do a little digging and experimenting to sort out how to do this. It took two attempts and a little reading to get it to work. These tools really are quite fun!
That’s it. The password is cracked. The admin was a dolt and use pass@word1 for their password. That isn’t too far down the rockyou word list, and it only took a few seconds to crack. Salted passwords offer some protection against attacks against a large list of passwords, but they can’t really help a weak password from being cracked individually.
Prevention and detection of SQL injection
Prevention of SQL Injection attacks starts during development. Never write SQL statements by concatenating untrusted values from the user. You might ask, what values are untrusted? All of them. Everything is untrusted until it is validated. Developers cannot trust the user, browser, cookies, database, or anything really. Everything must be validated, every single time. As developers we must be paranoid, the fact that we put something in the database in a clean state doesn’t mean it will still be clean when it comes back. A little bit of time messing around with Burp Suite and other tools should be enough to understand that literally anything can be messed with before it gets to the application.
Back in the day I remember fighting with my code to reduce page response time to less than half of a second. This was important at the time because the sales and marketing people were blathering on about the correlation between conversion ratios and page response time or some other non-sense. While it is true that users want things to be fast, I would like to think that most users understand that speed is second to security. I would like to think that, but I also remember all the tickets about users complaining about how slow the application felt. Login operations and the processing of data doesn’t need to be lightning-fast; they do need to be safe and accurate. Write the regex, do the extra validations, use hundreds or thousands of hashing rounds to slow things down and get it right. Start with safe, then if it is necessary and possible, make safe faster. I am not advocating for slow applications and un-optimized processes; I am saying that 2-6 seconds to process a login is fine, as long as you don’t leak my password.
This picture above is from the login process for my vulnerable application. Two queries are shown, the parameterized insert into the logs table, and the ridiculous (and somewhat useless) select statement that I shoved in there just to make it vulnerable. The INSERT statement is fine, the SELECT statement is utter garbage. Also, I would like to note that the FROM not being capitalized in the SELECT statement is lazy and I feel bad about that, but not enough to fix it and re-take the screenshot. This is a religious debate, and I am right. If you think differently, that is OK.
If I had parameterized the SELECT statement, the injection would not work, or at least would not work without a lot more effort. Every language has best practice guides for how to do parameterized SQL statements to protect against SQL injection. Stored procedures and functions are also useful when implemented correctly.
Once secure code is written the next step in the defense is going to be logging and paying attention to what is going on in the logs. As you remember, I used a time-based SQL injection attack. I artificially increased the time it took for the page to load. The response time for a request is usually included in the default web server access logs. Measuring and tracking the time it takes for a page to respond, would allow an analyst to identify that an attack was going on, and do something about it. Good application logging will help pinpoint what part of the page is being attacked, and how to correct the problem.
Lastly preventing error messages whenever possible is a very good way to prevent an attacker from figuring out how to craft their payloads. Without the helpful HTTP 500 errors it would have been very difficult for me to even know that that vulnerability existed, and I probably wouldn’t have been able to exploit it. Good error handling so that serious errors are not allowed to leak outside the application will make it more secure. Nothing makes an attacker happier than seeing an error message with a stack trace. Deprive the attackers of their joy, and they just might move along to another target.
I mentioned the sqlmap tool earlier in this post. After I got through my exploit, I ran sqlmap against the login page just to see what would happen. Sqlmap was unable to exploit the vulnerability in the X-Forwarded-For header. That doesn’t mean that it couldn’t do it, just that it didn’t do it out of the box. The sqlmap tool is kind of a no-no outside of CTFs, because it can be destructive. It runs a LOT of requests to try things out, and most of them are easy to spot in the logs. I would encourage developers and internal red teams to use a tool like sqlmap internally, on a test box to see how it works and what it finds. Again, unless you are Netflix and thrive on chaos, I do NOT recommend running these tools on live production applications.
I watched Burp Suite’s automated scan completely wreck a practice lab from Hacker101’s CTF. It added so much garbage to the application that I had to reset the lab to work out what it did. It was very instructive about what can happen if you blindly let an automated scanner loose during an engagement.
That is going to bring this post to a close. I hope you enjoyed this as much as I did. SQL injection is fun for me because I get to write code to solve problems. This technique is not easy to entirely automate, it is always a bit of a puzzle that must be worked out. That kind of thing makes me happy, but it may not be for everyone’s happy place.
In the next post, I’ll be doing a bit of post-exploitation and privilege escalation. I don’t yet have a plan for how that is going to work, so it should be a very interesting post. It is also coming up on pumpkin season, so it may be time for the return of the pumpkin cookie milestone rewards.
***
This blog was written by Greg Porterfield, Senior Security Consultant at Set Solutions.