Answers.Body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T15:21:39.030Z
Answers.Text: 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.
Answers.UserDisplayName: pddring
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T20:42:53.493Z
Answers.Text: 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.
Answers.UserDisplayName: Dan Is Fiddling By Firelight
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T11:05:05.407Z
Answers.Text: 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.
Answers.UserDisplayName: tucuxi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T12:24:55.203Z
Answers.Text: @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."
Answers.UserDisplayName: Dan Is Fiddling By Firelight
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-29T09:24:31.217Z
Answers.Text: @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.
Answers.UserDisplayName: Criticize SE actions means ban
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-29T12:53:31.313Z
Answers.Text: @DanNeely my point is that the students may feel cheated if they are requested to invest too much time writing insecure code and then breaking it (by using vulnerable string-concatenation queries) instead of being shown how to avoid this much earlier (by using prepared statements). Burned fingers teach, but they may resent you for setting them up.
Answers.UserDisplayName: tucuxi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T11:13:07.917Z
Answers.LastActivityDate: 2017-06-27T15:59:06.857Z
Answers.LastEditDate: 2017-06-27T15:59:06.857Z
Answers.UserDisplayName: thesecretmaster
Answers.Body: <p>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.</p>
  
  <p><a href="https://www.youtube.com/watch?v=ciNHn38EyRc" rel="noreferrer">https://www.youtube.com/watch?v=ciNHn38EyRc</a></p>
  
  <p>PLEASE NOTE HE USES MySQL in this video.</p>
  
  <p>He explains SQL injection by first showing how the information transfers between the client and server. </p>
  
  <p>Once he shows how the site is vulnerable to SQL injection, he starts off searching for</p>
  
  <pre><code>';--
  </code></pre>
  
  <p>He explains how the above command will show all the products his test website offers.</p>
  
  <p>After querying all the products, he searches for</p>
  
  <pre><code>hammer' AND 1 = SLEEP(2);--
  </code></pre>
  
  <p>This is a special part of the video because he's actually identifying whether or not the website uses MySQL by using the <code>SLEEP(seconds)</code> command. The <code>SLEEP(seconds)</code> command works correctly, so he knows the website uses MySQL. This is an example of blind sql injection (which he also explains).</p>
  
  <p>He then shows how you can exploit SQL testing purposes to locate hidden information by searching for</p>
  
  <pre><code>hammer' UNION (SELECT 1,2,3 FROM dual);--
  </code></pre>
  
  <p>The fact that he can output this query successfully proves he can find more sensitive information such as user passwords.</p>
  
  <p>He then shows just how serious SQL injection can be by searching for</p>
  
  <pre><code>hammer' UNION (SELECT TABLE_NAME, TABLE_SCHEMA, 3 FROM information_schema.tables);--
  </code></pre>
  
  <p>He now has shown every table the website's database has.</p>
  
  <p>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.</p>
  
  <p>I hope I explained this well enough!</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T14:46:07.270Z
Answers.Text: 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)
Answers.UserDisplayName: ItamarG3
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T14:54:22.593Z
Answers.Text: Hi, ecarl, and welcome to [cseducators.se]!  This is a nice answer, and I hope we hear more from you in the future :)
Answers.UserDisplayName: Ben I.
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T14:43:44.473Z
Answers.LastActivityDate: 2017-06-27T15:07:48.533Z
Answers.LastEditDate: 2017-06-27T15:07:48.533Z
Answers.UserDisplayName: ecarl
Answers.Body: <p>A couple suggestions - </p>
  
  <ol>
  <li>The classic xkcd comic about Bobby Tables:</li>
  </ol>
  
  <p><a href="https://i.sstatic.net/jXOyV.png" rel="noreferrer"><img src="https://i.sstatic.net/jXOyV.png" alt="enter image description here"></a></p>
  
  <p>Shown, complete with the <a href="https://www.explainxkcd.com/wiki/index.php/327:_Exploits_of_a_Mom" rel="noreferrer">explain xkcd</a> article can provide a nice, humorous introduction that will get them to start paying to attention.</p>
  
  <ol start="2">
  <li><p>Sites like <a href="https://www.hackthissite.org/missions/basic/" rel="noreferrer">hack this site</a> 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.</p></li>
  <li><p>Let them try it on their own programs or on a dummy website or two that you set up.</p></li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T18:37:13.127Z
Answers.Text: 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.
Answers.UserDisplayName: Barmar
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T11:06:44.853Z
Answers.Text: Hacking into bad password validation using `' OR 1=1; --` is a good, short demonstration
Answers.UserDisplayName: tucuxi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T21:43:51.190Z
Answers.Text: @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.
Answers.UserDisplayName: Ray
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T15:24:09.007Z
Answers.LastActivityDate: 2017-06-27T15:24:09.007Z
Answers.UserDisplayName: auden
Answers.Body: <p>I liked <a href="http://blog.ioactive.com/2013/03/sql-injection-in-wild.html" rel="nofollow noreferrer">this article</a> by Gunter Ollmann, and his <a href="https://twitter.com/gollmann/status/313393642922442752" rel="nofollow noreferrer">accompanying tweet</a>.</p>
  <p>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.</p>
  <p><a href="https://i.sstatic.net/5OOIq.jpg" rel="nofollow noreferrer"><img src="https://i.sstatic.net/5OOIq.jpg" alt="enter image description here" /></a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T11:16:57.643Z
Answers.Text: Welcome to Computer Science Educators! Wow. This is a very amusing suggestion. I'll add it to the end of the lesson plan. Thanks!
Answers.UserDisplayName: ItamarG3
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-06-18T13:32:22.663Z
Answers.Text: Extreme funny example - how not to do ;-) Thx.
Answers.UserDisplayName: de-jcup
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2017-06-28T11:14:49.993Z
Answers.LastActivityDate: 2020-06-19T15:56:00.317Z
Answers.LastEditDate: 2020-06-19T15:56:00.317Z
Answers.UserDisplayName: Reversed Engineer
Answers.Body: <p>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).</p>
  
  <p>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. </p>
  
  <p>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 <a href="https://en.wikipedia.org/wiki/Not_invented_here" rel="noreferrer">Not Invented Here Syndrome</a>.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T13:16:40.113Z
Answers.Text: I like this answer very much. Also, welcome to [cseducators.se]!
Answers.UserDisplayName: Ben I.
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-29T09:28:52.453Z
Answers.Text: 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!
Answers.UserDisplayName: Criticize SE actions means ban
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-29T11:08:09.303Z
Answers.Text: @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.
Answers.UserDisplayName: Sidney
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-29T22:42:17.567Z
Answers.Text: @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.
Answers.UserDisplayName: Criticize SE actions means ban
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T17:18:35.393Z
Answers.LastActivityDate: 2017-06-27T17:18:35.393Z
Answers.UserDisplayName: Sidney
Answers.Body: <p>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 <em>fuzzing</em> when you do your actual demonstration.)</p>
  
  <p>After they have created their sites, I would pull up my own site, and use it to talk through <em>whatever elements</em> 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.  </p>
  
  <p>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."</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T14:17:38.697Z
Answers.LastActivityDate: 2017-06-27T15:55:17.630Z
Answers.LastEditDate: 2017-06-27T15:55:17.630Z
Answers.UserDisplayName: Ben I.
Answers.Body: <p>YouTube has many videos about SQL injection, live demos etc.</p>
  
  <p>When starting to explain sql injection, this <a href="https://www.youtube.com/watch?v=v_wyUp_jBrs" rel="nofollow noreferrer">YouTube video</a> 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.</p>
  
  <p>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.</p>
  
  <p><strong>Why is this a good video for students not aware about SQL injection?</strong> (in a nutshell) </p>
  
  <ul>
  <li>"Ups" effect <em>(after SQL injection the attacker is able to login)</em></li>
  <li>Very simple to example, so easy to understand</li>
  <li>Author does explain steps with drawings</li>
  <li>All sources and data are visible inside video</li>
  <li>To try this out there is no special "magic" hacktool necessary, just brain + computer and keyboard</li>
  </ul>
  
  <p><strong>A description of the steps inside the video:</strong></p>
  
  <ul>
  <li>create a SQL table called "users" having "username" and "password" as columns in a database.</li>
  <li>create a "loginform.html":</li>
  </ul>
  
   
  
  <pre><code>&lt;form action="loginpage.php" method="POST"&gt;
    Username: &lt;input type="text" name="username"/&gt;
    Password: &lt;input type="text" name="password"/&gt;
    &lt;input type="submit"/&gt;
  &lt;/form&gt;
  </code></pre>
  
  <ul>
  <li>create a "loginpage.php":</li>
  </ul>
  
  
  
  <pre><code>&lt;?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-&gt;query($sql);
  
  // handle login
  if ( $fetched_row = $result-&gt;fetch_row() ){
      // found a row for given username with password...
      echo "login successful!";
  }else{
      echo "login failed";
  }
  
  // close connection
  $mysqli-&gt;close();
  
  ?&gt;
  </code></pre>
  
  <ul>
  <li>hack the page by entering <code>no_one</code> as user name and enter <code>' OR ''='</code> as password. When submit button is pressed login is successful. The reason is the resulting SQL:</li>
  </ul>
  
  
  
  <pre><code>select * from user where username='no_one' and password='' OR ''=''
  </code></pre>
  
  <p>It should be very easy to adopt the PHP example to a JSP variant. </p>
  
  <p><strong>What the solution?</strong> </p>
  
  <ul>
  <li>Use prepared statements...<br>
  In Java/JSP and also in PHP it comes out of the box, but has to be used...</li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T07:41:02.453Z
Answers.Text: 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.
Answers.UserDisplayName: ItamarG3
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T07:39:18.917Z
Answers.LastActivityDate: 2017-06-28T18:06:14.530Z
Answers.LastEditDate: 2017-06-28T18:06:14.530Z
Answers.UserDisplayName: de-jcup
Answers.Body: <p>I like the ideas of a two part assignment, but this would be my suggestion:</p>
  
  <ul>
  <li>Semester 1 : Run the project as before and mark it as expected, for example out of 20.</li>
  <li>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:
  
  <ul>
  <li>1 point for listing the schema.</li>
  <li>1 point for listing the users.</li>
  <li>1 point for listing plaintext passwords.</li>
  <li>1 point for cracking hashed passwords.</li>
  <li>1 point for changing a password and then logging in.</li>
  <li>2 points for deleting the users.</li>
  <li>2 points for dropping the user table.</li>
  <li>4 points for dropping the entire database.</li>
  </ul></li>
  </ul>
  
  <p>Finally if they think security isn't important then relate this recent story that ignorance really isn't bliss <a href="https://arstechnica.com/security/2017/03/firefox-gets-complaint-for-labeling-unencrypted-login-page-insecure/" rel="nofollow noreferrer">Firefox gets complaint for labeling unencrypted login page insecure</a>. 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.  </p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T15:27:11.530Z
Answers.Text: Hi Chris! Welcome to [cseducators.se]! It's great that you suggested an actual project and grading criteria.
Answers.UserDisplayName: thesecretmaster
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T15:29:57.353Z
Answers.Text: I don't understand, they are high school students, how do you suggest they manage to crack hashed passwords?
Answers.UserDisplayName: ItamarG3
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T16:00:47.183Z
Answers.Text: 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 ;-)
Answers.UserDisplayName: Chris
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T16:50:58.347Z
Answers.Text: @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.
Answers.UserDisplayName: ItamarG3
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-29T07:59:51.227Z
Answers.Text: 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
Answers.UserDisplayName: Chris
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T15:20:13.863Z
Answers.LastActivityDate: 2017-06-28T16:02:57.380Z
Answers.LastEditDate: 2017-06-28T16:02:57.380Z
Answers.UserDisplayName: Chris
Body: <p>Students building websites in a high-school computer science major (using <code>HTML</code>, <code>javascript</code>, <code>jsp</code> and <code>MySQL</code>) 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. </p>
  
  <p>The students build small websites as a project, and they work on that project for one semester (they do this during the 2<sup>nd</sup> semester. In the first one they are taught <code>HTML</code>, <code>css</code>, <code>javascript</code>, <code>jsp</code> and <code>MySQL</code>. <sup><sub>Rather efficient, isn't it?</sub></sup>). </p>
  
  <p>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 <code>users</code> table to be deleted just by signing in, but just saying it apparently isn't enough.</p>
  
  <p>So, how can I demonstrate the dangers of SQL injection? </p>
  
  <p>Note: This <em>isn't</em> a self-learning question (details can be found in the comments).</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-12-13T05:47:27.907Z
Comments.Text: Correct implementations are not subject to injection attacks. So why worry about injection when you seem not to be interested in correct answers--and probably, clear specifications? (Rhetorical.)
Comments.UserDisplayName: philipxy
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-27T10:59:13.227Z
Comments.Text: 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.
Comments.UserDisplayName: thesecretmaster
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-27T11:00:28.797Z
Comments.Text: @thesecretmaster But that's not what I'm asking. I'm asking how I can demonstrate it.
Comments.UserDisplayName: ItamarG3
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-27T14:04:51.737Z
Comments.Text: 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.
Comments.UserDisplayName: Ben I.
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-27T14:53:35.023Z
Comments.Text: 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.
Comments.UserDisplayName: Sidney
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-27T16:35:11.120Z
Comments.Text: Show the problem, and show the alternative (the solution). I have been stuck in the past knowing that I am doing it wrong, but saw no alternative. I was therefore stuck doing the wrong behaviour (and having deep knowing that it was wrong)
Comments.UserDisplayName: ctrl-alt-delor
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-27T21:35:24.640Z
Comments.Text: Pictures do a great job, especially when you the teacher aren't the source.  https://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...
Comments.UserDisplayName: ivanivan
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-28T11:36:03.740Z
Comments.Text: Comments are not for extended discussion; this conversation has been [moved to chat](http://chat.stackexchange.com/rooms/61210/discussion-on-question-by-itamar-green-demonstrating-the-possible-dangers-of-sql).
Comments.UserDisplayName: thesecretmaster
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-29T20:56:21.870Z
Comments.Text: I have a response that doesn't answer the question.  Ditch MySQL in favor of the equally free postgres, not because my flavor of RDBMS is better than yours, but because postgres supports prepared statements which the typical MySQL installation does not. Teach them to do everything through prepared statements, in which the SQL is passed to the RDBMS for precompile, and after that only the reference to the compiled access path plus paramaters are passed, so SQL injection is not possible.  Fringe benefit: eliminate 50 to 80% of the overhead of every SQL call.
Comments.UserDisplayName: pojo-guy
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-30T07:08:00.900Z
Comments.Text: Teach ethics prior to having students do anything like this. Obtain written acknowledgement from students that accessing computing resources without written permission from the owner, including manipulating a database, can be felony under the CFAA.
Comments.UserDisplayName: Bennett Brown
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-30T07:08:21.683Z
Comments.Text: Assign students to groups and have each group member provide other group members written permission to attack their site. Or provide a classroom site and have students add their name to a table `Successful_hackers`. The classroom will suffer -- providing a teachable moment -- if any student vandalizes the site by dropping the table.
Comments.UserDisplayName: Bennett Brown
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-07-01T16:12:27.630Z
Comments.Text: Is there a reason you're still teaching JSP, when it's really been hugely declining in popularity and relevance over the last ten years? Why aren't you teaching something similar to what would be used on a new project in industry - MVC is *not* that hard a concept to understand? I'd also echo the comments suggesting teaching prepared statements from the start.
Comments.UserDisplayName: Adam Williams
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-07-01T16:16:31.437Z
Comments.Text: @AdamWilliams Unfortunately, It's the school curriculum. It might be changing this year (finally!)...
Comments.UserDisplayName: ItamarG3
Tags: <lesson-ideas><secondary-education><sql-injection>
Title: Demonstrating the possible dangers of SQL injection
ThreadText: # Demonstrating the possible dangers of SQL injection
  
  (Asked by: username_0 on 2017-06-27)
  
  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).
  
  **Tags:** lesson-ideas, secondary-education, sql-injection
  
  > **Comments:**
  > **username_2**: 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.  
  > **username_0**: @username_2 But that's not what I'm asking. I'm asking how I can demonstrate it.  
  > **username_3**: 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.  
  > **username_4**: 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.  
  > **username_5**: Show the problem, and show the alternative (the solution). I have been stuck in the past knowing that I am doing it wrong, but saw no alternative. I was therefore stuck doing the wrong behaviour (and having deep knowing that it was wrong)  
  > **username_6**: Pictures do a great job, especially when you the teacher aren't the source.  https://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...  
  > **username_2**: Comments are not for extended discussion; this conversation has been [moved to chat](http://chat.stackexchange.com/rooms/61210/discussion-on-question-by-itamar-green-demonstrating-the-possible-dangers-of-sql).  
  > **username_7**: I have a response that doesn't answer the question.  Ditch MySQL in favor of the equally free postgres, not because my flavor of RDBMS is better than yours, but because postgres supports prepared statements which the typical MySQL installation does not. Teach them to do everything through prepared statements, in which the SQL is passed to the RDBMS for precompile, and after that only the reference to the compiled access path plus paramaters are passed, so SQL injection is not possible.  Fringe benefit: eliminate 50 to 80% of the overhead of every SQL call.  
  > **username_8**: Teach ethics prior to having students do anything like this. Obtain written acknowledgement from students that accessing computing resources without written permission from the owner, including manipulating a database, can be felony under the CFAA.  
  > **username_8**: Assign students to groups and have each group member provide other group members written permission to attack their site. Or provide a classroom site and have students add their name to a table `Successful_hackers`. The classroom will suffer -- providing a teachable moment -- if any student vandalizes the site by dropping the table.  
  > **username_9**: Is there a reason you're still teaching JSP, when it's really been hugely declining in popularity and relevance over the last ten years? Why aren't you teaching something similar to what would be used on a new project in industry - MVC is *not* that hard a concept to understand? I'd also echo the comments suggesting teaching prepared statements from the start.  
  > **username_0**: @username_9 Unfortunately, It's the school curriculum. It might be changing this year (finally!)...  
  > **username_1**: Correct implementations are not subject to injection attacks. So why worry about injection when you seem not to be interested in correct answers--and probably, clear specifications? (Rhetorical.)  
  
  ---
  
  ## 8 answers
  
  ### Answer by username_2 on 2017-06-27. Score: 37 | Accepted answer
  
  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.
  
  > **Comments:**
  > **username_10**: 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.  
  > **username_11**: 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.  
  > **username_12**: 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.  
  > **username_11**: @username_12 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."  
  > **username_13**: @username_12 "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.  
  > **username_12**: @DanNeely my point is that the students may feel cheated if they are requested to invest too much time writing insecure code and then breaking it (by using vulnerable string-concatenation queries) instead of being shown how to avoid this much earlier (by using prepared statements). Burned fingers teach, but they may resent you for setting them up.  
  
  ---
  
  ### Answer by username_15 on 2017-06-27. Score: 18
  
  A couple suggestions -
  
  1. The classic xkcd comic about Bobby Tables:
  
  [![enter image description here](https://i.sstatic.net/jXOyV.png)](https://i.sstatic.net/jXOyV.png)
  
  Shown, complete with the [explain xkcd](https://www.explainxkcd.com/wiki/index.php/327:_Exploits_of_a_Mom) article can provide a nice, humorous introduction that will get them to start paying to attention.
  
  2. Sites like [hack this site](https://www.hackthissite.org/missions/basic/) 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.
  
  > **Comments:**
  > **username_16**: 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.  
  > **username_12**: Hacking into bad password validation using `' OR 1=1; --` is a good, short demonstration  
  > **username_17**: @username_12 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.  
  
  ---
  
  ### Answer by username_14 on 2017-06-27. Score: 11
  
  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!
  
  > **Comments:**
  > **username_0**: 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)  
  > **username_3**: Hi, username_14, and welcome to [cseducators.se]!  This is a nice answer, and I hope we hear more from you in the future :)  
  
  ---
  
  ### Answer by username_3 on 2017-06-27. Score: 7
  
  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 username_2 suggested in his answer. The demonstration and the lab together would form a powerful lesson in the importance of sanitizing your inputs.
  
  
  ---
  
  ### Answer by username_18 on 2017-06-28. Score: 6
  
  I liked [this article](http://blog.ioactive.com/2013/03/sql-injection-in-wild.html) by Gunter Ollmann, and his [accompanying tweet](https://twitter.com/gollmann/status/313393642922442752).
  
  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.
  
  [![enter image description here](https://i.sstatic.net/5OOIq.jpg)](https://i.sstatic.net/5OOIq.jpg)
  
  > **Comments:**
  > **username_0**: Welcome to Computer Science Educators! Wow. This is a very amusing suggestion. I'll add it to the end of the lesson plan. Thanks!  
  > **username_19**: Extreme funny example - how not to do ;-) Thx.  
  
  ---
  
  ### Answer by username_4 on 2017-06-27. Score: 6
  
  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](https://en.wikipedia.org/wiki/Not_invented_here).
  
  > **Comments:**
  > **username_3**: I like this answer very much. Also, welcome to [cseducators.se]!  
  > **username_13**: 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!  
  > **username_4**: @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.  
  > **username_13**: @username_4 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.  
  
  ---
  
  ### Answer by username_19 on 2017-06-28. Score: 3
  
  YouTube has many videos about SQL injection, live demos etc.
  
  When starting to explain sql injection, this [YouTube video](https://www.youtube.com/watch?v=v_wyUp_jBrs) 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...
  
  > **Comments:**
  > **username_0**: 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.  
  
  ---
  
  ### Answer by username_20 on 2017-06-28. Score: 2
  
  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](https://arstechnica.com/security/2017/03/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.
  
  > **Comments:**
  > **username_2**: Hi username_20! Welcome to [cseducators.se]! It's great that you suggested an actual project and grading criteria.  
  > **username_0**: I don't understand, they are high school students, how do you suggest they manage to crack hashed passwords?  
  > **username_20**: 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 ;-)  
  > **username_0**: @username_20 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.  
  > **username_20**: 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  
  
  ---
  
  