SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Troublesome Names

By Phil Factor,

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

Total article views: 58 | Views in the last 30 days: 3
Related Articles

query like count(name),name,surname

query like count(name),name,surname


Meet the Geniiius Family

Over the next few weeks / months there will be plenty of opportunities to meet the Geniiius family, ...


Family Name Issue with Proper Case

Family Name Issue with Proper Case


Sport Lap time Input

Inputting sport times


SQL Family – an Update

At the beginning of the month, we had a Meme Monday on the topic of SQL Family.  I had a...

database weekly    
sql injection