How to Craft a Union SQL Injection Attack

Last week, we learned how to craft a basic SQL injection attack.  This week, we'll learn how to do something more elaborate.  We are going to use a simple search request to get a database to show us all of the records in a table of users!

It's important to know how to craft attacks like these, because it helps you find vulnerabilities in your company's application.  When you find vulnerabilities, they can be fixed before a malicious user exploits them.

In order to see a Union attack in action, we will be using the awesome OWASP Juice Shop website.  This site was created by Bjorn Kimminich, and it is so helpful in learning how to test for security vulnerabilities.  Bjorn has written a companion ebook for the site, which provides explanations of various security attacks, hints for the security challenges, and answers to the challenges if you get stuck.  The attack I'll be walking you through today is from his "Retrieve a list of all user credentials via SQL injection" challenge.

Navigate to the main page of the Juice Shop site using Chrome, then click on the three dots in the top right corner of of the browser.  Choose "More Tools", then "Developer Tools" from the dropdown menu.  This will open up the Chrome Dev tools on the page.  If the Dev Tools load on the side of the page, you may want to move it to the bottom of the page instead, so the console messages will be easier to read.  You can do this by clicking on the three dots on the right side of the toolbar, and choosing the Dock Side that puts the tools on the bottom of the browser. Click on the Console tab so you will see the console messages logged as you make requests.

We will be using the Search field on the main page of the Juice Shop for our attack.  The first thing we will do is try a simple attack to see what kind of information we can get in response.

Enter into the Search field: ';  and click the Search button.
You will get an error in the console. When you click on the carats on the left to expand the console entries, you will see that the SQL query that was run was: SELECT * FROM Products WHERE ((name LIKE '%';%' OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name

Now that you know what SQL query is being run when you submit a search, you can manipulate the query.
Enter into the Search field: '))--
Let's take a moment to examine why we are entering these characters.
We are using ' to make the query think that the search value is completed.
We are using the first ) to close out the parentheses that started right after the word WHERE.
We are using the second ) to close out the second pair of parentheses that started right after the first.
We are using the -- to comment out all of the remaining text in the query.

When you submit this search value, this is the query that is run:
SELECT * FROM Products WHERE ((name LIKE '%'))-- OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name
Note that everything after the -- is ignored.

You have probably noticed that this query returned all the juices in the Juice Shop!  This is because the search is running only on the % character, and the % character acts as a wild card.

Now let's try to do a UNION between this table of Products and another table.  We will make a guess that all of the usernames and passwords are in a table called Users.
Enter into the search field:  ')) UNION SELECT * FROM Users-- 
Here we are closing out the query as we did before, but now before the -- we are adding in a UNION command that will join the Users table with the Products table.

When you submit this search, this is the query that is run:
SELECT * FROM Products WHERE ((name LIKE '%')) UNION SELECT * FROM Users-- OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name

Notice that this did not give you a response in the browser.  Take a look in the Console to see what error you got:
SELECTs to the left and right of UNION do not have the same number of result columns
What this error is saying is that the number of columns in the Products table does not match the number of columns we are asking for in the Users table.  We need to get the number of columns to match before the UNION will be executed.

We can see that there are at least five columns in the Products table, so we will start our query with that.
Enter into the search field: ')) UNION SELECT '1','2','3','4','5' FROM Users--
The numbers indicate the column numbers that we are requesting.

When you submit this search, this is the query that is run:
SELECT * FROM Products WHERE ((name LIKE '%')) UNION SELECT '1,'2,'3','4','5' FROM Users-- OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name

Note that you get the same error message as before, which means that we still don't have the number of columns right.

At this point, you'll need to keep guessing at the number of columns.  To make things easier, I'll tell you that the correct number of columns is eight.

So, submit this search:  ')) UNION SELECT '1','2','3','4','5','6','7','8' FROM Users--
This is the query that will run:  SELECT * FROM Products WHERE ((name LIKE '%')) UNION SELECT '1','2','3','4','5','6','7','8'FROM Users-- OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name

And note that at the bottom of your search results, you have a new row of values!  We are getting closer to success with our attack.

The next step is to remove the product results so it will be easier for us to see the results we really want.  We can do this by searching for something that we know doesn't exist, so we won't get any results at all.

Submit this search: FOO')) UNION SELECT '1','2','3','4','5','6','7','8' FROM Users--
This is the query that will run: SELECT * FROM Products WHERE ((name LIKE 'FOO')) UNION SELECT '1','2','3','4','5','6','7','8' FROM Users-- OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name

We know that there aren't any juices named FOO, so we won't get any response.  But because we are doing a UNION, we will still get our row of column numbers as a result.

Now we can try to get some results into our table!  When we look at the columns in our UNION, we can see that the first column is an image field.  It's unlikely that there are any images in the Users table, and our UNION needs to have each column match up by data type, so we won't try to replace the first column with any of the Users columns.  We'll start with column 2.  We can guess that the Users table probably has a column for an id, a column for a username, and a column for a password.  Let's guess that the names of these columns are "Id", "Username", and "Password".  Replace columns 2, 3, and 4 with Id, Username, and Password, like this:

Search on: FOO')) UNION SELECT '1','id','username','password','5','6','7','8' FROM Users--

Unfortunately, this search doesn't give us any results, and we don't get any clues from the console either.  We can assume that one of our column names is wrong.  What would happen if we used 'email' instead of 'username'?

Let's search on: FOO')) UNION SELECT '1','id','email','password','5','6','7','8' FROM Users--

And now we get a complete list of ids, email addresses, and passwords for every user in the Users table!

I hope that this example has demonstrated the kind of thinking that is needed when searching for SQL Injection vulnerabilities.  Security testing requires more trial and error and more patience than traditional QA testing, but if it means finding and fixing vulnerabilities and protecting your company's data, it is definitely worth it!

Introduction to SQL Injection

SQL Injection is another type of security attack that can do serious damage to your application.  It's important to find SQL Injection vulnerabilities before a malicious user does.

In SQL Injection, a malicious user sends in a SQL query through a form field which interacts with the database in an unexpected way.  Here are four different things that a malicious user might do with SQL Injection:

• drop a table
• change the records of another user
• return records that the user shouldn't have access to
• log in without appropriate credentials

To understand how a SQL Injection attack is crafted, let's take a look at an example.  Let's say we have a form in our application with a username field.  When the username field is populated with a name such as 'testerguy' and submitted to the server, the following SQL query is run:

SELECT * from users where username = 'testerguy'

If this username exists in the database, results for the users table are returned to the application.  

A malicious user will try to trick the database by 

1. making it think that the entry has terminated, by passing in testerguy' 
2. adding an additional clause, such as OR 1=1
3. adding a terminating statement such as ; to make sure no other SQL statement will be run

In the above example, what the user would add to the username field would be:

testerguy' OR 1=1;

And what the database will execute is:

SELECT * from users where username = 'testerguy' OR 1=1;'

Take a moment to think about the 1=1 clause.  1=1 is always true, so the database interprets this as selecting everything in the table!  So this select statement is asking for all values for all the users in the table. 

Let's see some SQL Injection in action, using the OWASP Juice Shop.  Click the login button in the top left corner of the page.  We are going to use SQL injection to log in without valid credentials.

We'll make the assumption that when the login request happens, a request like this goes to the database:

SELECT * from users where username = 'testerguy' AND password = 'mysecretpass'

If the request returns results, then it's assumed that the user is valid, and the user is logged in.

What we will want to do is try to terminate the statement so that all usernames will be returned, and so that the password isn't looked at at all. 

So we will send in:

1. any username at all, such as foo
2. a single quote ' to make it look like our entry has terminated
3. the clause OR 1=1 to make the database return every username in the table
4. a terminating string of -- to make the database ignore everything after our request

Taken together, the string we will add to the username field is:

foo' OR 1=1--

You may notice that the submit button is not enabled yet.  This is because we haven't added a password.  The UI expects both a username and a password in order to submit the login.  You can add any text at all into the password field, because we are ensuring that it will be ignored. Let's add bar.

Now when you submit the login request, this is what will be executed on the database:

SELECT * from users where username = 'foo' OR 1=1--' AND password = 'bar'

The first part of the request is returning all users, because 1=1 is always true.  And the second part of the request will be ignored, because in SQL everything after the dashes is commented out.  So when the code sees that all users have been returned, it logs us in!

If you hover over the person icon at the top left of the screen, you will see that you have actually been logged in as the admin!  The admin's email address was the first address in the database, so this is the credential that was used.  Because you are logged in as the admin, you now have elevated privileges on the website that you would not have as a regular user.

Obviously, this sort of scenario is one that you would want to avoid in your application!  Next week we'll take a look at some other SQL Injection patterns we can use to test for this vulnerability.  

Automated Testing For XSS

Last week, we talked about three different ways to test for Cross-Site scripting.  We looked at examples of manual XSS testing, and talked about how to use the code to formulate XSS attacks.  Today we will look at the third way to test, which is to use automation.  For today's testing, we'll be using Burp Suite, which is an oddly-named but very helpful tool, and is available for free (there is also a paid version with additional functionality).  We'll also be using the Juice Shop and Postman. 

First, let's take a look at the field we will be testing in the Juice Shop.  Using the Chrome browser, navigate to the Juice Shop's home page.  You'll see a search window at the top of the page.  Open up the Chrome Developer Tools, by clicking on the three-dot menu in the upper right corner, then choosing "More Tools", then "Developer Tools".  Once the dev tools are open, click on the Network tab. 

Do a search for "apple" in the search field.  You'll get your search results on the web page, and you should see the network request in the developer tools.  The request name will be "search?q=apple".  Click on this request.  A window will open up with the full request URL, which should be https://juice-shop.herokuapp.com/rest/product/search?q=apple. 

Next, open up Postman.  Paste this URL into the URL window, and click the Send button.  You should get a 200 response, and you should see your search results.  Now we'll set Postman to use a proxy.  Click on the wrench icon in the top navigation bar and select "Settings".  Click on the Proxy tab, then turn the Global Proxy on.  In the first section of the Proxy Server window, type 127.0.0.1, which is your local IP address.  In the second section, type 8080, which is your local port.  Postman is now set up to send requests to Burp Suite.  You may need to do one more step here, which is to turn SSL verification off.  In the Settings window, click on the General tab, and turn off the "SSL certificate verification" setting. 

Once you have downloaded Burp Suite, start the application and click Next.  Then click Start Burp.  Now Burp Suite is ready to receive requests.  Go back to Postman, and click Send on the search request that you sent earlier.  It will appear that nothing happens; this is because the request has just been sent to Burp!  Go to Burp, and you will see that the Proxy tab is now in an orange font.  Click on the Proxy tab, and then click the Forward button.  Your request is now being sent on to Postman, and if you return to Postman to check, you will see your request results.  It's a good idea to turn off the Global Proxy in Postman now, because if you forget, the next time you make a Postman request you'll wonder why you aren't getting results! 

Return to Burp Suite, and click on the HTTP tab.  (This is a sub-tab of the Proxy tab.  If you don't see the HTTP tab, make sure the Proxy tab is selected.)  You should see your GET request listed here.  Right-click on your request, and click "Send to Intruder".  You should see that the Intruder tab is now in an orange font. 

Click on the Intruder tab.  You can see that the attack target has already been set to the Juice Shop URL.  Now click on the Positions sub-tab.  This is where we choose which element of the request we want to replace. Burp Suite has guessed correctly that the element we'd like to replace in our testing is the search field value of "apple", so we can leave this setting as-is.  Now click on the Payloads sub-tab.  Here is where we will try out a bunch of cross-site scripting payloads! 

Enter in a bunch of XSS attacks into the Payload Options window, using the Add button.  Here are some suggestions:

<script>alert("XSS here!")</script>
<script/src=data:,alert()>
<IMG SRC=javascript:alert('XSS')>
<IMG SRC=JaVaScRiPt:alert('XSS')>
<a onmouseover="alert(document.cookie)">xxs link</a>

You can find many more suggestions in this XSS Filter Bypass List.  It's worth noting that if you sign up for the paid version of Burp Suite, a whole list of XSS attacks will be available to use, saving you from having to type them in manually. 

Let's start our attack!  Click the "Start attack" button in the top right corner of the application.  You'll get a warning that your requests may be throttled because you are using the free version.  Just click OK, and the attack will start.  You'll see a popup window, and one by one your XSS attacks will be attempted.

Once the attacks are finished, we can look at the attack results. There are six requests in the popup window.  The first request, request 0, is simply a repeat of our original request.  Requests 1-5 are the requests that we added into the Payload Options window.  We can see that requests 1, 2, and 5 returned a code 200, while requests 3 and 4 returned a 500.  This means that requests 1, 2, and 5 were most likely successful!  You can try them yourself by pasting them into the search field of the Juice Shop page. 

A few notes: 

• It's also possible, and more common, to use Burp Suite to intercept web browser requests directly, rather than going through Postman.  I chose to use Postman here because it's so easy to set up the proxy.  
• If you have set up Burp Suite to intercept browser requests directly, you may be able to replay your XSS attack responses directly in the browser to see them in action.  
• Another feature of the paid version of Burp Suite is the Scanner tool, which will scan for a number of vulnerabilities, including XSS.

I hope that this blog post and the two previous ones have helped you to have a greater understanding of what Cross-Site Scripting is, why it's dangerous, and how to test for it.  Next week, we'll take a look at SQL Injection!

Three Ways to Test for Cross-Site Scripting

Last week, we explained what Cross-Site Scripting (XSS) is and demonstrated a couple of examples.  But knowing what it is isn't enough- we need to able to verify that our application is not vulnerable to XSS attacks!  Today we'll discuss three different strategies to test for XSS.

Strategy One:  Manual Black-Box Testing

This is the strategy to use when you don't have access to an application's code, and when you want to manually try XSS.  To implement this strategy, you'll need to think about the places where you could inject a script into an application:

• an input field
• a URL
• the body of an HTTP request
• a file upload area

You'll also need to think about what attacks you will try.  You may want to use an existing list, such as this one:  https://www.owasp.org/index.php/XSS_Filter_Evasion_Cheat_Sheet

This cheat sheet includes lots of different ways to get scripts past any validation filters, including:

• on error and on mouseover alerts
• URL encoding
• using upper and lower case letters (to evade a filter that's just looking for "javascript" in lower case letters)
• putting a tab or space into a script so it won't be detected
• using a character to end an existing script, and then appending your own

If you are testing manually, a systematic approach is best.  Locate all of the places where you could inject a script, choose a list of attacks you'd like to try, and try each attack in each place.  While you are testing, you may also gain some insight of how you could change the attack by what kind of response you get from the attack.  For example, if your script tag is stripped out by validation, you could try to encode it.

Strategy Two:  Look at the Code

This is the strategy to use if you want to test manually, and you have access to your application's code.  By looking at the code, you can determine the best way to craft an attack script.  This also works well for testing file uploads; for example, if your application's code lists file types that are not allowed, you may find some types that have not explicitly been blacklisted, and you can see if you can upload a script using one of those types.

Let's take a look at how you can use an application's code to craft an attack.  We'll be using the XSS Game again; we'll be doing Challenge 3, and in order to access it you need to have solved Challenges 1 and 2; so take a look at last week's post to see how to do that.  

As you look at the website in Challenge 3, you see that there are three different tabs, each of which displays a different image when clicked on.  Take a look at what happens in the URL each time you click on one of the tabs.  When you click on the Image 2 tab, "#2" is appended to the URL.  When you click on the Image 3 tab, "#3" is appended to the URL.  What happens when instead of clicking on a tab, you type "#2" into the URL?  Unsurprisingly, you are taken to the Image 2 tab.  What happens when you type "#5" into the URL?  There is no Image 5, but you can see that the page displays the words "Image 5".  What happens when you type "#FOO"?  The page displays "Image NaN" (short for "Not a Number").  You have probably figured out now that the end of the URL is the place that you are going to inject your malicious script.  

Now, let's take a look at the code!  Click on the "toggle" link next to the words "Target Code".  This will display the code used for the webpage.  Look at line 17; it shows how the URL for the image tag is created:

"<img src='/static/level3/cloud" + num + ".jpg' />";

The "num" part of this image tag is a variable.  The value of the variable is taken from what we are sending in the URL.  If you send in a URL with "#3", then the image tag will be
cloud3.jpg
If you send in a URL with "#FOO", then the image tag will be
cloudFOO.jpg

Our task now is to see how we can inject a script using this "num" variable.  Recall that in last week's post, we did some Cross-Site Scripting where we made it look like we were uploading an image, and we included an alert that would display when there was an error uploading the image. And we set things up so that there would always be an error, because we weren't really uploading an image at all.  We are going to do the same thing here.  

Let's craft our URL.  We will begin with

https://xss-game.appspot.com/level3/frame

because this is how the URL always starts.

Next, we'll add

https://xss-game.appspot.com/level3/frame#3

because we want to make it look like we are following the pattern of choosing an image number.

Now we'll add

https://xss-game.appspot.com/level3/frame#3 '

because we want to trick the code into thinking that the image URL is completed. This means that the code will try to load an image called "cloud3" instead of "cloud3.jpg", which will generate an error.

Now we can add our on-error script:

https://xss-game.appspot.com/level3/frame#3 ' onerror='alert("Hacked!")'

When the alert is triggered, a popup window will appear with the "Hacked!" message.

Let's try it!  Take the entire URL:

https://xss-game.appspot.com/level3/frame#3 ' onerror='alert("Hacked!")'

Paste it into the URL window, and click the "Go" button.  

You should see the popup window appear, and you have solved the challenge!

Strategy Three: Use a Security Testing Tool

As you can see from the example above, crafting an XSS attack takes a little time.  You may have many places in your application that you need to test for XSS, and not much time to test them.  This is where automated tools come in!  With an automated tool, you can send hundreds of XSS attacks in less than a minute.  In next week's blog post, we'll take a look at how to use an oddly-named tool called Burp Suite to automate XSS attacks!

What is Cross-Site Scripting, and Why Should You Care?

In discussions about security testing, you have probably heard about Cross-Site Scripting (XSS), but you may not have a good definition of what it is.  Cross-Site Scripting is an attack in which a malicious user finds a way to execute a script on another user's website.  Today we'll learn about two different kinds of XSS attacks, do a hands-on demo of each, and discuss why they are harmful to the end user.

Reflected XSS:

Reflected XSS is an attack that is executed through the web server, but is not stored in the code or the database.  Because the attack is not stored, the owner of a site may have no idea that the attack is happening.

In order to demonstrate this attack, we'll go to a great training site from Google called the XSS Game. This site has a series of challenges in which you try to execute XSS attacks.  The challenges become increasingly more difficult as they progress.  Let's try the first challenge.

On this page, you see a simple search field and button.  To execute the attack, all you need to do is type
<script>alert("XSS here!")</script>
into the text field, and click the button.  You will see your message, "XSS here!", pop up in a new window.

What is happening here is that you are sending a script to execute a popup alert to the server.  The client-side code does not have appropriate safeguards in place to prevent a script from executing, so the site executes the script.

You might be thinking "This is a fun trick, but how could a malicious user use this to hack me?  I'm typing into my own search window."  One way this is used is through a phishing link.  Let's say that you are the owner of a website.  A malicious user could create a link that goes to your site, but appends a script to the end of the URL, such as
?query=%3Cscript%3Ealert%28%22XSS%22%29%3C%2Fscript%3E. 
(This is simply the attack we used earlier, with HTML encoding.) The malicious user could send this link in an email to an unsuspecting visitor to your site, making the email look like it came from you.  When the person clicks on the link, the script will navigate to your site, and then execute the popup script.  The malicious user will craft the script so that instead of containing the message "XSS here!", it contains a message that encourages the visitor to interact with it, in order to obtain the user's account number, or other sensitive information.

Stored XSS:

Stored XSS is an attack where the malicious script is actually stored in the database or code of a website, so it executes whenever a user navigates to the page or link. This could happen if the creator of the site did not put adequate input sanitization in the back-end database.

We'll take a look at how to craft this attack by looking at the second challenge of the XSS Game.  (In order to see this challenge, you'll need to have solved the first challenge, so follow the instructions above.)

In the second challenge, you are presented with a chat app.  To solve the challenge, you need to add some text to the application that will execute a script.  You can do this by typing in
<img src='foobar' onerror='alert("xss")'>

As soon as you submit this entry, you should see a popup window with the "XSS alert!" message.  And not only that, if you navigate away from this page and return to it, you will see the popup window again.  The attack has been stored in your comment on the chat page, where it will cause a popup for any users who navigate to it.

Let's parse through the script we entered to see what it's doing:

<img src='foobar' onerror='alert(“xss”)'>
The items in red indicate that we are passing in an image element.

<img src='foobar' onerror='alert(“xss”)'>
The section in blue is telling the server what the source of the image should be.  And here's the trick- there is no URL of foobar, so the image cannot load.

<img src='foobar' onerror='alert(“xss”)'>
The section in green is telling the server that if there is an error, that a popup window should be generated with the "xss" text.  Because we have set things up so that there will always be an error, this popup will always execute.

One way that stored XSS might be used is to spoof a login window.  When a user navigates to a hacked site, they will be presented with a login window that has been crafted to look authentic.  When they enter their login credentials, their credentials will be sent to the malicious user, who can now use them to log in to the site, impersonating the victim.

Next week, we'll discuss more ways to test for XSS attacks!