Join Mike Chapple for an in-depth discussion in this video Preventing SQL injection, part of CompTIA Security+ Exam Prep (SY0-401): Threats and Vulnerabilities.
- SQL injection attacks use web applications as a mechanism to illegitimately access database servers that support web applications and retrieve sensitive information or make unauthorized modifications to the database. Many modern applications rely upon databases to help generate dynamic content on the fly. Consider, for example, an online shopping website that has millions of items in its catalog. Users can visit the site and search for just about anything using any combination of keywords.
Obviously the site developers can't imagine every possible search term and create pages in advance. That's where databases come into play. Instead of creating those pages in advance, developers write dynamic web applications. These web applications reach out to databases to obtain content as they build pages that respond to user requests. Let's look at an example of a SQL query. You don't need to know the specifcs of SQL for the Security+ Exam, but it is helpful to be somewhat familiar with query syntax.
In this example, the web application is requesting a password for the user mchapple from the database. The database returns a table showing the username and password. There are a few parts to this query. The select statement specifies the information that we want to retrieve from the database. In this case, that's the username and password. The from clause tells the database what table contains the information. In this case, that's the user_accounts table. Finally, the where clause limits the results to those matching a certain query.
In this case, those for the user mchapple. A dynamic web application might plug in information to the where clause from a variable. SQL injection attacks take advantage of this to give the database unexpected instructions. For example, what if we added this strange Or one equals one clause to the end of the where statement? One equals one is just a mathematical statement that's always true so the where clause now essentially reads where username equals mchapple or true so it's always true and you see the results that come back from the web application include all of the usernames and passwords from the database.
Let's try this against a WebGoat application. You see here a simple web application that returns database information for a user after entering their last name. The application also displays the SQL query on the screen for our benefit. If we use this application the way it's intended, we simply enter our last name, let's try Smith, into this box and then click Go. And you can see here the query was properly constructed. Select star from user_data where last_name equals Smith and below we see the results of that query, all the information about users with the last name Smith.
Now let's try a SQL injection attack. Instead of just entering Smith, we're going to add onto the end of that that Or one equals one. So I'm going to put a quote here, enter my Or one equals one, and then comment out the rest of the syntax. The purpose of some of these extra characters here is just to make the SQL query work. You'll see that in just a second when I hit Go. When I scroll down now and look at the query, you can see the quote that I added at the end of Smith has ended the last name equals Smith quotes and then we've added on the Or one equals one.
The rest of this just comments out the single quote that's left over from the query template. The interesting thing here now is instead of just seeing the results for the user John Smith, we see all of the users contained within that database and their credit card numbers. We can even get a little more sinister than this. Let's go ahead and try this again and this time instead of just putting in the Or one equals one, I'm going to go ahead and type in the last name Smith and then I'm going to add a command, delete from user_data.
And what's that going to do is remove all of the information from that table in the database. Now this time when I go back and try to search for records relating to Mr. Smith, you'll see there are no results remaining in the database. How can you prevent SQL injection attacks against your applications? Input validation. You have to check user input to make sure that it matches the expected format. If you're expecting a last name, you should have letters only, no apostrophes or equal signs in there.
SQL injection is just one form of injection attack. Similar attacks can occur against LDAP, XML, and other technologies where remote users can manipulate command parameters. In this demonstration you saw how a SQL injection attack allows the user of a web application to access the underlying database. In our first attack we simply added a one equals one to the end of a query to make the condition always true and display all of the contents of a database. We then got more malicious and deleted all of the records from that database.
SQL injection attacks allow dangerous, direct interaction between attackers and your databases. Input validation is essential to preventing SQL injection attacks.
NOTE: We are now a CompTIA Content Publishing Partner. Our training prepares members to pass CompTIA certification exams and become qualified IT professionals. As such, we are able to offer CompTIA exam vouchers at a 10% discount. For more information on how to obtain this discount, please download these PDF instructions.
- Comparing viruses, worms, and Trojans
- Understanding backdoors and logic bombs
- Defending against denial of service and password attacks
- Preventing insider threats
- Detecting social engineering attacks
- Preventing wireless eavesdropping
- Understanding cross-site scripting
- Preventing SQL injection
- Deterring attacks
- Securing your network
- Scanning for and assessing threats
Skill Level Intermediate
Q: This course was updated on 04/25/2016. What changed?
A: We updated eight movies to stay on top of the latest trends in IT security, and the latest objectives on the "Threats and Vulnerabilities" domain of the CompTIA Security+ exam.