Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I read an article on BBC. One of the examples they said was that people with surname 'Null' are having problems with entering their details in some websites.

No explanation is given about the error they are facing.

But as far as I know the string 'Null' and the actual Null value is completely different (from a database point of view).

Why would this cause problems in a database?

share|improve this question
2  
This is a somewhat famous blog article about assumptions that programmers make about names, written by one of the people quoted in that BBC article: kalzumeus.com/2010/06/17/… – Jörg W Mittag Mar 25 at 17:00
12  
Relevant xkcd – David Grinberg Mar 25 at 18:03
2  
See also: stackoverflow.com/questions/4456438/… – Foon Mar 25 at 18:55
4  
The first time I saw this guy on TV I assumed it was a database bug. Then I found out it's actually his name. – Nate Eldredge Mar 25 at 23:20
2  
@JarrodRoberson How can you say the "entire premise is false", given the description of the issues faced by "Jennifer Null" and the like-named in the link the OP posted? It's a real issue that faces real end-users. – Steven Burnap Mar 26 at 20:44
up vote 90 down vote accepted

It doesn't cause database problems. It causes problems in applications written by developers that don't understand databases. At the root of the problem is that much database-related software displays a NULL record as the string NULL. When an application then relies on the string form of a NULL record (likely also using case-insensitive comparison operations), then such an application will consider any "null" string to be NULL. Consequently a name Null would be considered to not exist by that application.

The solution is to declare non-null columns as NOT NULL in the database, and to not apply string operations to database records. Most languages have excellent database APIs that make string-level interfaces unnecessary. They should always be preferred, also since they make other mistakes such as SQL injection less likely.

share|improve this answer
29  
In this case, however, if you read the article in question, making a last name field NOT NULL will cause a whole set of problems for other people. "Some individuals only have a single name, not a forename and surname." – mikeTheLiar Mar 25 at 13:27
34  
@Darkhogg lots of people disagree with me about this but I think that names are like email addresses - don't bother validating them, give the user a single text box and let them put whatever they want. This is information that if I really need it I will get it from you in a way that is certain to be correct. – mikeTheLiar Mar 25 at 13:57
8  
@mikeTheLiar I don't know the name for this but there is a whole class of errors that come out of creating overly restrictive rules on data. Often you'll see postal codes and telephone numbers defined as numeric in applications and databases. They aren't really numbers because it makes no sense to do mathematical operations on them. So when someone tries to enter a Canadian address, they are stuck. – JimmyJames Mar 25 at 14:11
17  
@JimmyJames yeah, zip codes stored as numerics and suddenly anyone living here has an base-8 zip code. "If you're not doing math with it, it's a string, Full Stop." – mikeTheLiar Mar 25 at 14:13
7  
@mikeTheLiar. The problem with treating names as a single string (usually preferable, I agree) is when there's a requirement for alphabetical sorting by surname. – TRiG Mar 25 at 17:45

To answer your specific question there are many steps along the chain of events between a web form and the database. If the last name Null is erroneously interpreted as a NULL value then the system may reject a perfectly valid name as being invalid. This can happen at the database layer as explained by amon. Incidentally if this is the specific issue then the database is also probably open to SQL injection AKA the Bobby Tables attack. Another step in the chain that could be causing problems is the serialization process.

Overall the article was about a bigger problem. The world is a big messy place that doesn't always conform to our assumptions. This is especially apparent when you try to internationalize your application. At the end of the day we need to ensure our applications handle and encode our data properly. It is up to the business to decide how many resources we dedicate towards supporting increasingly complicated edge cases. While I fully support being inclusive, I will understand if the business decides that "the artist formally known as Prince" needs to use a Unicode character to represent his name in our database.

share|improve this answer
    
It's hard to imagine this being caused by the sort of unsafe string interpolation that can lead to SQL injection. If you forget to quote user input in an SQL query (e.g. INSERT INTO users (first, last) VALUES($first, $last) evaluates to INSERT INTO users (first, last) VALUES(Jennifer, Null)) everyone whose names aren't valid SQL keywords or column names are just going to throw errors and not have their records inserted either. The cause must be more complex. – Andrew Medico Apr 19 at 23:07
    
@AndrewMedico in your straw man example yes but there are lots of ways to do things wrong. Never underestimate the power of <strike>stupidity<\strike> ignorance. The bottom line is we have no idea what is the actual problem because we can't review the code in question – Erik Apr 19 at 23:13

Well, before it's entered into the database, it's a DOM element, then a javascript variable passed around, validated, and manipulated, then a JSON value, then a variable in whatever backend JSON library you're using, then a variable passed around, validated, and manipulated in your backend programming language, then an element of some sort of DAO, then part of a SQL string. Then to get the value back out, you do it all in reverse. That's a lot of places for programmers to make mistakes, and usually a lot of it without the benefit of static typing.

share|improve this answer

Most likely its a programming issue. If you look at this answer here on how NULLs are being passed you could easily cause some undesired behavior if you were "Mr. Null".

http://stackoverflow.com/questions/4620391/mysql-and-php-insert-null-rather-than-empty-string

You can see that if some data element was passed as NULL the data would be interpolated as a database null in the database.

"NULL" != Database Null

Some use cases and related behavior...

Let's say last name was marked in the database as not null, now when data is inserted it will be interpreted as a NULL and fail the insert.

Another case is let's say the last name was nullable in the database. Mr. NULL is inserted and is transformed into DBNull.Value which is not the same as "NULL". After the insert we can't find Mr. Null because his last name is not "NULL" but in reality a database null value.

So, those would be 2 cases of problems. As @Amon points out, databases themselves have no issues with nulls, although one should understand how nulls are handled in each RDMS instance as there will be differences between different vendors.

share|improve this answer
    
"You can see that if some data element was passed as NULL the data would be interpolated as a database null in the database." - the linked SO question/accepted-answer does not appear to show this? – w3dk Apr 21 at 11:00

I would attribute the problem to sloppy programming and poor design of some implementations of SQL. "Null" the name should always be presented and interpreted with quotes. null, the database value, should always be presented without quotes; but when writing ad-hoc code, it's easy to slip into the "anything will do" paradigm and accept things believed to be a string in unquoted form.

This is compounded by the fact that other types of data; numbers for example, can and are accepted in either form because the interpretation is unambiguous.

share|improve this answer
    
You mean poor implementations of applications using SQL, surely? No serious implementation of an RDBMS itself would be vulnerable to this (just as no serious application is!) – underscore_d Apr 19 at 10:39

Most of the existing answers focus on the non-SQL parts of an application, but there may be a problem in SQL too:

If instructed to filter out records where a user's last name is not available, someone who doesn't understand SQL very well may write a filter WHERE u.lastname != 'NULL'. Because of the way SQL works, this will appear to check whether u.lastname IS NOT NULL: all NULL records get filtered out. All non-NULL records remain.

Except of course for records where u.lastname == 'NULL', but there may not have been any such record available during testing.

This becomes more likely if the SQL is generated by some sort of framework, where that framework doesn't expose an easily accessible way to check for non-NULL-ness with parameters, and someone notices "hey, if I pass in the string NULL, it does exactly what I want!"

share|improve this answer

A problem, fundamentally, is that the term "null" is applied two different database concepts, sometimes using context to distinguish between them:

  1. Something doesn't have a known value
  2. Something is known to have no value

While context can sometimes suffice to distinguish between those concepts, there are times when it really doesn't. If one is using a record to hold a search query, for example, there should be a difference between saying "I want someone by the name of [whatever], with no last name", versus "I want someone whose first name is [whatever] but whose last name is unknown." Many database engines have a bias toward one meaning or the other, but they're not all the same. Code which is expecting a database engine to work one way may malfunction if run on a different engine that runs differently.

share|improve this answer
    
If a string is known to have no value, then the value should be an empty string, not a null string. – Byron Jones Apr 19 at 20:52

protected by gnat Mar 27 at 15:36

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.