Basically, certain characters (such as ' " ; ) are used as delimiters in programming and database languages. If not very carefully dealt with (in a process called "escaping" or "sanitizing"), they can potentially cause all sorts of problems.
There's no need to deal "carefully" with these in the normal course of events - a compiler will catch issues with delimiters at compile time.
The main issue occurs when you use one language (e.g. C++ / C# / Java) to interact with another language (SQL) where the SQL delimiter has no special meaning in your application language. Your C++ / C# / Java compiler isn't going to complain about issues with your SQL delimiters.
HOWEVER that all said, just about every major programming language provides data access APIs (ODBC, OLEDB etc) that provide support for parametised queries / prepared statements. In this case there is no need to escape anything, or do anything special at all. Instead this is all handled internally for you by the provider of the data access API (whether that be Microsoft or Sun or IBM or Oracle or whatever). Generally these APIs are extermely robust, and for the application programmer there's nothing special you need to do, no matter what other data source you connect to.
For any application written in the last few years, there's no excuse for being vulnerable to SQL injection attacks. For older applications, where the knowledge of the issue wasn't so widespread, there's definately code that doesn't take advantage of best practises, and updating/refactoring this obviously has a cost.
But that all said, these days, no extra effort is required. All the stuff necessary to protect your application is already there, supplied by the major vendors.
