I was NaN years old when I learned this.
Life isn't easy if your last name is 'Null' as it still breaks database entries the world over
Submitted 2 months ago by cm0002@lemmy.world to technology@lemmy.world
Comments
recursiveInsurgent@lemm.ee 2 months ago
Scrollone@feddit.it 2 months ago
It’s funny because I also learned on [Object objects].
sugar_in_your_tea@sh.itjust.works 2 months ago
And here I am at undefined years old, learning for the first time.
solrize@lemmy.world 2 months ago
/me changes name to
'); DROP TABLE STUDENTS; –.funkajunk@lemm.ee 2 months ago
Oh. Yes. Little Bobby Tables, we call him.
ZILtoid1991@lemmy.world 2 months ago
Are there character escapes for SQL, to protect against stuff like that?
solrize@lemmy.world 2 months ago
Yes but it’s a dangerous process. You should use paramatrized queries instead.
purplemonkeymad@programming.dev 2 months ago
Use parameters, that way data and queries are separate.
Septimaeus@infosec.pub 2 months ago
Input sanitation typically handles this as a string that only includes characters supported by the data type of the table in question. While in transit, the strings might be escaped at certain stages, such as via URL encoding. Though this is considered poor practice in many applications, it’s not uncommon to see. The point, however, is to prevent the evaluation of inputs as anything other than their intended type, whether or not reserved characters are present.
sugar_in_your_tea@sh.itjust.works 2 months ago
Only noobs get hit by this (called SQL injection). That’s why we have leads review code…
Chewbaccabra@lemmy.world 2 months ago
NULL!= ‘NULL’How do devs make this mistake
blackn1ght@feddit.uk 2 months ago
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 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.Slaxis@discuss.tchncs.de 2 months ago
That is something I’ve had to do on rare occasions because people set up and store info in stupid ways…
kava@lemmy.world 2 months ago
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 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
dataisdata = { 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 overlookChewbaccabra@lemmy.world 2 months ago
Like you said, GIGO, but I can’t say I’m familiar with any csv looking like that. Maybe I’m living a lucky life, but true null would generally be an empty string, which of course would still be less than ideal. From a general csv perspective, NULL without quotes is still a string.
If “NULL” string, then lord help us, but I would be inclined to handle it as defined unless instructed otherwise. I guess it’s up to the dev to point it out and not everyone cares enough to do so. My point is these things should be caught early.
I’ll admit I’m much more versed in mysql than postgres.
IcyToes@sh.itjust.works 2 months ago
“True”
kogasa@programming.dev 2 months ago
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.
Gonzako@lemmy.world 2 months ago
How do devs make off by one mistakes.
sugar_in_your_tea@sh.itjust.works 2 months ago
The most common source of security vulnerabilities is memory corruption and off by one errors.
DragonTypeWyvern@midwest.social 2 months ago
My academic advisor in college was named Null
Even I kept running into trouble because the system thought I didn’t have a registered advisor.
ploot@lemmy.blahaj.zone 2 months ago
I have never seen this happen, and I don’t know what tools would confuse the string “null” with NULL. From the comments in this thread, there are evidently more terribly programmed systems than I imagined.
Atomic@sh.itjust.works 2 months ago
Shit happens, mistakes are sometimes made. Valve once had code that could delete your entire drive.
DragonTypeWyvern@midwest.social 2 months ago
I’m pretty sure at least some of the university’s systems were designed by students.
shotgun_crab@lemmy.world 2 months ago
As long as there’s javascript somewhere, anything can happen
sugar_in_your_tea@sh.itjust.works 2 months ago
Two likely reasons:
- CSV got involved somewhere
- JavaScript
ramble81@lemm.ee 2 months ago
Knew a guy who had the license plate ‘NULL’ and he was telling me how he never got a toll bill or red light ticket.
afronaut@lemmy.cafe 2 months ago
Lmao, I knew a guy from grade school with the last name Null.
Shardikprime@lemmy.world 2 months ago
Friend of little Bobby I presume
gedaliyah@lemmy.world 2 months ago
How about XÆa-12? Asking for a friend.
Shardikprime@lemmy.world 2 months ago
mysticpickle@lemmy.ca 2 months ago
KeenFlame@feddit.nu 2 months ago
I bet, I can’t even read this article without confusion
undefined@lemmy.hogru.ch 2 months ago
I’ve been doing web development for something like 20 years now and I just can’t imagine how shitty your backend is if this is an issue.
timuchan@lemm.ee 2 months ago
This was my thought as well, sanitize your inputs! Are they not quoting/casting to string before input?
undefined@lemmy.hogru.ch 2 months ago
Unless you’re coding from scratch it’s hard to not do this with any modern framework.
livingcoder@programming.dev 2 months ago
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.
Dasus@lemmy.world 2 months ago
With LLM coding increasing, it might be going up. Idk am no pro, just worried.
Tangential, but I find it hilarious how Gemini’s syntax fucks up all the time.
I ask it to change my light called “CX2” to red. It complies, like usual, and it reads Okay, changing “CX2” to red., but what it says out loud is Okay, changing "CX two inches to red.
sugar_in_your_tea@sh.itjust.works 2 months ago
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…
undefined@lemmy.hogru.ch 2 months ago
Yeah that’s a whole other can of worms. I see this a lot at work where people are asking for direct database credentials and cringe every time.
undefined@lemmy.hogru.ch 2 months ago
Lately I’ve been dealing with tons of invalid byte sequences in MySQL dumps and it makes me question what the hell they’re allowing in there.