OWASP WebGoat SQL advanced lesson 5

Last week I wrote about the OWASP WebGoat XSS lessons. Today I’d like to write a few pointers on how to solve the SQL injection (advanced) lesson 5. The goal is simple: you are presented with a login box and given a username; log in as that user.

The usual username’ OR ‘1’=’1 — unfortunately doesn’t work (that would have been too easy!), and since the last slide talked about blind SQL injection, it was pretty obvious that this was the way to go. After a few unfruitful hours trying to use SLEEP and UNION on the register new user page I started looked online for a hint. What I found was to register a new user, and then register it again with either TRUE or FALSE:

Register new user: testuser
Again register the same user but like this: testuser’ AND ‘1’=’1
Which equates to “testuser and TRUE”. When registering this user, pay attention to the server reply:

User testuser’ and ‘1’=’1 already exists please try to register with a different username.

Now try registering: testuser’ AND ‘1’=’2
Which translates to “testuser and FALSE”. The server now replies:

User testuser’ and ‘1’=’2 created, please proceed to the login page.

At first I thought it literally created the user “testuser’ and ‘1’=’1” but no, it still creates only “testuser” but apparently the added 1=1 means “user already exists”, as opposed to 1=2 meaning user doesn’t exist, although it does (we already created user “testuser”).

Again, since the last slide talked about extracting the database version string with substring(database_version(),1,1)=’1 I tried tagging that on to the username instead of ‘1’=’1. Iterating through all 10 numbers for the database version, I got 2 as the only TRUE statement. Using this technique, I was able to find 2.3.4 as being the database version.

So what other kind of information can we extract using substring? I first tried ‘user’ and ‘username’, but got an error saying the string didn’t exist. ‘userid’ worked though, but I saw no need for that information. In a flash of inspiration I tried ‘password’, and lo and behold, the first letter that equated to TRUE was ‘t’:

tom’ AND substring(password,1,1)=’t

User tom’ AND substring(password,1,1)=’t already exists please try to register with a different username.

As we saw before, ‘1’=’1 equals TRUE and returns “user already exists”, so if the above command returns the same, it must equal TRUE as well. We now have the tool to extract Tom’s password!

At this point I got tired of going through all characters manually and fired up Burp and configured BURP Intruder for a sniper attack. There’s only 1 parameter to fuzz, the very last letter in the string. This is very easy to do with Intruder with the following settings:

Attack type: Sniper
Payload: Brute Forcer
Character set: abcdefghijklmnopqrstuvwxyz0123456789
Min length: 1
Max length: 1

To find a hit, sort the results by length. A successful hit is slightly longer than a miss.

Each time I got a hit I changed the start position of substring (2nd parameter) and ran intruder again. Within half an hour I had poor Tom’s password!

13 thoughts on “OWASP WebGoat SQL advanced lesson 5”

  1. How did you discover the database version number? I understand that by iterating the ‘1 in testuser’ and substring(database_version(),1,1)=’1 gave you 2 but were did you discover the .3.4?

    1. Hello! You find the subsequent numbers of the version by incrementing the second parameter of substring, i.e. substring(database_version(),2,1)=’. The syntax of substring is:
      SUBSTRING(string, start, length).
      I hope this helps!

      1. thanky you for posting this as it’s the only resource on the internet. For database enumeration, did you use burp too?

    1. Which part? The intruder? If you intercept the request in Burp you can right click it and send it to intruder, using the options I wrote in the article. Then you let intruder run until one of the replies indicates a hit, and change substring to the next index as I wrote in the above comment.
      However, you could also write your own script to automate this instead of using Burp.

  2. How can I reset registered user information?
    Even if I try to register any name, it says “User tom ‘AND substring (password, 1,1) =’ ● already exists please try to register with a different username. ‘

    1. Hmm you shouldn’t need to register any new users. The attack is done on the login form, not the register form.
      To completely reset WebGoat you could always just remove all its folders and run it as new. This would of course wipe your progress, too, so be careful.

      1. Not sure what you are saying here. Your explanation is really good and helpful, thanks a lot for that, but we are attacking the register form, not the login form. Don’t you agree?

        1. Sorry, you are right. It’s been several months since I did this exercise.
          Are you using a space between the quote and the AND? It’s possible it literally registers the user “User tom ‘AND substring (password, 1,1) =” and not user “tom”. Also, you are using the single quote (ASCII 0x27), right? It’s doesn’t seem like you are getting SQL injection.
          If none of this helps, you should try resetting WebGoat by removing all its folders though as I said, all your progress would be lost.

  3. Hi,

    Your solution is excellent. Getting the idea in the first place is quite difficult. However, I wanted to ask you, how did you come up with the character set for the password? Did you just guess it? What if the password had special characters?

    1. If the password had special characters I wouldn’t get a hit on that character position, and I’d have to expand the character set with special characters, which would make the brute forcing take longer. So it wouldn’t be any more difficult to brute force, just take longer.

  4. This paragraph **At first I thought it literally created the user “testuser’ and ‘1’=’1” but no, it still creates only “testuser” but apparently the added 1=1 means “user already exists”, as opposed to 1=2 meaning user doesn’t exist, although it does (we already created user “testuser”).**

    How did you ensured that payload “testuser’ and ‘1’=’1” is not created as user and only testuser is actually taken??

    What is guessed is, first they must have checked if testuser username exist using select statment . which will always return false becoz of ‘1’=’2. so next they must have inserted user input (payload) testuser’ and ‘1’=’1 in db to create new user

Comments are closed.