As if database were not able to del with apostrophes or other special characters...
Yes you have to sanitize your queries, but you have to do it anyway.
Client applications will of course have to be smarter
It's like the stories of people with the last name "Null" who get errors when trying to enter their name into websites. If that's true then I don't want to think about how poorly built (and insecure) those systems must be.
Lots of countries limit what you can name yourself. I very much doubt having a special apostrophe in your name can be a legal requirement.
Either way, apostrophes in names is quite uncommon in many parts of the world, and they are very likely to be ignored even on paper forms in those areas, if they are even allowed.
In general, each area has certain limits on what kinds of names if allows/understands, and it is up to the minority to adapt one way or another. It's very reasonable to want an Irish or maybe even British system to recognize a name like O'Reilly, but it's not really something you can expect of a Japanese system. Just as much as you shouldn't expect a name like 田中 to be recognized in France.
This results in amusing side-effects, like buying a house requiring you to sign every variation of your name that the credit check found, but it can also get you multiple “one per person” signup options, so there’s that, too.
H2 offers quite a comprehensive solution for dealing with this:
> [H2] provides a way to enforce usage of parameters when passing user input to the database. This is done by disabling embedded literals in SQL statements. To do this, execute the statement:
> SET ALLOW_LITERALS NONE;
> Literals can only be enabled or disabled by an administrator
I would argue if you sanitize your input you are already doing it wrong, you should parameterize queries and send the data entirely separately from code.
If it sanitizes anything, parameterization sanitizes the code, not the data, and has much lower impact on the outside world (because the rest of the world isn't pressured to rename things in the real world to fit arbitrary constraints in the computer).
