Life isn't easy if your last name is 'Null' as it still breaks database entries the world over

This was my thought as well, sanitize your inputs! Are they not quoting/casting to string before input?

It happened to a friend who wasn't passing in the proper types into their stored procedures, all strings, and "null" (not case sensitive) conflicted with actual null values. Everything in the web interface were strings, and so was null.

For some people it takes this mistake before they learn to always care about the data types you're passing in.

As a backbend dev, I blame DBAs. We were forced to support CSV imports from out support team so they could fix data issues on their own, and now we have some wonky data in prod...

It's funny because I also learned on [Object object].

Dammit, Bobby!

Oh. Yes. Little Bobby Tables, we call him.

Are there character escapes for SQL, to protect against stuff like that?

It's baffling to me. Maybe I'm just used to using "modern" frameworks, but the only way this could be an issue is if you literally check if the string value equals "null" and then replace it with a null value.

lastName = lastName.ToUpper() == "NULL" ? null : lastName;

Either that or the database has some bug where it's converting a string value of "null" into a null.

Code is easy in a vacuum. 50 moving parts all with their own quirks and insufficient testing is how you get stuff like this to happen.

How do devs make this mistake

it can happen many different ways if you're not explicitly watching out for these types of things

example let's say you have a csv file with a bunch of names

id, last_name
1, schaffer
2, thornton
3, NULL
4, smith
5, "NULL"

if you use the following to import into postgres

COPY user_data (id, last_name)
FROM '/path/to/data.csv'
WITH (FORMAT csv, HEADER true);

number 5 will be imported as a string "NULL" but number 3 will be imported as a NULL value. of course, this is why you sanitize the data (GIGO) but I can imagine this happening countless times at companies all over the country

there are easy fixes if you're paying attention

COPY user_data (id, last_name)
FROM '/path/to/data.csv'
WITH (FORMAT csv, HEADER true, NULL '');

sets the empty string to NULL value.

---

example with js

fetch('/api/user/1')
  .then(response => response.json())
  .then(data => {
    if (data.lastName == "null") {
      console.log("No last name found");
    } else {
      console.log("Last name is:", data.lastName);
    }
  });

if data is

data = {
  id: 5,
  lastName: "null"
};

then the if statement will trigger- as if there was no last name. that's why you gotta know the language you're using and the potential pitfalls

now you may ask -- why not just do

if (data.lastName === null)

instead? But what if the system you're working on uses JSON.parse(data) and that auto-converts everything to a string? it's a very natural move to check for the string "null"

obviously if you're paying attention and understand the pitfalls of certain languages (like javascript's type coercion and the particularities of JSON.parse()) it becomes easy but it's something that is honestly very easy to overlook

"True"

How do devs make off by one mistakes.

I can't even think of a language that does that. I don't think even JS does it, and if anything was going to it's fucking that.

I have never seen this happen, and I don't know what tools would confuse the strings "null" or "Null" with NULL. From the comments in this thread, there are evidently more terribly programmed systems than I imagined.

The article talks about a guy with a “NULL” license plate who gets tons of tickets for things he didn’t do so probably not the best plan

Hey leave the danish alphabet alone 😂