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:
$query = "INSERT INTO blog_entry VALUES
(NOW(), '" . $_POST[blog_title] . "', '".
$_POST[blog_summary] . "', '" .
$_POST[blog_content] . "', NULL);
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.
- PHP and MySQL Phrasebook. This book contained proper input sanitation and prepared statements.
- PHP & MySQL: Novice to Ninja, fifth edition. Good examples with security in focus from the beginning. It introduces prepared statements right away, and also discourages the use of PHP’s Magic Quotes.
- Learning PHP, MySQL, Javascript & CSS, 2nd edition. Introduces sanitation very early and keeps sanitation throughout the book. Very good.
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 it. Just 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:
$sql = "INSERT INTO table VALUES '%s', '%s', '%d', '%s'"; mysql_query(sprintf($sql, $_GET['name'], $_GET['email'], $_GET['id'], $_GET['tlf']));
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:
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):
$mysqli = new mysqli("example.com", "user", "password", "database");
$stmt = $mysqli->prepare("INSERT INTO test(number,text) VALUES (?, ?)");
$stmt->bind_param("i", $_POST["number"]);
$stmt->bind_param("s", $_POST["text"]);
$stmt->execute();
Much the same for ASP.net C#:
SQLCommand cmd = new SQLCommand("INSERT INTO test VALUES(@number, @text)", connection)
cmd.Parameters.Add("@number", SqlDbType.Int)
cmd.Parameters.Add("@text", SqlDbType.NText)
cmd.Parameters.AddWithValue("@number", 1)
cmd.Parameters.AddWithValue("@text", "One")
cmd.ExecuteNonQuery()
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.
