Security

On Being an O’Malley—A Tale of Apostrophes and SQL Injection

By Elevate posted 10-23-2013 07:00

  

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?

12 comments
0 views

Permalink

Comments

07-07-2018 14:14

 

Many sites about my last name:

"Illegal character in your last name"

Eh, that's a space and since when are those illegal?! I hope you did not call the police??

 

This site's comment form:

"Be sure to enter a unique name. You can't reuse a name that's already in use."

 

Also many sites helpfully 'correct' the use of capitals in my last name. All too often they end up changing 'Stijn de Witt' to 'Stijn Dewitt'. Thanks developers! I know how to spell my own name, thank you very much!

 

Please, IT teachers, teach your students to not put unreasonable restrictions on people's names. They are not unique, contain 'funny' characters, spaces etc. Deal with it!!

11-25-2013 13:54

O', I wonder...

11-25-2013 13:49

Will there be a SQL?

11-01-2013 09:37

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! 🙂

10-31-2013 20:06

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

10-29-2013 09:25

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.

 

10-29-2013 09:17

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?"

10-28-2013 08:53

Nice use of apostrophes. Thank you, Scott!

10-28-2013 08:09

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

10-23-2013 11:21

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

10-23-2013 09:29

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

10-23-2013 09:24

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