Security is top-of-mind, especially right here where Juniper experts share their insights on the latest security trends and breakthroughs
Recognized Expert Recognized Expert , Recognized Expert Recognized Expert Recognized Expert
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.”Wonder Woman small.jpg


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?

Oct 23, 2013
Andy Anderson

Wow! A techie! If you really want to impress people you should do your blog in french.5245

Oct 23, 2013
Recognized Expert Recognized Expert

Il y avait un temps . . . But hey, merci quand meme!

Oct 23, 2013
andy anderson

Hey! Who you callin' a temp....I got a full time job.

Oct 28, 2013

Another' great' article'' Erin''''''

Oct 28, 2013
Recognized Expert Recognized Expert

Nice use of apostrophes. Thank you, Scott!

Oct 29, 2013
Administrator Administrator

I have the same problem with the hyphen in my last name, but didn't realize that it may be a security "feature". Makes for questions such as, "How do I pronounce your last name?"

Oct 29, 2013
Recognized Expert Recognized Expert

I had another friend tell me the same thing about her hyphenated name. I can hear you now, "Try Sarah Lesway. Or Sarah Ball. Or Sarah Ball Lewsay." Or do you get Sarah L. Ball, too? Now that I'm thinking about it, maybe I got off easy with just an apostrophe!


Thanks for the comment.


Oct 31, 2013
Administrator Administrator

Thanks, Erin! You imagined right. Also, "Sarah Leswayball ... that's a weird name!" 🙂

Nov 1, 2013
Recognized Expert Recognized Expert

Maybe it's just that certain procedures are so rote that folks don't even stop to consider such things? How about this one? I was at another hotel recently, checking out, and the receptionist asked, "Did you enjoy your stay, Mr. O'Malley?" I was standing right in front of her. And, I mean, c'mon, I'm Wonder Woman! 🙂

Nov 25, 2013
Will there be a SQL?