Part of the work I do here is building custom web applications that users can interact with. A recent one is a simple feedback form that enables visitors to a client’s website to send quick suggestions. It looks like this:
Once someone enters their suggestion and clicks “Send,” I use Jquery and Ajax to submit the message to a PHP script which then processes the message, saves it to a MySQL database for later review, and emails it to my client. In just a few weeks, my client has received hundreds of highly valuable and actionable suggestions ranging from praise to notices about serious bugs.
Whenever you allow visitors to submit text through your website, security is a concern that must be dealt with. If you simply grab the message the user submits and send it to your database as-is, you leave an open door to SQL injection where a malicious user can include tricky code in their message that makes your database do nasty things.
Enter PHP Data Objects (PDO) and Prepared Statements. The beauty of these is that malicious visitors can include all the nasty tricky code they want in their message, but the database will never try to execute it because the message is kept completely separate from the commands. The best plain English explanation I’ve seen comes from an answer to a stackoverflow.com question and reads as follows:
When a query is sent to a data base, it’s typically sent as a string. The db engine will try to parse the string and separate the data from the instructions, relying on quote marks and syntax. So if you send “SELECT * WHERE ‘user submitted data’ EQUALS ‘table row name’, the engine will be able to parse the instruction.
If you allow a user to enter what will be sent inside ‘user submitted data’, then they can include in this something like ‘…”OR IF 1=1 ERASE DATABASE’. The db engine will have trouble parsing this and will take the above as an instruction rather than a meaningless string.
The way PDO works is that it sends separately the instruction (prepare(“INSERT INTO …)) and the data. The data is sent separately, clearly understood as being data and data only. The db engine doesn’t even try to analyze the content of the data string to see if it contains instructions, and any potentially damaging code snipet is not considered.
So great, you are thinking, if I use this complicated PDO thing with database queries I can make my website really secure and no longer have to use a bunch of cumbersome data sanitizers. Thats exactly where I was not long ago, but then I found a great series of videos on YouTube that showed exactly how it all works. The only gripe I had with the series is that it was hard to figure out which order to view them in, so with that in mind, here they are in order: