16
$\begingroup$

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 2nd 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).

$\endgroup$
  • 5
    $\begingroup$ It could simply be that the students want a grade and so don't see the point if you don't take off points for it. $\endgroup$ – thesecretmaster Jun 27 '17 at 10:59
  • 1
    $\begingroup$ @thesecretmaster But that's not what I'm asking. I'm asking how I can demonstrate it. $\endgroup$ – ItamarG3 Jun 27 '17 at 11:00
  • 1
    $\begingroup$ This feels like a semi-hidden self-learning question. If it is truly a self-learning question, then that's okay (encouraged, even), but then post it as such, and you don't need to couch the learning in terms of what other people need (i.e. "the students simply don't see..."). If it's not a self-learning question, then the answer is very simple: just perform a SQL injection attack on one of their sites in front of them, and watch their jaws drop. $\endgroup$ – Ben I. Jun 27 '17 at 14:04
  • 9
    $\begingroup$ What if you were to do a two part assignment? Give them a library through which to route their sql queries and make this library vulnerable to sql injection. Have them come in and show you their project using that library, and then in the middle of the demo, wipe their database using the libraries vulnerability. Tell them not to worry about it, you will address it in the next lecture and in the next lecture, tell the class about injection. In the second part, give them the source to the library, and have them fix the injection. This also teaches them to be concerned about libraries they use. $\endgroup$ – Sidney Jun 27 '17 at 14:53
  • 5
    $\begingroup$ Pictures do a great job, especially when you the teacher aren't the source. xkcd.com/327 There used to be a site where you could practice these types of things, trying to trigger specific errors, which would give you a username/password to get to the next step of the game... unfortunately, the domain went bye-bye and is now owned by some squatter... haven't found a replacement resource... $\endgroup$ – ivanivan Jun 27 '17 at 21:35
33
$\begingroup$

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.

$\endgroup$
  • 1
    $\begingroup$ I did a project like this when teaching about trust within the client / server model. As a class we designed a simple online banking web app with a single table database allowing you to log in and check your balance. As the class learnt more about SQL we explored different attack vectors and looked at how to stop them from working. $\endgroup$ – pddring Jun 27 '17 at 15:21
  • 6
    $\begingroup$ Just make sure all the students have offline backsups first. It's all fun and games until the pen testers destroy a production system afterall. $\endgroup$ – Dan Neely Jun 27 '17 at 20:42
  • $\begingroup$ On the other hand, as soon as they start using prepared statements, which should be from the get-go, they will no longer be vulnerable. Better to attack, say, WebGoat - and to know that a lot of old-school sites are still vulnerable. $\endgroup$ – tucuxi Jun 28 '17 at 11:05
  • 2
    $\begingroup$ @tucuxi I think that's the point the OP is trying to make to his students. "Don't do it the stupid way and you close off a huge vulnerability." $\endgroup$ – Dan Neely Jun 28 '17 at 12:24
  • $\begingroup$ @tucuxi "As soon as they start using prepared statements" - well, this sounds like something you'd only do a few times, and stop doing once the students start really thinking about security. $\endgroup$ – immibis Jun 29 '17 at 9:24
16
$\begingroup$

A couple suggestions -

  1. The classic xkcd comic about Bobby Tables:

enter image description here

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

  1. 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.

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

$\endgroup$
  • $\begingroup$ The XKCD example doesn't work with most database APIs, because they don't allow multiple statements. It's a good comic, but if you try to use it as a demonstration you won't get the problem across. $\endgroup$ – Barmar Jun 27 '17 at 18:37
  • 2
    $\begingroup$ Hacking into bad password validation using ' OR 1=1; -- is a good, short demonstration $\endgroup$ – tucuxi Jun 28 '17 at 11:06
  • $\begingroup$ @tucuxi Assuming they're comparing passwords in the clear instead of hashing them first. Of course, if they aren't hashing, that's another thing they need to learn about. $\endgroup$ – Ray Jun 28 '17 at 21:43
11
$\begingroup$

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!

$\endgroup$
  • 1
    $\begingroup$ Thanks! Could you specify in the answer what's the example? (Not being lazy, I will watch the video, but it's preferable if you added the example to your answer) $\endgroup$ – ItamarG3 Jun 27 '17 at 14:46
  • 1
    $\begingroup$ Hi, ecarl, and welcome to Computer Science Educators! This is a nice answer, and I hope we hear more from you in the future :) $\endgroup$ – Ben I. Jun 27 '17 at 14:54
7
$\begingroup$

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.

$\endgroup$
5
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ I like this answer very much. Also, welcome to Computer Science Educators! $\endgroup$ – Ben I. Jun 28 '17 at 13:16
  • $\begingroup$ I don't like the idea of giving them a vulnerable library. If I was the student, I would understand better if I was shown the vulnerability in my own code. If you have a predetermined library vulnerability it looks no different to a backdoor - all it demonstrates is that the professor's libraries can't be trusted. The point of a security demonstration should be to show people that their own code can't be trusted! $\endgroup$ – immibis Jun 29 '17 at 9:28
  • $\begingroup$ @immibis I had considered that, but doing the same thing with an individual's code for each student would require the instructor to fig l find a SQL injection risk in each application. This is why I suggested having the students fix the injection risk provided by the instructor. $\endgroup$ – Sidney Jun 29 '17 at 11:08
  • $\begingroup$ @Sidney Then I suggest picking one student's code and demonstrating that to the whole class - the point is that you didn't write the code so you didn't plant the vulnerability there. Hopefully most students will see how their own code has the same bug, but any student who doesn't understand the demonstration can come to you individually, like with any other problem. $\endgroup$ – immibis Jun 29 '17 at 22:42
5
$\begingroup$

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 cameras are doing image 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.

enter image description here

$\endgroup$
  • 2
    $\begingroup$ Welcome to Computer Science Educators! Wow. This is a very amusing suggestion. I'll add it to the end of the lesson plan. Thanks! $\endgroup$ – ItamarG3 Jun 28 '17 at 11:16
3
$\begingroup$

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...
$\endgroup$
  • 1
    $\begingroup$ Welcome to Computer Stack Educators! Could you please describe in your answer what in the youtube video is good? what's the example there? It would be very good to add the example from the video to your answer. $\endgroup$ – ItamarG3 Jun 28 '17 at 7:41
1
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ Hi Chris! Welcome to Computer Science Educators! It's great that you suggested an actual project and grading criteria. $\endgroup$ – thesecretmaster Jun 28 '17 at 15:27
  • $\begingroup$ I don't understand, they are high school students, how do you suggest they manage to crack hashed passwords? $\endgroup$ – ItamarG3 Jun 28 '17 at 15:29
  • $\begingroup$ The OP/teacher does that whilst marking their hardened projects in semester 2. I'm in the UK so don't know what age these student are, but if they are being taught this stuff it seems reasonable they should be able to prevent it occurring. Note I have edited my answer to clarify that the students are given the task of hardening their semester one projects prior to them being marked in semester two. That bit was in my head but didn't manage to make it into the answer first time ;-) $\endgroup$ – Chris Jun 28 '17 at 16:00
  • 1
    $\begingroup$ @Chris the students are 16 years old. I'm sorry to say, but they are nowhere near the level of cracking a hash. Some might manage to do it. Regardless, I think it's much better to award points for successfully preventing the listed attacks, rather than dock points off from students who did not manage to do something that is far beyond their level. $\endgroup$ – ItamarG3 Jun 28 '17 at 16:50
  • $\begingroup$ Okay, maybe a marking system that promotes positive reinforcement rather than marking them down would be more appropriate for 16 year olds. However, what they have to be taught is that you don't need to be a genius to do something like crack a hash. All you need is to download a tool with a decent dictionary and away you go. If you have a modern GPU you can crack tens of thousands in a minute $\endgroup$ – Chris Jun 29 '17 at 7:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.