Preventing SQL Injection Attacks
AbstractHow many websites do you have accounts with that store personal information like your name, email, phone number, or mailing address? If the people running these websites are not careful, hackers could gain unauthorized access to, and even change or delete, your information. They can do this using something called SQL injection, which involves entering malicious code into text fields on a website. In this project you will learn how SQL injection works and figure out how to prevent it.
Fix vulnerabilities in a website that is open to SQL injection.
Structured Query Language, or SQL, is a computer language designed to easily manage databases. This includes things like quickly finding data, updating it, or deleting it. Many websites like online shopping sites and financial institutions have huge databases that store data about millions of their users, and they use SQL to manage these databases. For example, when you log in to a website that stores some information about you, it might use SQL to look up your username in a table, and then pull up corresponding information about your account. If you update something about your account (like your mailing address), it will use SQL to overwrite your old address in the database and replace it with the new one. The tutorials from CodeAcademy and Khan Academy in the bibliography provide an introduction to SQL and how it can be used to create, edit, and search databases. If you do not already know SQL, you should work through at least one of these tutorials to learn the basics.
Many times, these databases can include sensitive personal information like phone numbers, email and mailing addresses, and credit card or social security numbers (very sensitive information should be encrypted instead of stored as plaintext, but that is not always the case). Ideally, only authorized users will be able to access specific information in the database. For a website, this frequently means that individual users can access their own information, and an administrator for the website may be able to access information for all users. However, hackers might want to steal the information for a variety of reasons: to sell email addresses to spammers, to use credit card numbers for illegal purchases, or just to cause disruption and harm the reputation of the company they hack. Since this can cause embarrassment, hassle, or financial harm for users and the company, it is important to make sure the databases remain secure.
One method hackers can use to gain unauthorized access to a database is SQL injection. SQL injection is a specific type of a code injection where a hacker tricks a website into executing an SQL command that results in unauthorized access to data. This can be done, for example, by entering a cleverly-formatted SQL statement into a text entry field on a website (like a search box or username/password login). SQL injection can be prevented with several techniques with varying levels of reliability, like sanitizing inputs and parameterized statements.
Several references in the bibliography describe how SQL injection works in more detail, and will even let you try it out for yourself (on a fake database, of course!). After reviewing the basic SQL tutorials, you should review these references to gain an understanding of how to perform an SQL injection attack, and how to prevent one. Then, move on to the procedure of this project and you will be challenged with fixing a website that is vulnerable to SQL injection. To do this, you will set up a web server with an instance of a virtual machine (running Microsoft Windows®) created by Science Buddies. This allows you to access your own copy of the virtual machine using the remote desktop protocol (RDP), and to run and modify your own copy of the website. If you are not familiar or comfortable with these terms, you should do more research on them before you start the project. You will need to look up how to use remote desktop protocol with your computer's operating system.
Terms and Concepts
- Structured Query Language (SQL)
- SQL injection
- Code injection
- Sanitizing inputs
- Parameterized statements
- Web server
- Virtual machine
- Remote desktop protocol (RDP)
- What is SQL injection and how can it be prevented?
- What are some real-world examples of successful SQL injection attacks? What type of data was stolen and what were the consequences?
- What are some other common types of cyberattacks used against websites? How do they differ from SQL injection?
- Codeacademy (n.d.). Learn SQL. Retrieved August 3, 2017.
- Khan Academy (n.d.). Intro to SQL: Querying and managing data. Retrieved August 3, 2017.
- W3Schools (n.d.). SQL Injection. Retrieved August 3, 2017.
- IEEE TryCybSi (n.d.). SQL Injection Attack. Retrieved August 3, 2017.
- Sweatman, W. (March 9, 2016). The Dark Arts: SQL Injection and Secure Passwords. Hackaday. Retrieved August 3, 2017.
- w3schools.com (n.d.). PHP 5 Tutorial. Retrieved October 11, 2017.
- Munroe, R (n.d.). Exploits of a Mom. xkcd. Retrieved August 3, 2017.
Materials and Equipment
- Computer with internet access
- Adult with a credit card (credit card is required to sign up for the free tier of Amazon Web Services, even if you do not spend any money)
Disclaimer: Science Buddies participates in affiliate programs with Home Science Tools, Amazon.com, Carolina Biological, and Jameco Electronics. Proceeds from the affiliate programs help support Science Buddies, a 501(c)(3) public charity, and keep our resources free for everyone. Our top priority is student learning. If you have any comments (positive or negative) related to purchases you've made for science projects from recommendations on our site, please let us know. Write to us at firstname.lastname@example.org.
Cybersecurity projects can be fun, but they can also get you in trouble if you are not careful. Make sure you follow these rules when doing a cybersecurity project:
- Do not attack any individual, computer, system, or network without consent from the individual (or person who owns the computer). For example, do not try to guess someone's email password and log into their account unless you get their permission first, or try to hack into a website without permission from the owner of the website.
- Even if you have consent to perform an attack, the attack should be for learning purposes only, and you should help the individual or organization fix any problems you find (this is known as "white hat" hacking). For example, if you are able to guess someone's password, you should tell them they need to pick a stronger password (and help them learn how). Do not read their emails, change any of their account settings, look at private information or files like pictures, or tell anyone else their password.
- If your project involves human subjects, even if you have their consent, you may still need approval from your science fair or an Institutional Review Board (similar to the rules for psychology or medical experiments). See this page for more information.
- Do not pretend to be a different person, company, or other organization online. This includes pretending to be someone else on a social media site, setting up fake websites designed to look like real websites from reputable companies, or sending "phishing" or other emails designed to look like they were sent by someone else. (A controlled experiment where only study participants have access to examples of such websites or emails would be OK.)
- Do not use data that was illegally obtained (for example, contact information stolen from a company's employee database), even if it was stolen by someone else and already posted online.
- Do not publicly post sensitive personal information, even if it was obtained with consent. For example, if your project involves accessing people's contact information (legally), do not post someone's name and address in the "Results" section of your science fair display board. You should destroy any such information (by shredding paper or deleting files) when you are done with your project.
- Do not install or run any malicious software (viruses, malware, spyware, trojans, etc.) on a computer that is connected to the internet. The software could easily spread to other computers and get out of your control.
If you have any doubts or questions about your project, check with your teacher or science fair administrator before you start.
Set Up Your Virtual Machine
Note: this section contains instructions for setting up a free-tier account with Amazon Web Services. The exact instructions you need to follow may change in the future if Amazon changes their services.
- Go to https://aws.amazon.com/free/ and create a free account. You will need a credit card to create the account, but you will not be charged if you only use the free services.
- After logging into the account, set the region to "US West (Oregon)" using the drop-down menu in the upper right (the menu should appear next to your name).
- Select "Services" in the upper left, and then "EC2" under "Compute."
- Important: at the top of the page, check that you do not have any "Running Instances" listed under "Resources" (for example, you might already have one running if you started this project earlier and came back to it). If you do, select "Running Instances," then right-click the instance, select "Instance State→Terminate," and wait for the instance to shut down (the "Instance State" will change to "terminated"). The free service tier only allows you to run one instance at a time. If you run multiple instances at once, your credit card will be charged, so always remember to check this before you launch a new instance.
- Click the "Launch Instance" button.
- On the "Application and OS Images (Amazon Machine Image)" section, click on "Browse more AMI's" on the right panel. Then click on "Community AMI's".
- Search for "science-buddies-sql-injection" and click on the "Select" button next to it.
- On the "Instance Type" section, pick the "t2.micro" type, which is marked as "Free tier eligible."
- On the "Network settings" section, click on "Create security group" and delete the existing "RDP" rule by clicking the check next to it.
- Click on Edit on the upper right of the "Network settings" section. Click "Add Security Group Rule" to add a new rule. Change the "Type" to "All Traffic" and the "Source type" to "My IP." This ensures that the instance can only be accessed from your IP address. Then click "Launch Instance."
- On the "Review Instance Launch" page, click "Launch."
- In the pop-up window, select "Proceed without a key pair." Then click "Launch Instance."
- On the "Launch an Instance" page, click on your instance in the Success pop-up. You should see your instance listed (at this point, you should only have one–follow the directions in step 4 to terminate any other instances). Wait until it says "running" under "Instance State" to indicate that your virtual machine is now up and running.
- Copy the "IPv4 Public IP" address into your browser's address bar in a new tab. This should bring you to the example login page created by Science Buddies (it should say "Cybersecurity: SQL Injection" at the top). If you get an error message and cannot access the page:
- Make sure your instance is running. If it still says "pending" under "Instance State," you might need to wait a few more minutes for it to start running.
- Make sure you set the security rules correctly as described in steps 7–9. This will only allow you to access the virtual machine from the same IP address you used to launch the instance. So, for example, if you set this project up at home and then try to access the virtual machine from school, it will not work because the school computer has a different IP address.
Try SQL Injection
The website provided by Science Buddies is a simple example of a site that stores information about users in a database. Each user has "public" information that other users can see (their favorite color) and "private" information that only they can see (their email address). Users must log in to the site using a password, and can search for other users by name to find the public information. However, the site is vulnerable to SQL injection, allowing a malicious user to gain access to information they are not authorized to see. Try the following:
- Log in to the site with the username "Chris" and the password "science". You should be able to see your own favorite color and email address.
- Search for the username "Steve". You should be able to see Steve's favorite color, but not his email address.
- Click the "Log out" button in the upper right.
- Now think like a hacker who wants to steal private information from the database. You want to log in as Steve so you can see his email address, but you do not know his password. Can you log in as Steve using SQL injection on the login page? If you have trouble, remember to read the references about SQL injection in the bibliography.
- Now you want to get even more email addresses. Can you use SQL injection to get the search page to display a list of all users in the database? Then, using the same trick you used to log in as Steve, log in as them one at a time to get their email addresses?
- Logging in as each user one at a time is a lot of work. Can you figure out how to get the search page to display every user's email address all at once?
- Keep thinking like a hacker. What other mischief or damage could you cause to the database? Try the following actions—some of these will require some creativity!
- Add a new user.
- Modify data about an existing user.
- Delete an existing user.
- Get a user's password.
- Create a completely new table.
- If you want to start over with the original database, go back to step 4 in the "Set Up Your Virtual Machine" section of the procedure, terminate your instance, and start a new one.
Preventing SQL Injection
Now that you have discovered vulnerabilities in the website, you have to figure out how to fix them. You can do this by editing the PHP files on your virtual machine, which hosts the website you are accessing from your regular computer.
- Connect to your virtual machine using remote desktop protocol. If you are on a Windows computer, run the "Remote Desktop Connection" app. If you have a Mac, you can use the "Microsoft Remote Desktop" app. Use these credentials:
- The IP address from step 14 of the "Set Up Your Virtual Machine" section.
- Username: Administrator
- Password: ScienceBuddies!
- Select "Yes" on the warning screen to connect.
- On the virtual desktop (not your regular desktop), navigate your Downloads to C:\Science Buddies\SQL Injection. This folder contains the PHP files for the example website. "login.php" contains the code for the login page and "search.php" contains the code for the search page. You do not need to edit the other files.
- On the virtual machine, make a simple change to one of these files in a text editor and save it (for example, change some of the HTML text on the login page). Then, refresh the website (in the browser on your regular desktop) and you should see the updated page. You may want to make backup copies of the original PHP files before you proceed and make more changes.
- Now for the real meat of this project: can you edit login.php and search.php to fix their vulnerabilities to SQL injection? This will require some research on your part. What are the current best practices for preventing SQL injection? If you go back to the "Try SQL Injection" section (remember to save your PHP files and refresh your browser first), do the methods you tried there still work?
Ask an Expert
This project explores topics key to Industry, Innovation and Infrastructure: Build resilient infrastructure, promote sustainable industrialization and foster innovation.
- Do you know anyone with their own website, like a friend who runs a blog, or someone who owns their own business? Get their permission first and then find out if their website is vulnerable to SQL injection. Do not attempt to gain unauthorized access to any real data. If the website is open to an SQL injection attack, can you show them how to fix it?
- Websites can be vulnerable to other types of attacks like cross-site scripting (XSS) or remote file inclusion. Research these types of cyberattacks and do a project demonstrating how to prevent them.
- Try this project with a friend. You can each set up your own virtual machine, and then try to hack the other person's while fixing the vulnerabilities in your own.
- What if a website is protected from SQL injection but hackers still want to break into a user's account? Can they do so just by guessing a user's password? Try this project to find out.
If you like this project, you might enjoy exploring these related careers: