“The World’s Most Dangerous Punctuation Mark”
– aka –
“Why my work e-mail address sucks.”

What would you say if I told you there was a key on your keyboard that was so dangerous, so malicious and so feared by security experts that tens of thousands of websites have banned it from being used. No, it’s not that stupid Windows key sitting between the CTRL and ALT keys (who uses that Windows key, anyway?). It’s the apostrophe.

(Cue evil music: Duh duh DUUUUUUUUUUUUH.)

What’s so dangerous about an apostrophe, you might ask? Two words, my friends: SQL Injections. Right about now, half of you are going “Well, duh,” and the other half are saying, “What in the world is that?” Today’s post is for the second half. I promise, it’ll be fun.

SQL (pronounced “sequel”, like a movie sequel) is Microsoft’s enterprise database solution. SQL can hold millions and millions of entries and can sort and retrieve them really quickly. (There’s also a free version of SQL called MySQL. Everything I’m about to say applies to both products.) SQL databases are very easy to access through web languages, like ASP and PHP. Using scripting languages like these, we now have what we refer to as “dynamic web pages” — pages that change. Back in the old days, web pages were “static” (unchanging). An example of a dynamic page would be this website (robohara.com). You can view my blog entries by category, by date, or chronologically. Depending on what you click, the website displays the information in different ways. It’s dynamic.

For a web page to get information from a database, it has to do a query. For example, whenever you visit robohara.com, the website does a database query and says, “Show our visitor the most ten recent posts.” If you scroll down through the entire page, you’ll see ten posts. At the bottom there’s a button that says “Previous Entries.” If you click on that, my website does another query and says, “Show our visitor the ten posts before that.” If you use the search box and search for the word “Ketchup”, the website does a query that says, “Show our visitor all posts that include the word ‘Ketchup’.” (There are only two; I already looked.)

There are two very basic concepts to glean from this. The first is, with the right query, you can get any information from a database. The second, which follows logically, is we need to control what information the visitor is able to get from the database.

All queries are done with SELECT statements. That tells the database that I want to “select” records that meet my criteria. For example, in the above example involving ketchup, the query looks something like this:

SELECT * FROM posts WHERE text = ‘ketchup’

Even if you have never seen a SQL query before, you can probably figure out what’s going on there. Notice are the two smallest pieces of punctuation: apostrophes. You (the visitor) typed ketchup, which the website turned into a SQL query and went looking for posts that contained the word ketchup. Man, that’s great. These dynamic web pages are really something, aren’t they?

Well, guess what. SELECT isn’t the only thing you can do with SQL. You can add entries into the database using the INSERT command, you can change entries by using the UPDATE command, and (guess what) you can delete entries with the DELETE command. Obviously those are things I don’t want random readers doing! (Not that I don’t trust you guys …) You can search my whole website and you won’t find a button that says, “Delete all of Rob’s super awesome posts.” But, if you know how to do it, you can make your own.

SQL queries can be compound. For example, you can say, “Find me all posts that contain either ketchup or mustard.”

SELECT * FROM posts where text = ‘ketchup’ or text = ‘mustard’.

See those apostrophes? We’re about the become evil hackers and use them to our advantage. Imagine that instead of typing the word ketchup in the search bar, you typed the following:

ketchup’ or text = ‘mustard

That looks kind of weird, doesn’t it? But look what happens when we plug that query into our original query:

SELECT * FROM posts where text = ‘ketchup’ or text = ‘mustard‘.

Do you see what just happened? We just manually changed the SQL query? Oh boy; that’s bad news, and not just because now we’ve got people searching for ketchup and mustard all over the place. What happens when I search for ketchup’; DROP posts–? I get:

SELECT * FROM posts WHERE text = ‘ketchup’; DROP posts–

For the uninitiated, the first part of that SQL query looks for all posts that include the word ketchup. The second half instantly deletes the entire posts database. Do not pass go, do not collect $200 — those posts are gone, forever, instantly.

Another very simple and well-documented trick is bypassing logon credentials. Let’s say when I post on robohara.com, it asks for my username and password. After I enter that information in, the website does a query (that information is stored in the database too) to see if what I entered matches what’s in the database. Let’s say it looks like this:

SELECT * FROM Users where name = ‘Rob’ and password = ‘Password’

Ah, here again with the evil hackers. Let’s say a hacker tries to log in to my website. He enters my name (“Rob”), but for a password he enters ‘ or 1=1–. Now watch what happens to our query:

SELECT * FROM Users where name = ‘Rob’ and password = ‘Password‘ or 1=1–

In “computerese”, that says “Let me log as Rob if I got the right password, or if 1 = 1.” Those of you who made it through second grade may remember that one always equals one. For websites not protected against this kind of attack, well, you just let a hacker waltz in the front door with the keys to the kingdom.

Here’s a little “Come to Jesus” moment for you — anytime you’ve ever put your personal information online, it’s been stored in a database. With the right query, it can be retrieved. Last week, Monster.com got hacked for the third time, and by “got hacked” I mean “hackers got the user information from their database.” If you are one of the 75 million registered users of Monster.com, hackers now have your username, password, phone number, e-mail address, and so on. Remember your friendly IT guy telling you “not to use the same password on multiple sites?” This is why. And guess what, if you use “passwordMONSTER” as a way to fool people, I know what I’m going to start guessing as your password on Hotmail (“passwordHOTMAIL”), ebay (“passwordEBAY”), and so on. And by the way, I’m not singling Monster.com out — it just so happens they’re the ones that got it this week. The week before that Destructoid got hacked, the week before that it was the El Paso Credit Union, and the week before that it was the Universite of Rochester. University websites are great targets because (A) half the time the websites are written by students, and (B) university records usually include social security numbers. Kiss that credit rating goodbye, freshman! By the way, for those of you who don’t click on the links, all of these examples are from January of 2009.

Now, every single one of those examples I showed you had one thing in common; they all required the user to inject text into the SQL query — thus the term, SQL Injections. If you look closer though, you’ll see they all have something else in common; they all needed apostrophes to work. That’s how SQL queries are written, and to be able to perform injections, you need to be able to pass apostrophes to SQL. What’s the quickest way to block 99% of all SQL Injections? You got it … ban users from using the apostrophe.

“But Rob,” I hear you asking, “You used apostrophes in this post! And aren’t your posts stored in a SQL database?” Yes, and yes — that’s because WordPress, and many other websites, “sanitize” their input. In my database, apostrophes aren’t stored as apostrophes — they’re stored in a way that the database knows they’re text and not a real SQL command. It’s like naming your dog “Tree”. That doesn’t make him a tree (although they both “have bark”, nyuk nyuk nyuk …)

So, couldn’t web programmers sanitize all input? Sure, but that would take a lot of work. Instead, what a lot of people use are commands like this:

SQLQuery = Replace(Request.form(“TEXT”), “‘”, “”)

In ASP, what that does is take the query you submitted, and replace any apostrophes with “nothing.” If you try to send an apostrophe in a query, it just gets replaced with “nothing” and disappears. That only requires one of code. Unfortunately, it’s a really quick, dirty, and lazy way to deal with the problem. It causes all sorts of problems. Most advanced programmers don’t deal with apostrophes in this fashion. In fact if you go to my search bar on this page and search for a single apostrophe, you’ll find a whole bunch of them. There are more eloquent ways to fix the problem than just not allowing apostrophes.

Most web-based programs like eBay and PayPal and such will not let you put an apostrophe in your user name. (As you now see, it could cause problems.) Unfortunately for me, my wife and my dad, the e-mail system we use at work not only allows them, but assigns you an e-mail address based on your full name. Yes, that’s right — all three of us have work e-mail addresses that end in O’Hara, complete with the apostrophe.

So what happens when you go to a website that requires your work e-mail address, but won’t accept apostrophes? Well, we’re screwed. It happened to my dad last night, trying to create a trouble ticket on IKON’s website. I just verified it.

Oh well, no trouble ticket for me. :/

A few final words — all the SQL Injection examples I used in this post were found by searching Google for “SQL Injection Examples”. If you didn’t understand them, don’t worry — most kids do, kids that would gladly delete your database just for fun.

If you read this entire post and understood half of it, I hope you appreciate the following cartoon. Click to enlarge.