On Being an O’Malley—A Tale of Apostrophes and SQL Injection
Oct 23, 2013
I was recently checking into a hotel when the usual thing happened. The desk clerk told me he couldn’t find my reservation. “Nope, you’re not here. No Erin O’Malley.”
In the past, and due to my worry wart nature, I might’ve panicked, thinking, “Did I screw up? Did I not hit ‘reserve’?” But this time, I remained cool. Maybe even a bit overconfident.
I challenged the young man, “No, no. I’ve got a reservation. How about checking just Malley? Or O space Malley? Or O underscore Malley? Or Omalley? Just anything without the apostrophe.”
And lo and behold, there I was: Ms. Malley O.
The befuddled clerk was like, “That's so strange. Our system actually won't allow the apostrophe.”
Again, a too-cool me, “Yes, yes. I know. That’s because it can cause a security vulnerability.”
He looked at me now as if I were Wonder Woman. But, then again, that could have just been my tiara and arm cuffs.
Props really do need to go to my colleague, Kyle Adams. I’d previously turned to him for some answers. In addition to having “reservation” issues, I let him know that I’d also had problems with various software programs doing funny things with access or online payment systems giving me error messages.
“Kyle,” I said, “The bane of my name . . . the apostrophe . . . . Do you know what’s up with that?”
Of course, he did. And like a Grand Master of the Jedi Order, he’d told me how it was.
Basically, any name with an apostrophe will look innocent, but it's actually a test for SQL injection. If you get the right error, you can take over a server or download customer data. Poorly written sites tend to just block apostrophes versus fix the code.
The scenario often evolves like this:
Developers don’t bother putting in any restrictions.
If someone attacks them, they then freak and finally decide they need input validation—which really should have been done originally.
Developers may think, “What are the characters someone is likely to have in their name?” And they’ll usually settle on just the standard English alphabet—which is about the point where you stop being able to use apostrophes.
Though, sometimes, they will allow apostrophes because they think it’s a common case—which it is! But if they have allowed apostrophes, then, unfortunately and too often, someone finds a way to attack them with SQL injection.
At this point, it kind of comes full circle, where the developers react by simply disallowing apostrophes again—instead of fixing the way they store the data to truly resolve the issue.
The actual attack vector to test for SQL injection is: ‘ or ‘1’=’1
The usual solution: Prevent any potentially problematic characters—such as that darn apostrophe—from being submitted so that the data can be stored without worry of proper encoding.
The correct solution: Use prepared SQL statements to make it so any character is valid and won’t cause a problem.
Sounds to me like there really isn’t any excuse for denying apostrophe’s in someone’s name. What do you think? Has anything similar ever happened to you?