SQL Injection Lab: Think Like a Hacker

Security is IT’s top spending priority according to the 2017/2018 Computer Economics IT Spending & Staffing Benchmarks report*. Given the frequent changes and updates in vendor platforms, the pressure is on for IT teams who need to keep their infrastructures and data secure. As breaches and attacks become more sophisticated, teams will need to get creative to stay ahead of the next threat, and this includes putting yourself in a hacker’s shoes to see your system and its vulnerabilities in a new light. Following our previous hacking lab using VirtualBox and Vagrant, in this post, I will focus on how to use the lab to practice SQL injection attacks. SQL injection is often used by hackers to exploit security vulnerabilities in your software to ultimately gain access to your site’s database.

As with our previous lab, I recommend downloading and using the Kali Linux VM as your attack platform.

So, fire up your lab, set the difficulty to medium, and head to the SQL injection page!

SQL Injection

SQL Injection Submit Form

On the SQL injection page, you are presented with a User ID number selection input and a submit button.

Clicking submit returns some information about the user with the user ID selected in the input. In the example here, it’s for the admin user with ID of 1.
SQL Injection View Source
Check out the HTML source for the page by right-clicking anywhere on the page and select “view source.” Scroll down until you find the form element.

Here’s how this page works: When you select a value in the drop-down menu and submit it, the value for the selected item is sent as a POST request to the current page.

Depending on who you ask, the great or lousy thing about websites is that there isn’t a way to prevent users from sending whatever data they want.

For this reason, developers should consider all data from the browser to be malicious. This includes things like cookie values and request headers, as well as inputs. I like to consider the user interface as a nice way to use the site, though not a requirement.

SQL Injection Inspect Element

There are a lot of different ways that you can edit a value posted to a web page. One way is to use the developer tools built into the browser.

Try it out for yourself. Right-click on the input and select “Inspect Element.”

It should open the developer tools window and show the select element in the document object model, or DOM. If not, you can click the arrow beside the select element and expand it to show the options.

SQL Injection Edit Element
Double-click on the “1” in the value attribute for the first option in the select element. Edit the value by adding a space and then a single quote after the 1.

Once you’ve changed it, make sure the drop-down is set to 1 and click submit. Now, instead of sending a 1  it will send 1 ‘  which should throw an error. Because single quotes are part of the SQL syntax, adding one should throw off the query.

Throw an error, please!

SQL Injection Error
In this case, the error is returned for us to see. While this is not a best practice for developers, it is great for attackers.
The error here is basically saying that the change made to the posted value broke the query. Knowing this is your starting point for figuring out how to inject your own SQL.

Knowing what SQL to inject will initially be difficult. However, the more you learn about SQL and application development, the easier it will become.

For this example, my guess is that the query being executed is something like the following:

SELECT id, first_name, last_name FROM users WHERE id = 1 LIMIT 1

Why? I can only search for one record at the time; however, there are three fields displayed. The fields are for the user’s first name, last name, and the ID. Also, it’s a common SQL practice to use lower snake case for column names.

Another common practice is to have the table name in front of the ID. In this case, the ID column might be something like “user_id.” However, I’m not ready to look at the source code to find out.

In this case, the 1  in the “where” clause is the ID that’s being passed in from the POST body. This means that we can include the ID and some SQL, and it will be executed.

To start testing this, edit the value attribute for the option element again, setting it to 1 OR 1=1  and then submit it. If the query is similar to how I suspect it will look, then the end result will be like the following:

SELECT id, first_name, last_name FROM users WHERE id = 1 OR 1=1 LIMIT 1

The where clause here will evaluate true for all rows since the number 1 will always equal 1. Regardless of whether this query works or fails, it should provide some insight into both the application and the query design.

If multiple users are displayed on the page, then I know that there isn’t a limit of one. I’ll also know that the app just loops over all of the returned records and displays them.

Drumroll, please…

SQL Injection 1=1
The results are interesting because there are multiple records that are being displayed. This is often just a bit easier to exploit because regardless of how many records are returned, they’ll be looped over and displayed.

Also, the ID for each record shows the value that I posted, which means that the query probably isn’t returning the ID. Now, it’s time to try and paint a clearer picture of the SQL query. To do that, set the value of the option element to 1 UNION ALL SELECT 1, 2

If this works, the query will return just two columns. If this fails, it should show an error indicating that the column count doesn’t match.
Testing how many columns in the query
It worked! This means that the query is returning two columns. This helps define the constraints, so it will make it easier for us to start creating queries.

The ID isn’t being fetched from the query, there doesn’t seem to be a limit, and there are only two columns. Therefore, my new hypothesis is that the query now looks something like this:

SELECT first_name, last_name FROM users WHERE id = 1;

Knowing this, I want to try and list all of the user tables to get an idea of what other data might be available. Set the value of the option element to:

1 UNION ALL SELECT table_schema,table_name FROM information_schema.tables where table_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema');

The error message returned wasn’t what I expected. Since I know the query should work, this makes me think that the posted ID is being passed through some function to sanitize the input.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT table_schema,table_name FROM information_schema.tables where table_schema' at line 1

If the returned value is being sanitized, this means that any query with a single quote will fail. That’s only a problem for those who lack creativity.

I’ve created a crude bit of Python to be executed on the command line that will take a word as an argument and convert its ASCII value. Next, it wraps that in a char  function and concatenates the results.

python -c "import sys; print 'concat(' + ','.join(['char(' + str(ord(i)) + ')' for i in sys.argv[1]]) + ')'" someword

For example, passing in mysql  will return concat(char(109),char(121),char(115),char(113),char(108)) which MySQL will treat as the word mysql.

So the final query will be:

1 UNION ALL SELECT table_schema,table_name FROM information_schema.tables where table_schema NOT IN (concat(char(109),char(121),char(115),char(113),char(108)),concat(char(105),char(110),char(102),char(111),char(114),char(109),char(97),char(116),char(105),char(111),char(110),char(95),char(115),char(99),char(104),char(101),char(109),char(97)),concat(char(115),char(121),char(115)),concat(char(112),char(101),char(114),char(102),char(111),char(114),char(109),char(97),char(110),char(99),char(101),char(95),char(115),char(99),char(104),char(101),char(109),char(97)));

Successful query without quotes
Admittedly, that’s a pretty awful looking query, but it’s going for results rather than style. This returns a list of all the different user tables, although there aren’t many in this example.

Now that this is working, I want to try something a bit more interesting. This time, I will try to list off any MySQL usernames and password hashes.

For that, I’m going to set the value attribute to:

1 UNION ALL SELECT user, authentication_string FROM mysql.user

Running this is interesting since it returns results that include the username and password hash for MySQL users. With this, you could now take the results offline to crack them using a password cracker.

SQL Injection Union Users

All good things must come to an end

While there’s a lot more to do here, I’ll leave you here and let you keep learning on your own.

Don’t forget, user interfaces are just suggestions and errors let you know that you’ve found something interesting. If you want to explore SQL further, you can read another of our blog posts on How To Migrate Your SQL Server Database to Amazon RDS.

Happy hacking, and thanks for reading!

*Source: 2017/2018 Computer Economics IT Spending & Staffing Benchmarks report

Avatar

Written by

Ben Lambert

Ben is a software engineer with years of experience building web and mobile apps. He learned about DevOps some time ago, and hasn’t stopped talking about it since. In addition to DevOps, he’s passionate about information security, as well as virtual and augmented reality systems. When he’s not working he’s hiking, camping, or creating video games.


Related Posts

Joe Nemer
Joe Nemer
— September 15, 2020

New Content: Azure DP-100 Certification, Alibaba Cloud Certified Associate Prep, 13 Security Labs, and Much More

This past month our Content Team served up a heaping spoonful of new and updated content. Not only did our experts release the brand new Azure DP-100 Certification Learning Path, but they also created 18 new hands-on labs — and so much more! New content on Cloud Academy At any time, y...

Read more
  • AWS
  • Azure
  • DevOps
  • Google Cloud Platform
  • Machine Learning
  • programming
Simran Arora
Simran Arora
— August 21, 2020

Docker Image Security: Get it in Your Sights

For organizations and individuals alike, the adoption of Docker is increasing exponentially with no signs of slowing down. Why is this? Because Docker provides a whole host of features that make it easy to create, deploy, and manage your applications. This useful technology is especiall...

Read more
  • DevOps
  • Docker
  • Security
Avatar
Andrew Larkin
— August 18, 2020

Constant Content: Cloud Academy’s Q3 2020 Roadmap

Hello —  Andy Larkin here, VP of Content at Cloud Academy. I am pleased to release our roadmap for the next three months of 2020 — August through October. Let me walk you through the content we have planned for you and how this content can help you gain skills, get certified, and...

Read more
  • alibaba
  • AWS
  • Azure
  • content roadmap
  • Content updates
  • DevOps
  • GCP
  • Google Cloud
  • New content
Alisha Reyes
Alisha Reyes
— August 5, 2020

New Content: Alibaba, Azure AZ-303 and AZ-304, Site Reliability Engineering (SRE) Foundation, Python 3 Programming, 16 Hands-on Labs, and Much More

This month our Content Team did an amazing job at publishing and updating a ton of new content. Not only did our experts release the brand new AZ-303 and AZ-304 Certification Learning Paths, but they also created 16 new hands-on labs — and so much more! New content on Cloud Academy At...

Read more
  • AWS
  • Azure
  • DevOps
  • Google Cloud Platform
  • Machine Learning
  • programming
Alisha Reyes
Alisha Reyes
— July 2, 2020

New Content: AWS, Azure, Typescript, Java, Docker, 13 New Labs, and Much More

This month, our Content Team released a whopping 13 new labs in real cloud environments! If you haven't tried out our labs, you might not understand why we think that number is so impressive. Our labs are not “simulated” experiences — they are real cloud environments using accounts on A...

Read more
  • AWS
  • Azure
  • DevOps
  • Google Cloud Platform
  • Machine Learning
  • programming
Alisha Reyes
Alisha Reyes
— June 11, 2020

New Content: AZ-500 and AZ-400 Updates, 3 Google Professional Exam Preps, Practical ML Learning Path, C# Programming, and More

This month, our Content Team released tons of new content and labs in real cloud environments. Not only that, but we introduced our very first highly interactive "Office Hours" webinar. This webinar, Acing the AWS Solutions Architect Associate Certification, started with a quick overvie...

Read more
  • AWS
  • Azure
  • DevOps
  • Google Cloud Platform
  • Machine Learning
  • programming
Luca Casartelli
Luca Casartelli
— June 1, 2020

DevOps: Why Is It Important to Decouple Deployment From Release?

Deployment and release In enterprise organizations, releases are the final step of a long process that, historically, could take months — or even worse — years. Small companies and startups aren’t immune to this. Minimum viable product (MVP) over MVP and fast iterations could lead to t...

Read more
  • decoupling
  • Deployment
  • DevOps
  • engineering
  • Release
Luca Casartelli
Luca Casartelli
— May 14, 2020

DevOps Principles: My Journey as a Software Engineer

I spent the last month reading The DevOps Handbook, a great book regarding DevOps principles, and how tech organizations evolved and succeeded in applying them. As a software engineer, you may think that DevOps is a bunch of people that deploy your code on production, and who are alw...

Read more
  • DevOps
  • DevOps principles
Michael Dehoyos
Michael Dehoyos
— May 13, 2020

Linux and DevOps: The Most Suitable Distribution

Modern Linux and DevOps have much in common from a philosophy perspective. Both are focused on functionality, scalability, as well as on the constant possibility of growth and improvement. While Windows may still be the most widely used operating system, and by extension the most common...

Read more
  • DevOps
  • Linux
Avatar
Logan Rakai
— April 7, 2020

How to Effectively Use Azure DevOps

Azure DevOps is a suite of services that collaborate on software development following DevOps principles. The services in Azure DevOps are: Azure Repos for hosting Git repositories for source control of your code Azure Boards for planning and tracking your work using proven agil...

Read more
  • Azure
  • DevOps
Simran Arora
Simran Arora
— October 29, 2019

Docker vs. Virtual Machines: Differences You Should Know

What are the differences between Docker and virtual machines? In this article, we'll compare the differences and provide our insights to help you decide between the two. Before we get started discussing Docker vs. Virtual Machines comparisons, let us first explain the basics.  What is ...

Read more
  • Containers
  • DevOps
  • Docker
  • virtual machines
Avatar
Adam Hawkins
— October 24, 2019

DevOps: From Continuous Delivery to Continuous Experimentation

Imagine this scenario. Your team built a continuous delivery pipeline. Team members deploy multiple times a day. Telemetry warns the team about production issues before they become outages. Automated tests ensure known regressions don't enter production. Team velocity is consistent and ...

Read more
  • continuous delivery
  • continuous experimentation
  • DevOps