Demonstrating the possible dangers of SQL injection

Question

Students building websites in a high-school computer science major (using HTML, javascript, jsp and MySQL) don't seem to understand the dangers of SQL injections. I try to explain that unchecked SQL statements can really ruin their databases, but just telling them doesn't get the message through.

The students build small websites as a project, and they work on that project for one semester (they do this during the 2^nd semester. In the first one they are taught HTML, css, javascript, jsp and MySQL. _{^{Rather efficient, isn't it?}}).

The project guidelines specify they need login and registration pages, and a users database (among other requirements). After teaching how a login\sign up page might work I explain a bit about SQL injection. However, the students simply don't see the dangers SQL injection poses. I explain that I can cause the entire users table to be deleted just by signing in, but just saying it apparently isn't enough.

So, how can I demonstrate the dangers of SQL injection?

Note: This isn't a self-learning question (details can be found in the comments).

Answer 1

The best way to show your students that their work is vulnerable to SQL injection is to demonstrate it. I'd suggest that you challenge the students to "hack" each others projects using SQL injection. You could then give bonus points for the students who are able to find the most vulnerabilities and for the students who didn't leave that attack vector open.

That would be a fun way for students to learn by doing. This activity could also serve as an intro to what SQL injection is. Students could discover what SQL injection is themselves, and have a fun time doing it while also getting feedback on how their own code is weak.

You could then go on to discuss the broader consequences of SQL injection with the entire class and go into real life instances of SQL injection vulnerabilities.

Answer 2

A couple suggestions -

1. The classic xkcd comic about Bobby Tables:

Shown, complete with the explain xkcd article can provide a nice, humorous introduction that will get them to start paying to attention.

2. Sites like hack this site are completely legal and allow users to experiment and learn about vulnerabilities (in this case, using them to solve levels and challenges, but it will teach them how to avoid them in their own code). You do need to create an account to access any of the challenges, but it's a great site. I'm pretty terrible at it, but it's fun to try to figure out. Multiple challenges in the basic level involve SQL injection.

3. Let them try it on their own programs or on a dummy website or two that you set up.

Answer 3

If you search 'computerphile SQL injection' on google, it should bring you to the link below. The man in this video explains SQL injection very clearly and uses a practical example as well by breaking into a vulnerable website he created for the video's purposes.

https://www.youtube.com/watch?v=ciNHn38EyRc

PLEASE NOTE HE USES MySQL in this video.

He explains SQL injection by first showing how the information transfers between the client and server.

Once he shows how the site is vulnerable to SQL injection, he starts off searching for

';--

He explains how the above command will show all the products his test website offers.

After querying all the products, he searches for

hammer' AND 1 = SLEEP(2);--

This is a special part of the video because he's actually identifying whether or not the website uses MySQL by using the SLEEP(seconds) command. The SLEEP(seconds) command works correctly, so he knows the website uses MySQL. This is an example of blind sql injection (which he also explains).

He then shows how you can exploit SQL testing purposes to locate hidden information by searching for

hammer' UNION (SELECT 1,2,3 FROM dual);--

The fact that he can output this query successfully proves he can find more sensitive information such as user passwords.

He then shows just how serious SQL injection can be by searching for

hammer' UNION (SELECT TABLE_NAME, TABLE_SCHEMA, 3 FROM information_schema.tables);--

He now has shown every table the website's database has.

Eventually he gets to the page where passwords are stored. To be practical, he does hash the passwords so people don't think all passwords are simply stored in plain text.

I hope I explained this well enough!

Answer 4

I would allow them to create initial websites without any mention of security at first. I would also have created your own web site (using exactly the same techniques and technologies) in advance. (Doing it in advance allows you time to plan out and test a SQL injection attack string that will work the first time - this way you can avoid the initial trial-and-error fuzzing when you do your actual demonstration.)

After they have created their sites, I would pull up my own site, and use it to talk through whatever elements of html, css, javascript, jsp and MySQL you would like to call attention to. After you have gone through your lesson, it's time for the big reveal.

I would ask the class something along the lines of, "how much did we worry about security when we made these sites?" And then follow up with "and the reason that matters is because... check this out."

Perform the SQL injection attack. Drop a table, break the site, change a password... just make sure that the result is fairly dramatic. Watch their jaws drop.

At this point, you could turn around and do exactly the lab activity that thesecretmaster suggested in his answer. The demonstration and the lab together would form a powerful lesson in the importance of sanitizing your inputs.

Answer 5

A good way to should this would be to do a two part assignment (don't tell the students it's a two part assignment though). For the first part, have them do something fairly simple, but give them a library to interface with their sql databases. Code that library with a SQL Injection vulnerability (this way you won't have to go hunting for one in each app).

Invite the students to walk through the first part of the project with you during office hours or during class. It'll work better if other students don't get the surprise, but I'm not sure how that would work in a high school setting. During the demonstration, trigger your sql injection vulnerability to wipe the database (If you feel like being nice, you can take a snapshot). They might panic a bit, but tell them not to worry about it, you'll go over it in the next class. In the next class, explain the purpose of your sql library and how it opened them up to an injection. For the second part of the assignment, have them turn in copy of your library, but with the injection vulnerability fixed.

This will not only teach them about sql injection, but might have the nice side effect of teaching them to be aware that even if their code is fine, libraries provided to them might not be perfectly secure. If you want to explicitely mention this though, it wouldn't be a bad idea to also explain the pitfalls of ahering to Not Invented Here Syndrome.

Answer 6

I liked this article by Gunter Ollmann, and his accompanying tweet.

I think it could be used to get the idea across of how someone tried to attack a traffic offences database using SQL injection. Obviously the traffic camera system is doing optical character recognition on vehicles' number plates, and the attacker hoped he could inject a DROP DATABASE command. It is uncertain whether the attack worked or not.

Answer 7

YouTube has many videos about SQL injection, live demos etc.

When starting to explain sql injection, this YouTube video would be a good choice in my opinion because it has a very simple source code and offers extreme vulnerability by having full login access after injection. So it should be clear at first sight for students why this is a real big problem.

I think it's a good and easy to understand example, especially for people who are not aware of sql injection threats. The example server code is written in PHP but so easy that's should be no problem to read for anybody able to write jsp files.

Why is this a good video for students not aware about SQL injection? (in a nutshell)

• "Ups" effect (after SQL injection the attacker is able to login)
• Very simple to example, so easy to understand
• Author does explain steps with drawings
• All sources and data are visible inside video
• To try this out there is no special "magic" hacktool necessary, just brain + computer and keyboard

A description of the steps inside the video:

• create a SQL table called "users" having "username" and "password" as columns in a database.
• create a "loginform.html":

<form action="loginpage.php" method="POST">
  Username: <input type="text" name="username"/>
  Password: <input type="text" name="password"/>
  <input type="submit"/>
</form>

• create a "loginpage.php":

<?php 
// create connection to database
$mysqli = new mysqli("localhost", "dbuser","password","databasename");

// fetch post data to variables
$user = $_POST['username'];
$pwd  = $_POST['password'];

// create (vulnerable) SQL
$sql = "SELECT * from users where username = '$user' and password = '$pwd'";

// execute SQL
$result = $mysqli->query($sql);

// handle login
if ( $fetched_row = $result->fetch_row() ){
    // found a row for given username with password...
    echo "login successful!";
}else{
    echo "login failed";
}

// close connection
$mysqli->close();

?>

• hack the page by entering no_one as user name and enter ' OR ''=' as password. When submit button is pressed login is successful. The reason is the resulting SQL:

select * from user where username='no_one' and password='' OR ''=''

It should be very easy to adopt the PHP example to a JSP variant.

What the solution?

• Use prepared statements...
  In Java/JSP and also in PHP it comes out of the box, but has to be used...

Answer 8

I like the ideas of a two part assignment, but this would be my suggestion:

• Semester 1 : Run the project as before and mark it as expected, for example out of 20.
• Semester 2 : Explain security, including SQL injection. Set them the task of hardening their semester one projects, and tell them that you will be remarking the project. This time they start off with their mark from semester one, and you will then deduct the following:
  • 1 point for listing the schema.
  • 1 point for listing the users.
  • 1 point for listing plaintext passwords.
  • 1 point for cracking hashed passwords.
  • 1 point for changing a password and then logging in.
  • 2 points for deleting the users.
  • 2 points for dropping the user table.
  • 4 points for dropping the entire database.

Finally if they think security isn't important then relate this recent story that ignorance really isn't bliss Firefox gets complaint for labeling unencrypted login page insecure. Read the comments to see how quickly it went from story to passwords and credit card details obtained to database dropped to web site hosed.