Local council in England to remove apostrophes from road signs because Computer Says No

boing
42

If apostrophes or other reserved characters are a problem, then the software was written incorrectly and insecurely, and the developers should be held liable to fix it.

What they are doing wrong is building a SQL query bit by bit, adding user data and fixed command language together into a single “string” that they submit to the SQL engine for interpretation and execution. This is 100% always wrong.

The way to deal with user data safely and securely is to use what is called “parametric SQL”. The developer needs to construct the SQL query statement using tokens to mark where the user data is to be inserted, and not inserting the user’s raw data. The call to SQL then supplies the query string, and a list of parameters containing the user data that the engine will safely insert into the database. The user data must never be interpreted by the SQL parser.

If they were using parameterized calls, they never would have noticed a problem with apostrophes, quotes, semicolons, or other SQL query reserved characters.

Nothing I wrote is new. It’s literally the first lesson taught in Secure Software Development 101; and SQL injection is the first attack taught in ethical hacking courses. It’s been known since the dawn of databases, yet SQL injection attacks are still the #1 vulnerability on the OWASP Top 10 list, and have been since the list was first compiled decades ago. That it’s still #1 is 100% the fault of inadequately trained developers, code reviewers, and organizations that don’t make security a priority. Developers have a responsibility to be better than this, as do the organizations that provide software.

I have no mercy for anyone who deploys crap code that allows for injection attacks. Not in this era of ransomware and cyber attacks. You can make mistakes while learning, either in school or on the job, but you better never deliver something so vulnerable.

EDIT:

I just saw CVE-2024-1597, in which the PostgresSQL JDBC driver, pgjdbc, will convert your nice, safe parameterized SQL statement into a vulnerable, injectable query if you use PreferQueryMode=SIMPLE. :person_facepalming: :sob:

10 Likes
43

It’s like this has been pulled straight out of a Douglas Adams novel, which makes a welcome change from feeling like i’m living on Scarfolk island. :face_exhaling:

10 Likes
44

Will Westward Ho! be losing its exclamation mark next?

Westward Ho - Wikipedia!

edit: wow, the exclamation mark really messed up the parsing of that link, huh?

3 Likes
45

Westward Ho!

Escape characters can be your friend

3 Likes
46

The eunuch? His name was Varys, no?

47

thats-the-joke-ranier-wolfcastle

2 Likes
48

Recently ran into this with a Tongan patient, whose name includes an apostrophe. The mom explained that in their language, the apostrophe is a letter and changes the sound of the vowel after it, but it was not allowed by immigation, so their name is not actually their name. So i asked her to teach me how to say it properly. It’s not much, but it’s what i can do. Other than go “What? You aren’t allowed your name?” Computers suck. Or, i guess the programming sucks, to be more accurate.

11 Likes
49

This is what happens when you let programmers determine your business needs. For a long time our patient administration system would not allow any surnames that had apostrophes or hyphens because the programmers decided it was easier to pretend that there were no surnames that had those characters.

8 Likes
50

Time to re-boing this old gem…

11 Likes
51

I’m not a geospatial standards wonk; willing to plunk down almost a thousand redcoat exchange units, or able to find a pirate copy of BS7666; but I’m puzzled by the fact that the AGI introduction to the standard specifically notes:

4.6 Object names
BS 7666 standardises only the structure and form of the gazetteers. It does not
standardise the content itself. Gazetteers are essentially records of geographic place
names. These place names are created and controlled elsewhere, sometimes by a
statutory body. Of particular importance to the Standard is street naming and
numbering. This is a statutory function carried out in local authorities. This process is
outside the remit of BS 7666. Many of the problems encountered in addressing relate
to street naming and (property) numbering, and need to be resolved in that context.
Complex gazetteer structures and records are not a sensible way of solving real-world
addressing problems. There is no substitute for rational street naming and property
numbering, and data standards cannot solve problems resulting from institutional
failure.

Which doesn’t sound like a “Thou shalt abstain even from ASCII codes 32-47; for some printable characters are of sin”.

I also looked up a vendor’s documentation of their allegedly-compliant data structures; and they (as would be expected from being based on ISO 19112) just note that text fields are unicode(and must include enough of it to support Welsh) and that there’s also a provision for providing language codes for entries; and for submitting multiple localizations where relevant.

I was not able to chase down the ISO standards-referring-to-standards web enough to find anything about the contents of text fields(lots of stuff about encoding and decoding rule test cases and generally making sure that everyone’s XML is lined up; but nothing about the expected contents of text fields, just their representation).

Anyone familiar with gory geospatial standards details able to say if it’s even the case that certain punctuation is discouraged or forbidden by the standard? And, if so, how the standard got bogged down in minutae of a specific natural language rather than just running screaming from that problem(less severe for the BS7666 case; literally all the trouble in the world for the ISO 19112) in order to focus on standardizing coordinate systems and deciding what elements should or must be provided to count as a proper entry?

I’m in no position to state that it definitely doesn’t require removing punctation, and it might well; it just seems very, very, odd for a standard intended for genera applicability(and based on ISO standards with ambitions for considerably wider applicablility than that) would make the mistake of being really brittle about delimeters or something that would prevent you from shoving basically whatever you want into text fields.

7 Likes
52

Australia doesn’t allow possesive place names so no apostrophe needed. That was decided in 1966. But we have been mangling the english language for longer than that.

https://www.stylemanual.gov.au/grammar-punctuation-and-conventions/names-and-terms/topographic-terms#dont_use_an_apostrophe_for_possessive_names

4 Likes
53

I’d forgotten about this. This might be very useful to determine some test cases around our upcoming new patient administration system.

4 Likes
54

I suggested Google Maps correct the place name to Queen’s Park, not Queens Park, they did eventually, but you may notice they still call the neighbourhood QUEENS PARK, erroneously.

IMG_5931
IMG_59311179×1218 338 KB

Similar but different, we have numbered streets here too, but if you add th or st after some numbers the streets are sometimes not found…locally…a GPS glitch.

Also sort of peripherally related…more British road signs in this book!

IMG_5932
IMG_59321179×1172 357 KB

1 Like
55

Contracting Saint to St doesn’t require a fullstop because the last letter is the same:

If the last letter of a contraction is the same as the last letter of the whole word, then don’t use a period.

4 Likes
56

… it’s really a letter in our language as well

a contraction without an apostrophe is misspelled

7 Likes
57

Should I mention Breton here, where “c’h” is a letter?

(Breton dictionary order is A B CH C’H D …)

8 Likes
58

… you’re making the localization engineers cry now

8 Likes
59

Seems simple enough to have a punctuation-insensitive search function. Google can do this just fine – search “St Marys” on Google Maps, and you’ll find all the “St Mary’s” near you.

2 Likes
60

That’s true, though I’d bet some emergency services have to use some klunky proprietary system.

1 Like
61

Openstreetmap seems to cope with apostrophes just fine. Check out your Queen’s park example on there. QUEEN'S PARK | OpenStreetMap
St Mary’s Walk too at Way: ‪St Mary's Walk‬ (‪23609308‬) | OpenStreetMap

4 Likes