«

»

Jan 04 2013

Print this Post

Why it’s easy being a hacker – A SQL injection case study

Finding SQL injections today is like picking apples from an apple tree. It’s very easy, and anyone can do it. Ask any hacker you want, SQL injection is everywhere. There have been many folks predicting the end of SQL Injection, however, year after year we see it in the OWASP Top 10.

In my presentations on the topic I’ve said that the reason for this is not necessarily only the developers fault, but just as much the material they are teaching themselves with. Now it is time to put bang to the buck and prove it!

Lets do a Google search

on e.g. “How to use PHP with MySQL” and check out the top 10 answers. I’ll do it for you and sum up the results:

  • 1st result: Vulnerable to SQL Injection
  • 2nd result: Does not show you how to handle user input. No use of prepared statements.
  • 3rd result: Same as above. No prepared statements, only hardcoded value examples.
  • 4th result: This is from php.net. It does not show how to query the database with user supplied values.
  • 5th result: Does not show you how to handle user supplied input.
  • 6th result: From w3schools.com. I changed the page to insert queries (from connecting to database) and they are all vulnerable to SQL Injection.
  • 7th result: Youtube result which does not introduce how to use user supplied input. I click the next link on Youtube and that video is vulnerable to SQL Injection.
  • 8th result: Vulnerable to SQL injection.
  • 9th result: This was an irrelevant result. Skipping to next one and it operates on hardcoded values, and does not introduce the developer to user supplied values.
  • 10th result: This one is from mysql.com. Vulnerable to SQL Injection!

So there you have it folks. The first 10 results on Google does NOT provide the developers with knowledge on how to properly use a database with user supplied input! Thank you for providing us security folks with jobs many years to come! There is no need to single-handedly blame the developers for SQL injection when the learning material is wrong from the beginning.

Of course some of these injections may be thwarted by say “PHP Magic Quotes” or other filtering services on the web/app-server.   This is however no excuse to not properly do safe queries in your examples. PHP.net even recommends turning off the magic quotes option.

What about books on programming?

If the Google searches does not provide us with quality information on how to develop proper code, then surely books written by professionals has to get this right? Nope…

PHP & MySQL: The Missing Manual
Pretty much the entire book contains SQL injection, until the end where the author finally introduces mysql_real_escape_string function to sanitize user input. The method is introduced in chapter 10.

The following chapter is “Security and the Real World” I wonder how many readers skip this chapter, because their web applications are already working as intended! They’ve learned how to program by now, so why not just get started?

On a side note, the book also doesn’t implement a proper password hashing algorithm and instead relies on using PHP’s crypt function. It does introduce salts, however, it is not enough to be considered a proper password algorithm.

Head First PHP & MySQL
In chapter 6 the reader is introduced to SQL Injection and how to thwart it with mysqli_real_escape_string(), but the sanitation method is not used throughout the rest of the book, leaving any readers that don’t read from page to page vulnerable. By not using proper sanitation for the rest of the book the users will of course forget to use sanitation as well.

For passwords the book introduces MySQL’s SHA() method, however, no proper password algorithm or salts are used.

In Appendix A (named “All the topics we didn’t cover) we find a topic called “Securing your PHP application”. These pages only contained a couple of pages on removing phpinfo() scripts and combating XSS. Disappointing.

Just Enough Web Programming with XHTML, PHP and MySQL
SQL injection in the examples provided in the book and I found no countermeasures described at all. The title of the book should instead be “Just Too little Web Programming…”.

Unfortunately, very many books first introduce the reader to vulnerable programs, then later in the book fixing past mistakes. Nots so often did I discover books that introduced security from the start, which is how I think you need to focus the education. Security needs to be inherent throughout the entire education of the users, if not they will forget it and choose the simple paths.

When doing this research you get very used to seeing queries like this:

It should not be necessary to buy security books in order to learn how to code properly!

It would be unfair to not mention some books that got it right. Do note however that I have not read these books page to page and because of that I cannot vouch for them being great books, except only on the fact that they do proper education regarding SQL injection.

Educational facilities

Unfortunately even the schools and courses get it wrong. I dug up the books I had to use when I studied at a Norwegian college, and these are my findings.

Programming in Java
Even though this course does not formally contain database operations, the book we are had to get contained some of itJust about the entire chapter about doing database queries is vulnerable to SQL injection. Prepared statements get a brief mention in the end, but only as a potential performance booster rather than for security reasons. Also there is no filtering done nor any mentions of potential security issues.

The book’s name in Norwegian: “Programmering i Java. 3. Utgave.

Web-programming in PHP

The book in this course, like most of the other books I’ve read, barely raise any security concerns over the course of the book. All of its database example code contains SQL injection all the way up to the point where the book brings in a dedicated chapter on security. Over the course of the book it teaches its readers to program unsafely, then finally, in one of the final chapters, letting everyone know that everything they did earlier is now wrong! Please erase what we put into your heads, you now have to do it this way…

But wait! In code examples in a later chapter of the book it seems the author forgot what he had just taught to the users. These code examples contain SQL injection as well! So there we are, back at square one.

For those interested, the code in the latter example looked like this:

A potential exploit would look like this: ?name=Chris&email=e@mail.com&id=1&tlf=phone’ WHERE 1=2–
Not very destructive but modifying this for something malicious is definitively easy. Examples of SQL injection on insert statements usually contains concatenating queries with semicolon injection, but I don’t like these examples as query concatenation rarely works these days.

The book’s name in Norwegian: “Webprogrammering i PHP. 2. utgave

A look into another students homework

A bit of Google Fu and I found a Norwegian students homework for his PHP and database classes (link in Norwegian). The homework is from November 2012 so its quite fresh. The code in the students homework clearly contains several SQL injection vulnerabilities. The SQL injection can be seen in this screenshot from the students blog:

No sanitation

I’ve asked the student if he had gotten any comments on the SQL injection from the teacher, however he has not replied me yet. I  am pretty sure the teacher did not comment on the SQL injection though, and I will update this post if I ever get a reply on it.

“Webdevelopment with Microsoft ASP.net MVC 3″ course
In 2012 I was sent to a course in ASP.net development with MVC 3. The course contained some database material where we used Entity Framework and LINQ.

To my surprise the course instructor did not have the proper sanitation in place for queries, and in fact the instructor did not know that hidden variables could be modified in a form submit, so that you could submit any value you wanted. When I taught him how to modify input variables in his own program, and I showed him how to do SQL injection, he found severe vulnerabilities in his how applications and on one of the customer pages that he had previously worked for.

So how to properly do SQL queries?

With PHP you should use prepared statements. You use for example the mysqli class or a PDO class to access the database functions, then mark the input values as a question mark and finally bind them with the bind_param function. The bind_param method takes two arguments, one which is the datatype (i for integer) and one with the actual data. The following code example shows you best practice when using the mysqli class (don’t forget to add error handling):

Much the same for ASP.net C#:

Ofcourse, in these examples you need to add the appropriate error handling.

Other thoughts on preventing SQL injection

  • Implement a secure coding regime
    • Escape user inputs, and make it obvious in your code wether or not the code is clean or dirty.
    • Parameterized queries as described above.
    • Defensive coding
    • Have code reviews
    • Teach your peers that security is incredibly important
  • Monitoring for attacks
    • Network Intrusion Detection System (NIDS)
    • Host Intrusion Detection System (HIDS)
    • Application Intrusion Detection System (AppIDS)
    • Log analysis
  • Block attacks
    • Application firewall
    • Database firewall
    • Web application firewall
      • Apache ModSecurity
      • Cisco Application Velocity System (AVS)
  • Probe for vulnerabilities
    • Automated blackbox injection testing
    • Static source code analysis
    • Manual penetration testing
  • Harden your database server
    • Least amount of privileges principle. Obey it!
    • No use of super users. No shortcuts on this one.
    • Patch your DBMS.

 

Interested in learning more on how to do it correct?

OWASP’s SQL Injection cheat sheet: https://owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
OWASP’s Input Validation cheat sheet: https://owasp.org/index.php/Input_Validation_Cheat_Sheet 
PHP Prepared statements (with appropriate error handling): http://php.net/manual/en/mysqli.quickstart.prepared-statements.php
For a list of PHP drivers, see: http://php.net/manual/en/pdo.drivers.php

Conclusion

Education needs to have security with it from the beginning. Security can’t finish last in the books and examples, it has to be included with the very first examples. And why should it not be? Why should you be teaching your peers on how to program in-securely, just so that you can later show them how it really should be done?

Chances are you will not get the proper education. It is obvious that everything from Google, school, books and courses get it wrong, thus breeding new generations of insecure programmers.

The authors of the books with problems mentioned will be notified as soon as possible.

I hope you enjoyed this article. Please do leave a comment!

PS: I have no doubt that we could all write this same article for the rest of the OWASP Top10.

It’s not fun when you’re next! (http://thepowerofapostrophe.blogspot.no)

Share

Permanent link to this article: http://www.securesolutions.no/why-its-easy-being-a-hacker/

  • JRWR

    I’m not the best PHP dev in the world but I know to mysql_real_escape() eveything I put into my database, I found this a long time ago and it should be well known, its not the best, but it helps with most cases

    • http://www.securesolutions.no/ Chris Andrè Dale

      Thanks for your comment. I’m glad you got your user input sanitated :)

    • Priapus

      ALWAYS assume your user can get past your “sanitize” functions and use parameterized queries. No excuses.

  • ccornutt

    One thing to note, if you go through PDO vs just straight mysqli, you can do things like “bind by name” (bindParam) for parameters and provide a correct data type they should match: http://php.net/manual/en/pdostatement.bindparam.php

  • http://twitter.com/brutelogic Brute Logic

    Another reason to that is the “long tail” of technology: in countries like Brazil, security is undeveloped as you can see in @VulnBrazil (a special edition of @VulnSites).

  • Jim Bird

    To be part of the solution, write another post similar to this called “How to use PHP with MySQL” and post it to every popular development community that you can find, so that some day that Google search will show your post in the top 10. Make it easy, make it simple, don’t spend a lot of time talking about SQL injection, just explain it briefly. If we are gong to reach developers, we need stuff written from the builder perspective explaining how to build stuff properly, with working code samples that they can copy and paste from, because developers who are writing code or learning to write code aren’t looking for something on SQL injection, they are looking for something on “How to use PHP/Java/…. with MySQL/Oracle/…”.

  • Caezar

    To be fair, at no time in the past were there ways to discover the right answer. Take the first GSM standards as an example: the only existing public source code had an initialization vector of 000000. Every phone operator used that “reference” algorithm. And the only network protocol was SMB which supported partial password string matching and a maximum length of 14 so you could brute force it in hundreds of tries. So while I think you’re right that never have the technorati had so many targets, I do think that the hard targets are WAY WAY harder today.

  • Kelsey Grammar

    *single-handedly not single handily

    • http://www.securesolutions.no/ Chris Andrè Dale

      Thanks :) I’ve updated accordingly. First I thought the word was misspelled, but they are actually both words with different meanings.

  • http://twitter.com/H_Mosafer Hamed mosafer

    tnx for your article

    I think it was better that you Fuzzing and software related issues as well as how to work with them on this point article

    the end of articel with apache modsecurity you can monitor & block Sqlinjection Attaks

  • Parsiya

    One problem that I’ve seen with ASP.Net C# is that SQL vulnerable queries are used (e.g. INSERT INTO test VALUES (+textbox1.text etc) but regular expressions are used on the actual input fields. This will mitigate the threat a bit but more often than not the code containing those queries are re-used in other parts (projects) without the accompanying regular expression. That’s why I prefer your suggested method of sanitizing data (+ using regex on input fields)

  • SysAdm

    First thing I learnt in college was “garbage in == garbage out”. Those who do not understand that this is true, should not be allowed to handle computers.

  • Michael

    All should know “Little Bobby Tables”

    http://xkcd.com/327/

  • anon

    Good information!

  • gwapo ko

    Very nice article! :D Keep posting..

    • http://www.securesolutions.no Chris Andrè Dale

      Thanks for kind words! Motivating to hear!