Sanitized for your protection
Protecting against this type of attack isn't simply a case of "sanitizing" the single-quotes, as this excludes valid names such as "Brer O'Hare", in which a quote is a perfectly valid character.
Depending on which language/platform/database you're using, there are plenty of libraries whose creators have thought through all the possible combinations of "problem characters". You just have to make sure you use one — and, of course, make sure you have an integration test that confirms this protection is working.
In fact, an often-neglected aspect of security awareness is integration testing — that is, ensuring that the disparate parts of the system fit together without any glaring (or subtle) security holes.
The trouble is, testing a system for security weaknesses after it's developed is like building a warship and only then thinking about water-level hatches where enemy frogmen might potentially be able to sneak in. Security testing really needs to be incorporated into the development process, not just as an "after-the-event" phase; the mantra "test early" is espoused in The Art of Software Testing by Glenford Myers, for example.
But herein lies another problem: you'd think an "utterly" test-driven process would help improve security — yet with the advent of Test Driven Development (TDD), programmer-led testing has become a case of "does this minute software function work while I feed it simulated inputs and fence off external calls using mock objects?" rather than "does this system work correctly when the pieces are joined together?"
There's a difference between zooming in on individual components and testing those, or kick-starting the whole Rube Goldbergesque end-to-end interaction and confirming that the results displayed on the user's screen are as expected. Integration testing across system boundaries such as firewalls or third-party components is a good way to reveal security flaws.
Developers should also be keeping an eye on the SANS top cyber security risks page, and OWASP, and XSSed, and thinking about automated tests they can write — which can usually be shared among projects or components — to verify that the system isn't vulnerable to these kinds of attacks.
Otherwise, high-profile attacks and exploits won't just not die, they'll pop up with increasing regularity. ®
Don't forget user permissions
Will the website ever need to do a table drop? Probably not - so why give the user it's accessing the DB through permission to do so? This kind of thing is the next step down from just running everything as sa.
Don't string build - this includes DB-side via something like executesql. There's nearly no excuse. Parameterised SQL or stored procs will do the job just as well, and you get an awful lot of the sanitisation thrown in for free.
XSS is flipping hard to deal with when working on legacy code! Add a "Does this look like it's OK" function and call it before you do any other processing. There will always be more gaps if you instead try for a "Does this look dodgy" function.
Alternatively, institute a "no Irish, no yahoos with multi-barrelled surnames" rule on your site. There can't be too many of them ;)
A couple of suggestions
1) Don't split the development by tier - get developers who can implement every part of the stack and understand the interactions between each component. Split by groups of web pages (or forms, or whatever) with related functionality. Developers can write the page, the calls to underlying logic layers and calls to the database from there.
2) Never construct ad-hoc SQL statements. Use parameterised stored procedures, and use the provided underlying object model to build the calls (rather than a string such as "EXEC MySP '%1', '%2', '%3'").
3) In my mind, unit tests are good for regression testing - proving a bug fix or new feature does not modify existing, expected behaviour. But I've seen developers implement things, or perform a bug fix and declare it works because the unit tests pass. There is no substitute for proper end to end testing in the first case by the implementor, and then more thoroughly by a QA team (if you have that luxury).
4) XSS - if you are spitting back to the browser anything which has been entered by the user via a post, or taken as a query parameter (or any other source of external data for that matter), always pass it through a HTML encoder so it will not be executable.
That's what initially came to mind reading the article. Look forward to recommendations from others with more/different experience.
We shouldn't be constructing DB queries by concatenating parameter strings onto constant strings containing SQL statements. The SQL query should be preprepared with parameter placeholders and then have the variables containing the data from the user bound to it. This not only does away with the risk of the sanitization not being thorough enough but is also generally quicker.