Nothing makes me more frustrated than working to maintain a nicely formatted resume and have my potential employers ask me, "hey, can you just gimme a plain text version of that pdf?" Well, they never actually ask me that, but their web application does by showing me a "resume" field for me to submit my resume in this small, plain text box. Usually a couple thoughts go through my head at this point. Does the company who I am interviewing to develop web applications for have a web application that can handle the submission of files? If not, do I wish to work for this employer? I then grumble, switch to a new tab in my browser for several minutes, and then return to begin the sad and questionable process of converting my resume into ASCII art.
The particular application form that prompted this rant included an extra special field I hadn't seen before, a "code snippet". There was no further description for it. This insightful plain text field inspired me to enter the following:
'; delete from applicants where name!='Taylor Phillips';
If you aren't sure what this is, it's a simple SQL injection attack. Basically, this type of attack messes with the database and makes it do something that I specify in one of their fields. In this case, I told SQL to delete all entries from the table containing applicants. Except of course, me.
This attack works by changing the SQL string the server is going to execute. For example, if they used PHP and had:
$sql = "update applicants set code_snippet='".$_POST['code_snippet']."' where etc...";
Then the code I entered into their form would change their query to this
$sql = "udate applicants set code_snippet='';
delete * from applicants where name!='Taylor Phillips';
where etc..."
which means I broke their query and made it into three queries, two of which don't work. The middle query (my special code), however, will probably still be executed and will remove all other applicants from their database! This of course relies on the fact that there table and fields are named what I called them...
To prevent this type of attack, you can simply clean the fields before you write them to the database. You can do this by not allowing certain characters or by escaping invalid characters. This solution isn't the best because you either reduce what the user can do with your field and every time you insert or retrieve data you have to do a linear pass over the string, which could be costly large production systems. The real solution is to use whats called "prepare statements".
Basically, prepare statements work by first telling your database that your query will take some specific form. In other words, you send the server the SQL you want to execute, but with no values in it. After SQL gets that, then you send over values separately. This way SQL can never get confused about what you actually want to happen AND its faster to insert/retrieve data. The downside of this is that it requires sending two queries for each desired query. This isn't a huge deal for the trade-offs your getting, but even this downside is negated. If you want to execute some query with multiple sets of values then this can actually save you bandwidth because you only have to send your query structure once!