SQLServerCentral Editorial

Troublesome Names

,

One of the ways that is often suggested for preventing SQL Injection is to validate the inputs. If, for example, you were accepting input from a name field, you should check the input for any SQL statements. Any seasoned developer will flinch at the idea. The input of any application comes in a surprisingly rich variety, even in its every-day legitimate usage.

There are many legitimate names that cause big problems to poorly-written computer applications. After years of misery, Christopher Null famously went public on his woes with his famous blog post, Hello, I'm Mr. Null. My Name Makes Me Invisible to Computers.

"Most will accept "Null" without complaint. Some will loop back to the input screen and tell the user to try again, that the last name field can't be blank (But it's not blank! That's just my name!) Some will tell the user that "Null" is a reserved term that can't be used. And some will just crash."

In fact, Null is a name with a long history, originally from Donegal in Ireland, and meaning an Ulsterman.

Punctuation in names has always caused problems too. Some years back, so many people with apostrophes in their name, such as O'Brien, found that so many applications crashed around their ears that many dropped the centuries-old usage. The problem was that developers would merely concatenate a string into a SQL Statement within the application.

$SQLString = "insert into Customer (surname) select '"+name+"'"

It could even be that a struggling O'Neil discovered SQL Injection when he finally snapped, and typed into the Surname field….

O';Truncate table customer –

…to spite the lazy programmer.

So, surely, we really should be checking the input by looking for SQL keywords or punctuation? Even with names derived from Europe we're in trouble. We can't look for signs of someone attempting to elevate permissions, because Grant'is the name of the current PASS president. We can look for Table, surely? No, the Table family exist, and derive their name from their origins in Tapeley, in Devon UK. OK but surely nobody is called 'Alter'? Well, that is actually both a surname and a given name, a Yiddish word derived from alt, meaning 'old'. The Delete family derive their name from the French name Dillet. The Join family is well-known in France, and so are the Joines in Britain. The Case family (name from the Old Norman French word 'casse') thrive in New York. The Files family come from Lancashire. There are people with the surname Drop, from Norfolk. The With family come originally from Middlesex, There are many companies with Select in their name. There is a record of a Create family in the 1750s but the name is probably no longer current. Mercifully, there is nobody called FillFactor. You might be safe in counting DBCC as a dodgy string, but not much else. (Thanks to William Brewer for the information).

To add to our difficulties, not only are the plenty of apostrophes in surnames, but also a whole range of punctuation, including full stops, question marks, hyphens and en-dashes. For the full gamut of variation in names, see the classic Personal names around the world.

You might think that creating a simple name-and-address database is a simple thing that can be safely left to the cub programmer, but in fact it can be one of the more awkward tasks that developer's face.

Phil Factor

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating