sql injection lab assignment

Lab 6: Exploits: SQL Injection

Introduction to sql, sql examples, sql injection, input validation and sanitization, additional reading on sql injection, diff and patch: see differences and create source patches, mysql: command line mysql client, frobozzco community credit union, submission instructions.

The purpose of this exercise is to introduce you to SQL Injection attacks and give you a first-hand opportunity to see them in source code, exploit them, and patch them. After successfully completing this exercise, you will be able to:

• Accurately identify and describe SQL Injection attacks
• Identify SQL Injection vulnerabilities in a preexisting PHP/MySQL application
• Understand how vulnerabilities can lead to unauthorized access to private data
• Repair simple examples of these security flaws
• Author memos describing in detail your findings and code changes

You should be familiar with the Unix command line, POSIX permissions, and basic programming. The exercise will use PHP and SQL, but at introductory levels.

The Structured Query Language

SQL -- or the Structured Query Language -- "is a computer language designed for the retrieval and management of data in relational database management systems, database schema creation and modification, and database object access control management"[ 2 ]. Put simply, it's a database query language. SQL (pronounced "sequel" or S-Q-L) has been around since the 1970s, and was standardized by ANSI in 1986. SQL is ubiquitous -- practically all current relational databases in use today speak some vendor-specific variant of SQL, and most enterprise web applications use a relational database on the back end. Furthermore, scripting languages like Python, Perl, Ruby, and PHP that are often used for web development and all have robust, easy to use SQL modules. In fact, the combination of L inux servers, the A pache http daemon, the M ySQL relational database, and the P HP scripting language are so popular for web development today that it has its own acronym -- LAMP .

Dedicated database servers are valuable because they free programmers from the task of creating customized data storage systems from scratch (which are likely to have bugs and shortcomings). SQL serves as a common language for many database systems, allowing programmers to ignore most details of the specific database system in use (e.g., MySQL, Oracle, PostgreSQL, etc.). Overall, this modularity improves performance and makes maintenance and portability easier.

Additionally, general-purpose database systems like MySQL are a win because the performance critical code is written in a fast language like C or C++, while applications using the database can be created with a slower, user-friendly scripting language. This is especially important when databases contain millions of entries because a database written in a relatively slow scripting language would be prohibitively expensive from a computational and time perspective. SQL servers can run on the same machine that serves the application, or they can be accessed via the network, allowing a web frontend and database backend to operate on separate machines. This flexibility can enable more efficient use of computing resources and can add other other benefits, such as easier backups.

For example, an online retailer might have a database table for all their inventory with the columns product number, name, price , and number in stock . The programmer can craft a database query requesting all products whose name s' second letter is an "x" like so:

We could also perform a query filtering out the 10 items with the cheapest price :

We could also ask for just the names of items whose price is greater than 100:

SQL results are typically returned to the programming language as a list (array) or similar data structure. The programmer can then work with the list in her favorite language, make further queries, or display the data in some way. In executing queries like this, the SQL database -- not the application -- performs the selection, filtering, and alphabetization. Not having to write new code to do this (and more) saves application developers a tremendous amount of time.

Because small flaws can often be leveraged into much larger exploits, simple programming mistakes and omissions often result in unexpected negative security effects. For example, PHP is every bit as vulnerable to failures of input validation such as filesystem and directory traversal exploits as are Perl and Python because these vulnerabilities are a result of system semantics and the necessity of programs to be capable of performing general purpose tasks like reading files.

Poorly written applications that interface with SQL are no different. A common class of attacks are called SQL injection attacks which -- like directory traversal and buffer overflow vulnerabilities -- are the result of not properly validating input and implicitly granting applications privilege they do not require. In this case, the non-validated input actually contains SQL statements and relies on the application to naively insert the user input into the application's own request.

For example, imagine a fatally flawed web interface for the Social Security Administration where you enter your Social Security number (123006789) and the system displays a summary of your account and personal information. Using MySQL and PHP, the application might include code like this:

If the value for $ssn contained 123006789, the application would construct the following SQL query and execute it:

The result would be your personal data.

Now imagine that instead of entering your Social Security number, you enter % -- the SQL wildcard matching anything. The application foolishly takes your input as valid and blithely plugs it in to the SQL statement:

This would return the personal information of everyone in the table with a Social Security number!

Even worse, if the user entered the following input into the SSN field

'; DROP TABLE personal

... the constructed query would be:

The semicolon ( ; ) character separates individual SQL statements and "DROP TABLE tablename" is how database tables are deleted. Thus -- if not for a restriction in PHP applications -- this could result in the deletion the entire personal table from the database! (XKCD had a funny comic about this very issue .)

As we can see, this is a malicious SQL statement and a dangerously negligent application. Using the LIKE operator is a subtle example of unnecessary privilege . Because everyone has a unique Social Security number, there is no chance that a glob match (enabled by LIKE in SQL) will be required. At the very least, the above code should have used the exact match test = (equals sign) instead of LIKE, which would have returned an error when it was given a wildcard to match. The database also has a permissions problem, because the application accessing the database does not need to be able to use the DROP TABLE command. (Database users have permissions similar to users on a filesystem -- but the permissions still need to be configured properly.)

More fundamentally, this application is fatally flawed due to the total lack of input validation . Since all Social Security numbers are 9 digits, there is no chance that a legitimate user would ever need to enter anything but 9 digits into the SSN field. It is trivial to first check the contents of a variable before using it -- making sure that it contains only numbers or letters, etc. -- this is called input validation . In this way, punctuation, letters, and everything but numbers would cause an error before the SQL statement was even assembled.

However, making sure that the SSN contains just numbers or letters only works if you have purely numerical or alphabetical data. Unfortunately, it's not always possible to use this approach, because many fields require letters, letters and numbers, or even arbitrary characters that have special meaning in SQL like the single quote ( ' ) and semicolon ( ; ) that we used for our DROP TABLES statement. For example, consider a web forum where users' posts are stored in a database. Since posts can contain virtually any input, the database must be able to safely handle arbitrary input. Instead of validating our input, we want a way to sanitize it -- to make it safe.

The classic security approach to this problem is something called "string escaping." If a string contains special characters, we need a way to inform a parser (in this case the SQL server) that the characters should be treated simply as regular characters, not as characters with special meaning in SQL. Traditionally, this is done by putting a backslash (  ) in front of the each special character. For example, in most languages, to include a double quote character ( " ) in a double-quoted string, we can usually use the following syntax:

In essence, the backslash says "don't end the string here -- just treat it as a regular double quote character".

Most languages have functions that will sanitize or "escape" strings being used in MySQL requests. In PHP, the function is mysql_real_escape_string(foo) . It will take the string foo and return an escaped version. For example, if we did the following:

... $ssn_escaped would contain:

Since the special characters are now escaped with the backslash, they'll be treated as regular letters -- not special SQL tokens: Thus, our original query would become:

...and of course, there is no ssn like \'\; DROP TABLE personal . Running this query wouldn't drop the table, it would simply return an error.

Input validation errors happen constantly, not just with SQL. The only response is to write code with the least possible privilege and to perform comprehensive and correct validation (or sanitization) on all input.

For more information, see these articles:

• SQL on Wikipedia
• SQL Injection on Wikipedia
• SQL Injection article by Chris Shiflett
• OWASP SQL Injection Prevention Cheat Sheet

... for additional information, search online -- numerous resources and SQL tutorials exist.

Software Tools

This section will describe some tools you may need to complete this exercise.

In this exercise, you'll be fixing security vulnerabilities in a few simple programs. However, instead of your whole program, we only want the differences between your new, fixed, program, and the original. A file which contains only the changes between two revisions of a program is called a "patch." Fortunately, creating patch files for single-file source programs is easy.

To see the differences between two files on Unix, you use the diff utility:

Another useful tool is called patch . patch takes properly-formatted diff output, and applies the changes to the original file. diff can generate this output with a few options:

This above options for diff will create a patch with the filenames and all necessary information that the patch program requires. This makes patching as simple as executing:

... and this will create a patched version of the program that you can test.

When submitting a patch file, it is highly recommended that you create the patch and then test it before submitting it to make sure that it works. You will not get any points for code that does not execute or compile in the exercise environment.

If you're having permissions problems, consider switching to root by executing sudo su - or change the permissions of the source directory in question.

When attackers try to create SQL injection attacks, they often know very little about the the database schema. In our case, we have hands-on access to the database, so this should make the job of developing injection attacks easier. This is where the MySQL command-line client comes in.

To use the MySQL command line on the server, run a command like this:

... root is the user and zubaz99 is the password -- and the lack of spaces is important.

This is essentially an SQL "shell" and gives you root access to the entire database. Once you get logged in to the database, you need to select the database to use. Then, you can make selections from the database:

If you scroll up, or limit the query, you'll see that mysql very nicely adds a title to each column -- this is the column name. So you could create a query like this to display all account information for accounts with an id greater than 50:

There are many online SQL tutorials -- including some on SQL Injection, so we won't cover more here.

Assignment Instructions

You are the security administrator for FrobozzCo, a large corporation with a great many secrets. You have just come back from a much-needed four week vacation in West Shanbar, only to find that FrobozzCo has been having some serious security issues! In order to do everything you need, you've prepared a test environment on DETER with the necessary software installed.

• If you don't have an account, follow the instructions in the introduction to DETER document.
• Log into DETER.
• In the "Idle-Swap" field, enter "1". This tells DETER to swap your experiment out if it is idle for more than one hour.
• In the "Max. Duration" field, enter "6". This tells DETER to swap the experiment out after six hours.
• Swap in your new lab.
• After the experiment has finished swapping in, log in to the node via ssh.

Make sure that you save your work as you go. See the instructions in the submission section of this exercise for information about save and restore scripts. Save any changes you make to the sourcecode, your patches, memos, etc. in your home directory.

FrobozzCo has its own internal company credit union, FrobozzCo Community Credit Union (FCCU). FCCU has an Internet-accessible web-based application that allows employees to access their paychecks and pay bills via a money wiring system. There are very few bank employees, and they use a a special administrative interface that runs on a different system that is not network accessible. In true FrobozzCo fashion, the public banking software was written in house by the CTO's nephew (who is a nice kid but not the brightest candle on the cake).

As it turns out, a lot of money has been disappearing from the credit union while you've been gone. It looks like someone has figured out how to force other accounts to wire money... to an anonymous bank account in the Cayman Islands! Worse yet, several employees have had serious identity theft problems of late -- clearly someone has access to personal information and you have a hunch it's all coming from this server. To top it all off, the company itself is showing a deficit of $32,767 and it looks like it was somehow drawn through FCCU.

In a surprising display of foresight, your predecessor installed a network monitor watching the FCCU server. However, you are shocked to find out (from the network monitor and server logs) that nobody has logged into the server directly -- in fact, the only interaction that anyone has had with the server has come through the Internet facing web interface. It looks like insecure software is to blame, again.

You assume that there must be one or more vulnerabilities in the code that interfaces with the SQL database -- in the FCCU software, the directory, or both -- and that somehow a malicious user is able to make the system do something it's not supposed to, like write checks. Worse yet, it seems like the attacker has managed to view private information like social security numbers, birthdates, and so on. You've heard about a class of attacks called "SQL Injection," and it seems likely that this is the kind of exploit being used.

Surprisingly, your boss agrees with you and instructs you to produce a one page memo, a detailed transcript demonstrating the exploit, and a patch for the software. Additionally, he also wants to know how to clean up this mess -- how severe is the compromise? How can we restore the system to a safe state?

• The sourcecode is located at /usr/lib/cgi-bin/FCCU.php
• If you have set up ssh tunnelling to port 80 via local port 8118 (a good idea), the memo application can be accessed at http://localhost:8118/cgi-bin/FCCU.php
• Show how you can log into a single account without knowing any id numbers ahead of time.
• Show how you can log into every account account (one at a time) without knowing any id numbers ahead of time.
• Make some account (your choice) wire its total balance to the bank with routing number: 314159265 and account number: 271828182845
• Explain why you can't create a new account or arbitrarily update account balances (or show that you can).
• Create an exploit transcript in the file /root/submission/exploit.txt , which should include your SQL injections (in order), short answers and any other information you think I should know.
• Fix the vulnerability in the FCCU application by adding input validation and either character escaping (sanitization), parameterized queries or prepared statements.
• Create a patch against the original source.
• A description of the security flaw in the FCCU application
• A description of how you fixed the flaw. How does your fix solve the problem?
• How serious was this breach? Could attackers gain root access through this vulnerability?
• What should be done with the server in order to secure it?
• Include any other observations or thoughts you might have.
• your exploit walkthrough ( exploit.txt )
• Use the scripts described in the section for creating a submission tarball.

For this exercise, you will submit a tarball containing your patch, memo, and exploit code. Use the script submit.sh in /root on the server host for creating and restoring those tarballs.

submit.sh and restore.sh

submit.sh will back up:

• The FCCU code in /usr/lib/cgi-bin/FCCU.cgi

everything in /root/submission , which should include:

• exploit.txt

restore.sh will restore those files to their original locations, automatically overwriting whatever is there.

Submit your tarball to your instructor via email before the deadline indicated on the class website.

CS88 Lab 2: Part A: Web Security: SQL Injection Attacks

Handy references, lab 2a goals, prior knowledge, lab requirements, getting your lab2 starting point code, setting up docker containers for the database and the web-server, the web application, setting up an ip address, getting our docker vm up and running, getting familiar with the web interface, getting familiar with sql statements, create a database, insert and update the database, select, where and union clauses, adding comments, launch your attacks, grading rubric.

Lab Due Date: Tuesday, September 27, 11:59 PM

HTTP References: HTTP Request Methods , Python Requests Library , Handling Cookies in your browser , Base64 Library (Python) Encoding and Decoding

SQL References: SQL Introduction , SQL OWASP Injection , SQL Injection UNION Attack

This lab is built on the SEED Labs for Security Education project by Prof. Wenliang Du, at Syracuse University and by Deian Stefan at UCSD.

This lab is composed of two distinct web security attacks. The first is an SQL injection attack and the second is a Cross-Site Scripting (XSS) attacks. Since these are two disparate types of attacks, in week 1 we will focus on SQL Injection attacks and in week 2 we will focus on XSS attacks.

This lab page describes SQL, and launching SQL Injection attacks. In week 2 we will discuss XSS attacks.

The goal of this lab is to gain hands-on experience exploiting server-side SQL vulnerabilities and launching SQL injection attacks!

This lab will demonstrate how simple vulnerabilities (often occurring due to the carelessness of the website developer) can be really harmful to the website’s owners. These security attacks include (but aren’t limited) to stealing sensitive information, injecting malicious code, and causing the host server to crash.

The goal is to find ways to exploit the SQL injection vulnerabilities, demonstrate the damage that can be achieved by the attack, and master the techniques that can help defend against such type of attacks.

SQL Injection

SQL injection is a code injection technique that exploits the vulnerabilities in the interface between web applications and database servers. The vulnerability is present when user’s inputs are not correctly checked within the web applications before being sent to the back-end database servers.

Web applications often take inputs from users, and then use these inputs to construct SQL queries, so they can either validate/authorize a user, or retrieve information from the database (shopping, banking applications, etc). Web applications use SQL queries to store information in the database.

During the lab, you may encounter situations that may require to either execute HTML, Client-side JavaScript or SQL statements. However, no code that you are expected to write in this assignment exceeds 10 (very small) lines. Most of the expected code is pretty generic and the references above will help you find the code.

The aim of the assignment is not to teach you coding in the said languages, but to give you hands-on experience with the tricks that most “hackers” or exploiters use in today’s world. During no time are you expected to know “truly fancy” stuff like PHP, ASP, server-side JavaScript, AJAX, JQuery, Flash, CSS or in general - anything beyond the realms of the aforementioned techniques.

Complete and submit lab2a-sql.adoc .

Successfully conduct the following:

Compose SQL queries to a database: SELECT and UPDATE statements

Successfully run a series of SQL injection attacks

Defend against SQL injection attacks using the Prepared statement

Submit your

Power-up the CS88 VM through VirtualBox and login. Then, pull up a browser in your VM and log into CS88 Github for our class and get the ssh-URL to your lab git repository. Follow along with the prompts below to SSH, create a lab directory and clone your lab repos.

In this lab we will be using a VM - [installation and setup steps in Lab1 ] where we will clone the code from github .

Lab Environment Setup

In the last lab we saw how we can setup a virtual machine and we talked about what a VM does - i.e., creates virtual instances of the entire system hardware up!

You can think of a Docker container as a light-weight VM. Docker virtualizes the application environment in which all of your software applications, code and data live. If you think of the hardware as "bare metal" and the OS as managing computer resources, then the Docker container sits on top of the OS, giving us a sandboxed environment to work in, without the overhead of the entire virtual machine. You can read more about Docker containers here .

In this lab, we are going to run two Docker VMs inside our virtual machine. One for the web server that takes user input and one for our the SQL database.

Can you run a docker inside a VM? Yes! How about a docker inside a docker inside a VM? also yes!…​ it’s turtles all the way down…​

For this lab, we have a simple employee management application. Employees can view and update their personal information in the database through this web application. There are two user profiles in our application:

Administrator: who has root privileges and can manage employee’s profile information.

Employee: who can view and update their own profile information.

First we need to configure an IP address where we want to host our web server. To do so, we need to modify the /etc/hosts file that maintains a mapping of the IP addresses for each domain name (e.g. www.google.com maps to 8.8.8.8 ).

We will first make a copy of the hosts file (so we don’t inadvertently get rid of it!). Then, we will open hosts in atom and edit the file as shown below.

In atom, your hosts file should be updated to the following:

Save your file, and at the Authentication Required prompt, enter your password for your VM (i.e., the password associated with your user login to the VM).

We first need to build our docker image just like we built our vm.

Next, we want to run dcup which starts the docker container. This is equivalent to starting our VM.

You should now have a docker container up and running! The dcup command will swallow the cursor. To interact with the docker, we will need to open up a new terminal.

In-Lab Tasks

In lab today, we will first explore the web interface, and get familiar with MySQL syntax for the lab. We will then build on this knowledge to execute SQL injection attacks. The pre-class videos for Thursday go over SQL injection attacks in more detail and we will also go through more examples of SQL commands and injections in class on Thursday.

To interact with our newly launched web server, let’s pull up the firefox browser and load our web server address www.seed-server.com .

You should see the following page displayed:

Try to login as Alice using the information provided in the SQL Table above and you should see Alice’s information provided.

Next, click on the Edit Profile link and notice that you are only allowed to edit a small subset of fields and not all of the information maintained about Alice. For e.g., you can’t edit her salary for obvious reasons :).

You can try to edit Alice’s email or phone number and we will see that change propagated to our MySQL database.

Also notice that when you are on Alice’s homepage the web address looks like the following

You can already see the structure of the underlying SQL query that is being passed to the database to be something of the form: From the table of users, select the person with username "Alice" and password "seedalice" .

You can try logging in as different users, editing their profiles and observe how this URL changes.

In order to interact with the SQL database directly, we can login on the terminal, to the docker VM that is running the database. To do so, we will frist need to run dockps to find the ID of the docker container running the SQL database.

Now, we want to start a shell on the mysql docker container. To do so we can execute the following. Note: We only need to specify the first two characters that uniquely identify the docker container to get a shell prompt started:

We are now logged into the docker container! We will now use the mysql client program to interact with the database. The login credentials for the database are:

To login to the SQL database we will use the following command. Note: there is no space between p and dees in the command below .

Show Databases: First, we can view the databases in our database management system:

We see that we have 5 databases. The one that we are going to be experimenting with is called sqllab_users .

Show tables in a database: We can now see all the tables in a particular database.

SQL Tutorial

In this section we are going to do a brief tutorial on using a mySQL database. By the end of this section, you should be ready to start attempting your lab! If you are already familiar with mySQL syntax feel free to skip this section.

Inside our MySQL database, we can use the SHOW DATABASES that we saw above to list existing databases. We can also create new databases. Let’s create a new database called 'under_the_sea'. And create

We can use the INSERT INTO statement to insert a new record into the table. Note that we do not need to specify a value of the ID column, as it will be automatically set by the database.

We can view our new entry in the table using the SELECT statement.

We can use the UPDATE statement to modify an existing record.

The SELECT statement is the most common operation on databases. It retrieves information from a database and can be used in conjunction with the where clause to express more complex queries.

A simple example of SELECT that builds on what we have seen so far is to display a subset of columns in our table. For example, we can choose to only depict the name and password of all the users in ocean .

We can now apply the WHERE clause that has the format: SQL Statement WHERE predicate;

Here, the SQL statement only reflects the rows for which the predicate in the WHERE clause is TRUE.

The predicate is a logical expression; multiple predicates can be combined using keywords AND and OR.

Let’s first populate more users into our table. You can choose your own rows, but as an example here are the rows that I’ve populated into ocean :

Using the WHERE clause: The example below shows two clauses. The first, just returns a record where the ID=4 , and the second returns the record where the ID = 4 or the Name = Squidward .

We can also use a slightly strange clause that will be really handy when we start running SQL injection attacks. In the example below the 1=1 will always evaluate to TRUE no matter what other clauses exist before it! This ability for us to be able to append a 1=1 to any clause can create a whole host of vulnerabilities. You can try changing the clause before 1=1 and see if the results change.

The UNION operator: The UNION operator is used to combine the results of more than one SELECT statement where:

The same number of columns are returned from both SELECT statements and,

The columns from both the tables have the same data type

We can add comments in three differnt styles in mySQL as shown below. We shall soon see that adding comments can invalidate the rest of the line, and canalso result in a whole host of vulnerabilities!

You can exit from mysql and the database docker container by using two exit commands

Getting started with SQL Injection Attacks

Now that we have a basic understanding of how SQL commands work, and we’ve interacted with the front-end database. Let’s try to apply them together to expose vulnerabilities in the front-end web-server.

In the figure below, we are functioning as the user, sitting at a web browser ( www.seed-server.com ) accessing the web server that retrieves content from the backend database.

Web server functionality

When the user provides input in the input fields, the request is sent to the Web-server, which then parses the user input into an SQL query. Based on the data that is displayed upon loggin in as Alice we can guess that the query that the webserver send to the database looks something like the following:

Since the webserver is performing no input verification , we can essentially put in anything we like in the two input fields for username and password. Let’s see an example of this.

In the following login attempt as an attacker, let’s assume we don’t know Alice’s password but we do know that there is a user called Alice in the system. We might then try to just put in the username without a password, and as expected, this will result in an error as shown below.

The figure also shows what the underlying SQL query might look like when we provide just the name alice and no password.

In our second attempt, we provide a new input where in the name field we type in alice '# . Now suddenly we seemed to have logged in without providing the password! If we look at the underlying SQL query, we see that our input has effectively terminated the string input when we added the ' right after alice , and we commented out the rest of the query by adding the # character!

Our goal for the SQL injection attacks is going to leverage vulnerability in the manner in which data is read into the web server, to login and change individual users' details without (a), having the right credentials (e.g., login as Alice without having Alice’s password), or (b), having the right user privileges (e.g. Alice’s salary being updated without logging in as Admin).

You are now ready to launch all five attacks listed in lab2a-worksheet.adoc . For every level that you succeed in attempting, write down the corresponding SQL query on the worksheet.

Lab 2 Part A is worth 4% that is going to be graded as follows:

2 points for launching SQL Injection Attacks and demonstrating them in lab the following week.

2 points for completing lab2a-worksheet.adoc

Please remove any debugging output prior to submitting.

To submit your code, simply commit your changes locally using git add and git commit . Then run git push while in your lab directory.

Burp Scanner

Burp Suite's web vulnerability scanner

Product comparison

What's the difference between Pro and Enterprise Edition?

Download the latest version of Burp Suite.

• Web Security Academy
• SQL injection

Lab: SQL injection vulnerability in WHERE clause allowing retrieval of hidden data

This lab contains a SQL injection vulnerability in the product category filter. When the user selects a category, the application carries out a SQL query like the following:

To solve the lab, perform a SQL injection attack that causes the application to display one or more unreleased products.

Launching labs may take some time, please hold on while we build your environment.

• Use Burp Suite to intercept and modify the request that sets the product category filter.
• Modify the category parameter, giving it the value '+OR+1=1--
• Submit the request, and verify that the response now contains one or more unreleased products.

Community solutions

Rana Khalil

Michael Sommer

Register for free to track your learning progress

Practise exploiting vulnerabilities on realistic targets.

Record your progression from Apprentice to Expert.

See where you rank in our Hall of Fame.

Already got an account? Login here

Find SQL injection vulnerabilities using Burp Suite

• Share via Twitter
• Share via Facebook
• Share via LinkedIn

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

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

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.

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.

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!

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:

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:

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…

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

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:

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:

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.

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.

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:

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:

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.

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

Learn SQL practically and Get Certified .

Popular tutorials, learn sql interactively.

Learn Python practically and Get Certified .

Popular Examples

• SQL Introduction

SQL SELECT (I)

• SQL AND, OR, NOT
• SQL SELECT DISTINCT
• SQL SELECT AS
• SQL LIMIT, TOP, FETCH FIRST
• SQL IN Operator
• SQL BETWEEN Operator
• SQL IS NULL and NOT NULL
• SQL MIN() and MAX()
• SQL COUNT()
• SQL SUM() and AVG()

SQL SELECT (II)

• SQL ORDER BY
• SQL GROUP BY
• SQL Wildcards
• SQL Subquery
• SQL ANY and ALL
• SQL INNER JOIN
• SQL LEFT JOIN
• SQL RIGHT JOIN
• SQL FULL OUTER JOIN

SQL DATABASE & TABLE

• SQL Create Database
• SQL Create Table
• SQL Drop Database
• SQL Drop Table
• SQL Alter Table
• SQL Backup Database

SQL Insert, Update and Delete

• SQL Insert Into
• SQL Select Into
• SQL Insert Into Select
• SQL Delete and Truncate Rows
• SQL Constraints
• SQL Not Null Constraint
• SQL Unique Constraints
• SQL Primary Key
• SQL Foreign Key
• SQL Default
• SQL Create Index

SQL Additional Topics

• SQL Data Types
• SQL Date and Time
• SQL Operators

SQL Comments

• SQL Stored Procedures

SQL Injection

Sql tutorials.

Introduction to SQL and Database

SQL SELECT INTO (Copy Table)

• SQL SELECT LIMIT, TOP, FETCH FIRST
• SQL FOREIGN KEY Constraint

SQL Injection is a technique where SQL commands are executed from the form input fields or URL query parameters. This leads to unauthorized access to the database (a type of hacking).

If an SQL injection is successful, unauthorized people may read, create, update or even delete records from the database tables. This technique is mainly used by (but not limited to) hackers, pentesters, QAs and security researchers.

• SQL Injection Using Multiple Statements

Suppose we have a search form to search products by their ID on our website. The PHP code snippet to search product would look something like,

If the user inputs 20; DROP TABLE Products; in the form, then the SQL statement becomes,

Now, this SQL statement deletes the Products table from the database. This is possible because most database systems can execute multiple statements at the same time.

• SQL Injection Using Always True Condition

Another way to perform an SQL injection is by passing a condition that always results in TRUE so that the data is always fetched no matter what.

Let's take a look at another PHP code snippet where we have a login form in our website and we need to fetch users by providing credentials.

If the user inputs username as invalid_user" OR "1"="1 and password as invalid_pass" OR "1"="1 , then the SQL statement becomes

Since, "1"="1" is always TRUE , no matter what the username and password user enters, SQL will fetch the data of all users from the database.

• How to Protect SQL Statements From Injections?

Validate User Input

We should always validate the user's input data before actually sending them to the database. Some best practices include: trimming spaces, parsing special characters, limiting the input size, etc.

For example,

Here, this PHP code snippet validates input data to some extent.

ORM (Object Relational Mapping) is a tool that basically parses SQL statements into programming language code and vice-versa.

If we use ORM's, we don't have to write raw SQL for the most part. Since ORM's are designed by following good practices and security protocols, they are safe and easy to use.

For example, consider the SQL code below:

Its equivalent code in SQLAlchemy ORM for Python will be:

Using Prepared Statements

Another method to protect SQL statements from injections is by using prepared statements.

Prepared statements are basically SQL statements but with placeholders. The passed arguments are just replaced in place of the placeholders.

Here, the values are only placed in place of the ? placeholder and the structure of SQL statements are preserved.

Using Frameworks

Also, if we are building a real world application, it's always a good idea to use frameworks (such as Django, Laravel, ASP.net, etc.) instead of writing code from scratch.

It's because these frameworks handle SQL injection and many other commonly occurring issues by default.

SQL Injection is a very common way of hacking any web application.

So, if we use raw SQL statements while building our application, we must test and verify them thoroughly.

Table of Contents

• Introduction

Sorry about that.

Related Tutorials

Programming

SQL Tutorial

Sql database, sql references, sql examples, sql injection.

SQL injection is a code injection technique that might destroy your database.

SQL injection is one of the most common web hacking techniques.

SQL injection is the placement of malicious code in SQL statements, via web page input.

SQL in Web Pages

SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database.

Look at the following example which creates a SELECT statement by adding a variable (txtUserId) to a select string. The variable is fetched from user input (getRequestString):

The rest of this chapter describes the potential dangers of using user input in SQL statements.

SQL Injection Based on 1=1 is Always True

Look at the example above again. The original purpose of the code was to create an SQL statement to select a user, with a given user id.

If there is nothing to prevent a user from entering "wrong" input, the user can enter some "smart" input like this:

Then, the SQL statement will look like this:

The SQL above is valid and will return ALL rows from the "Users" table, since OR 1=1 is always TRUE.

Does the example above look dangerous? What if the "Users" table contains names and passwords?

The SQL statement above is much the same as this:

A hacker might get access to all the user names and passwords in a database, by simply inserting 105 OR 1=1 into the input field.

Advertisement

SQL Injection Based on ""="" is Always True

Here is an example of a user login on a web site:

A hacker might get access to user names and passwords in a database by simply inserting " OR ""=" into the user name or password text box:

The code at the server will create a valid SQL statement like this:

The SQL above is valid and will return all rows from the "Users" table, since OR ""="" is always TRUE.

SQL Injection Based on Batched SQL Statements 

Most databases support batched SQL statement.

A batch of SQL statements is a group of two or more SQL statements, separated by semicolons.

The SQL statement below will return all rows from the "Users" table, then delete the "Suppliers" table.

Look at the following example:

And the following input:

The valid SQL statement would look like this:

Use SQL Parameters for Protection

To protect a web site from SQL injection, you can use SQL parameters.

SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.

ASP.NET Razor Example

Note that parameters are represented in the SQL statement by a @ marker.

The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed.

Another Example

The following examples shows how to build parameterized queries in some common web languages.

SELECT STATEMENT IN ASP.NET:

INSERT INTO STATEMENT IN ASP.NET:

INSERT INTO STATEMENT IN PHP:

COLOR PICKER

Report Error

If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

[email protected]

Top Tutorials

Top references, top examples, get certified.

Come join our Discord server for support or further discussions

Writeups should have a link to TryHackMe and not include any passwords/cracked hashes/flags

Web-Based Machine Information

Use the web-based machine to attack other target machines you start on TryHackMe.

• Private IP: (Use this for your reverse shells)
• When accessing target machines you start on TryHackMe tasks, make sure you're using the correct IP (it should not be the IP of your AttackBox)

Complete the room to earn this badge

Question Hint

Congratulations.

You've completed the room! Share this with your friends:

Leave feedback

To access this machine, you need to either

Connect to our network via a VPN

Use the AttackBox

Use a web-based attack machine (recommended)

Expiring Soon

Your machine is going to expire soon. Close this and add an hour to stop it from terminating!

Expired Machine

Your machine has expired and terminated.

How to access machines

Now you've started your machine, to access it you need to either

Download your VPN configuration file and import it into a OpenVPN client

Control a web-based machine with everything you need, all from inside your browser

Reset Your Progress

Cloud information.

• Environment
• Credentials

Generating Your Certificate

Hey there, what's your name.

If you want your name to appear on your certificate, please fill the field below.

You're here incognito? It's ok!

If you chose skip, your username will be used instead!

To access target machines you need to either:

Use a browser-based attack machine

What Operating System are you using?

• Download your OpenVPN configuration pack.
• Download the OpenVPN GUI application.
• Install the OpenVPN GUI application. Then open the installer file and follow the setup wizard.

• Select the configuration file you downloaded earlier.

• Run the following command in your terminal: sudo apt install openvpn
• Locate the full path to your VPN configuration file (normally in your ~/Downloads folder).
• Use your OpenVPN file with the following command: sudo openvpn /path/to/file.ovpn
• Download OpenVPN for MacOS.

• Open and run the OpenVPN GUI application.

Having Problems?

• If you can access 10.10.10.10 , you're connected.
• Downloading and getting a 404? Go the access page and switch VPN servers.
• Getting inline cert error? Go the access page and switch VPN servers.
• If you are using a virtual machine, you will need to run the VPN inside that machine.
• Is the OpenVPN client running as root? (On Windows, run OpenVPN GUI as administrator. On Linux, run with sudo)
• Have you restarted your VM?
• Is your OpenVPN up-to-date?
• Only 1 OpenVPN connection is allowed. (Run ps aux | grep openvpn - are there 2 VPN sessions running?)
• Still having issues? Check our docs out.

Use your own web-based linux machine to access machines on TryHackMe

To start your AttackBox in the room, click the Start AttackBox button. Your private machine will take 2 minutes to start.

Free users get 1 free AttackBox hour. Subscribed users get more powerful machines with unlimited deploys.

Search code, repositories, users, issues, pull requests...

Provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications

SQL injection lab

toogle/sql-lab

Name already in use.

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more about the CLI .

• Open with GitHub Desktop
• Download ZIP

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Sandbox for the lab on SQL injections. Includes documentation in russian.

The project intended for demonstration purposes only. It is EXTREMELY DANGER to use it for real applications.

Requirements

Copyright (C) 2013 Andrew A. Usenok < [email protected] >

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see http://www.gnu.org/licenses/ .

This website uses cookies to analyze our traffic and only share that information with our analytics partners.

SQL Injection

A SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to affect the execution of predefined SQL commands.

Threat Modeling

• SQL injection attacks allow attackers to spoof identity, tamper with existing data, cause repudiation issues such as voiding transactions or changing balances, allow the complete disclosure of all data on the system, destroy the data or make it otherwise unavailable, and become administrators of the database server.
• SQL Injection is very common with PHP and ASP applications due to the prevalence of older functional interfaces. Due to the nature of programmatic interfaces available, J2EE and ASP.NET applications are less likely to have easily exploited SQL injections.
• The severity of SQL Injection attacks is limited by the attacker’s skill and imagination, and to a lesser extent, defense in depth countermeasures, such as low privilege connections to the database server and so on. In general, consider SQL Injection a high impact severity.

Related Security Activities

How to avoid sql injection vulnerabilities.

See the OWASP SQL Injection Prevention Cheat Sheet . See the OWASP Query Parameterization Cheat Sheet .

How to Review Code for SQL Injection Vulnerabilities

See the OWASP Code Review Guide article on how to Review Code for SQL Injection vulnerabilities.

How to Test for SQL Injection Vulnerabilities

See the OWASP Testing Guide for information on testing for SQL Injection vulnerabilities.

How to Bypass Web Application Firewalls with SQLi

See the OWASP Article on using SQL Injection to bypass a WAF

Description

SQL injection attack occurs when:

• An unintended data enters a program from an untrusted source.
• The data is used to dynamically construct a SQL query

The main consequences are:

• Confidentiality : Since SQL databases generally hold sensitive data, loss of confidentiality is a frequent problem with SQL Injection vulnerabilities.
• Authentication : If poor SQL commands are used to check user names and passwords, it may be possible to connect to a system as another user with no previous knowledge of the password.
• Authorization : If authorization information is held in a SQL database, it may be possible to change this information through the successful exploitation of a SQL Injection vulnerability.
• Integrity : Just as it may be possible to read sensitive information, it is also possible to make changes or even delete this information with a SQL Injection attack.

Risk Factors

The platform affected can be:

• Language: SQL
• Platform: Any (requires interaction with a SQL database)

SQL Injection has become a common issue with database-driven web sites. The flaw is easily detected, and easily exploited, and as such, any site or software package with even a minimal user base is likely to be subject to an attempted attack of this kind.

Essentially, the attack is accomplished by placing a meta character into data input to then place SQL commands in the control plane, which did not exist there before. This flaw depends on the fact that SQL makes no real distinction between the control and data planes.

In SQL: select id, firstname, lastname from authors

If one provided: Firstname: evil'ex and Lastname: Newman

the query string becomes:

select id, firstname, lastname from authors where firstname = 'evil'ex' and lastname ='newman'

which the database attempts to run as:

Incorrect syntax near il' as the database tried to execute evil.

A safe version of the above SQL statement could be coded in Java as:

The following C# code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where owner matches the user name of the currently-authenticated user.

The query that this code intends to execute follows:

However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName , then the query becomes the following:

The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:

SELECT * FROM items;

This simplification of the query allows the attacker to bypass the requirement that the query only return items owned by the authenticated user; the query now returns all entries stored in the items table, regardless of their specified owner.

This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name hacker enters the string "name'); DELETE FROM items; --" for itemName , then the query becomes the following two queries:

Many database servers, including Microsoft® SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error in Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, in databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens ( -- ), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed. In this case the comment character serves to remove the trailing single-quote left over from the modified query. In a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT \* FROM items WHERE 'a'='a" , the following three valid statements will be created:

One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a deny list of potentially malicious values. An allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, deny listing is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers can:

• Target fields that are not quoted
• Find ways to bypass the need for certain escaped meta-characters
• Use stored procedures to hide the injected meta-characters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. For example, the following PL/SQL procedure is vulnerable to the same SQL injection attack shown in the first example.

Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.

Related Attacks

• SQL Injection Bypassing WAF
• Blind SQL Injection
• Code Injection
• Double Encoding
• ORM Injection
• SQL Injection Knowledge Base - A reference guide for MySQL, MSSQL and Oracle SQL Injection attacks.
• GreenSQL Open Source SQL Injection Filter - An Open Source database firewall used to protect databases from SQL injection attacks.
• This also includes recommended defenses.

Category:Injection

Important Community Links

• Attacks (You are here)
• Vulnerabilities

Upcoming OWASP Global Events

Owasp news & opinions.