Prepare on Input, Escape on Output, pt. 1
*[XSS]: Cross Site Scripting *[SQL]: Structured Query Language *[PDO]: PHP Data Objects *[REST]: Representational State Transfer *[MVC]: Model-View-Controller *[LAMP]: Linux, Apache, MySQL, PHP ### How being prepared can prevent needing to escape ### **Note: I am **not** a security expert, but I have become enamored and fascinated by Web Security. This is what I have learned from reading and personal experience. If you know of a better way, or wish to correct something, *please do*! I am more than happy to learn and amend.** There has been a bit of talk on Forrst about XSS and the issue of escaping data, stripping input, and saving things to databases[^1].[^2] The two main security issues in play here are SQL Injection and Cross Site Scripting (XSS). This is going to be slightly in depth, so I am going to split it into two smaller and more digestible chunks. The first will deal with the basics of SQL Injection and parameterized queries, while the second will deal with XSS and escaping output. To begin, I'm going to weave a tale of a frustrated developer. You are the frustrated developer, working at a new Web 2.0 company. Your boss says she wants more user participation, because user participation is super 2.0, and has tasked you to create a comments section for the company's custom coded blog. You are on a shared hosting LAMP stack (boss wanted to go the cheap and easy route) and so you are stuck dealing with a relational database (MySQL) and PHP. You decide to make your form super simple, just the person's name [`name="name"`], their e-mail (you obviously want to grab their gravatars!) [`name="email"`], and their comment [`name="comment"`]. So you start out coding your PHP to interact with your MySQL database. You've got it all set up to connect properly to your aptly named `comments` table and now you are ready to store those pieces of data in the database. You don't think you have to worry security (or don't know too much about it) so you go the super simple way and make
// The comment data has been POSTed to this comment processing script $comment_name = $_POST['name']; $comment_email = $_POST['email']; $comment_body = $_POST['comment']; $query = "INSERT INTO comments VALUES ('" + $comment_name + "', '" + $comment_email + "','" + $comment_body + "')"; mysql_query($query);
And you think **bam, done**. But along comes a smart hacker, and figures that your code more than likely looks like that, so he writes a comment that looks like Name: Smart Hacker Email: [email protected] Comment: You are about to get hacked '); DROP TABLE comments-- Which then creates a query that looks like
INSERT INTO comments VALUES ('Smart Hacker', '[email protected]', 'You are about to get hacked '); DROP TABLE comments-- ')
When your query runs this, you no longer have a comments database! This is a properly formed SQL query that first inserts a value into the database and then drops the entire table (the `--` is a SQL comment telling it to ignore the rest of the statement). **This is known as a [SQL Injection](https://www.owasp.org/index.php/SQL_Injection).** Albeit, this is a *very* simplistic version of one, but the basic concept is the attacker can execute potentially malicious SQL and interact with your database without you knowing. Whether that's logging in as an admin, changing someone's password to compromise their account, dropping your tables. It's **bad news bears**. The proper way to prevent against this is to use Parameterized Queries. In PHP, this is conveniently contained within [PDO](http://php.net/manual/en/book.pdo.php), available in PHP5. You read up on PDO and figure out that you can rewrite that query a parameterized query as
try { // You've already created a PDO connection saved with a handle in $handle $comment_name = $_POST['name']; $comment_email = $_POST['email']; $comment_body = $_POST['comment']; $comment_query = $handle->prepare('INSERT INTO comments (name, email, comment) value (:name, :email, :comment)'); $comment_query->bindParam(':name', $comment_name, PDO::PARAM_STR); $comment_query->bindParam(':email', $comment_email, PDO::PARAM_STR); $comment_query->bindParam(':comment', $comment_body, PDO::PARAM_STR); $comment_query->execute(); } catch (Exception $e) { echo "Something somewhere didn't work. Try again, please?"; }
And you are spared from a primary SQL injection attack[^3]. Please note that this doesn't help you against 2nd order SQL injections, and you should be doing other things such as input validation (client and server side). Hopefully, if this type of blog post is well received, I will cover them both of those in the future. Next up: Part 2, Escape on Output, or how I learned to prevent XSS and still maintain REST/MVC. References and Resources: * [SQL Injection Prevention Cheat Sheet](https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Introduction) * [Injection Attacks by OWASP](http://www.youtube.com/watch?v=pypTYPaU7mM&NR=1) on Youtube [^1]: https://forrst.com/posts/Backend_form_validation-sHl#comment-364326 [^2]: https://forrst.com/posts/XSS_protection_Any_tips-ILg [^3]: Some would suggest that I validate or sanitize my inputs, and [Never Use `$_GET` again](http://www.phparch.com/2010/07/never-use-_get-again/). I will explain my rationale against this in the next section.
