Hacker News Reader

Comment by dhosek | Hacker News Reader

Nah, because it's not unsanitized SQL at fault, but people writing a literal NULL in the license plate field when there isn't one.

View on HN

I doubt that. Normal people do not tend to use the word NULL at all.

What this usually is is the result of systems that talk to systems that talk to systems that talk to systems, all in different legacy formats never written to be interchange formats. One system has true SQL NULLs, the next system down the chain only accepts strings for that field, NULL gets written as the most sensible string, and then from that point on all downstream systems can't tell the difference between the original system having had an SQL NULL or having had the string NULL.

Maybe NULL is the default value if you don't enter anything

Did you read the article? It literally says people at a private citation outfit put NULL in the license plate field when there is no number available.

And I still expect my story is more accurate, with theirs being a reasonable expectation of what you'd get when some techie tells their manager what happened, who tells their manager, who tells the reporter.

To be clear, I'm not denying that what you say is literally true, just that by the time I'm done filtering that particular fact through my personal belief network and personal experiences, I still end up saying that my story is more likely. It's true enough that they put a "NULL" in, it's just that the way the private firm does that is most likely that the field agents leave it blank, some software somewhere puts a NULL in some database, and the report that comes out for the enforcing authority has NULL in it. For a reporter, it's not a false statement, it's just not all the technical details.

With this story, the responsibility ends up distributed in a very plausible manner I've seen many times over; HN readers could fill in a dozens of similar stories no problem. It's a problem characteristic of these sorts of systems and the way they tend to communicate with each other.

People use the word NULL and in all caps as well, in particular in bureaucratic processes like those you would encounter at the DMV.

NULL & VOID, etc.

It is entirely reasonable that the system would not accept an empty string for the plate so the process folks worked around that by instructing all employees to write NULL if they couldn't read the plate.

Many people who are not programmers per se come into contact with databases that use SQL enough that they might absorb a few random concepts or names for things.

So, some bureaucrat might in fact know "NULL" because they type a command into a database every Tuesday to run a report.

As per HN guidelines, please don't ask or insinuate that someone's not read the article, instead just quote the relevant part.

Actually, I think it's doubtful the folks at the private processing facility are actually writing 'NULL', but my guess is the DB field is just not set (i.e. left as NULL), and then when the info is read out somewhere it's just printed as the string literal value.

I'd guess that there is a CSV step involved.

Wouldn't their license plate be in quotes, though? 'NULL' != NULL

As my other comment points out, this is probably systems talking to systems talking to systems talking to systems to the nth degree. So even if the first system did in fact distinguish the NULL case from the string case, it only takes one system in the chain to be incapable of representing the difference to permanently and unrecoverably wreck it for all downstream systems.

What are the odds at least one system silently filters out apostrophes as invalid characters in license plate fields? Pretty good. These systems are often unattended, unmonitored processes often maintained by people who either can't fix errors upstream, or don't even want to, so these conversions are often written extremely permissively, trying to get through the data with whatever heuristics are necessary for the process to just Keep Working.

That makes perfect sense, and seems like those most likely explanation. Thanks.

A colleague’s name is “True.” When we ran some reports to generate a check in list for an event - it was converted to either “TRUE” or “1” depending on the script.

I was amused.

Even without sql doing odd things certain strings will just cause problems.

I wonder, what will "DROP *;" license plate do?

Nah, because it's not unsanitized SQL at fault, but people writing a literal NULL in the license plate field when there isn't one.

Should be

    "; DROP *;

Lic. plates are entered into thousands of systems, so yeah it can have an effect somewhere.

Nothing because it's not unsanitized SQL at fault.

Probably can’t do special characters, but maybe ASCII (0x23) will let you get around it.

0x3b so your plate is just ";" in some systems, maybe?

Most states do not allow special characters in the plate text.

Depends on how you define "special." I've seen hearts on California tags, and I think some glyphs on Virginia tags, but I might not be remembering that correctly.

That's just part of the design. They are ignored when you type it in, etc. and you can't have ABC<heart>123 and ABC123 simultaneously existing because they're the same to the DMV.

Reply on news.ycombinator.com