I've used a lot of CPAN modules in production code. CPAN is probably the primary reason I use perl for most of my work since it enables me to build quality apps quickly. Not using CPAN is... foolish :-)

The important thing is to have some sort of process. My general rules when using other peoples modules are:

• Look for a test suite. If there isn't one be wary. Strongly consider writing one before you start using it :-)
• Review the code.
• Consider wrapping all the external code in a proxy so you can swap it out easily if necessary.
• Make sure your integration tests exercise all the modules, not just the ones you wrote.
• Keep an eye on CPAN for updates. Other people fix bugs (isn't it great :-)
• Do not blindly update to the latest version of a module when it hits CPAN. Read the changelog. Run a diff against the one used on the production machine. Run regression tests.

Even if you do all of the above it will still take less time than writing something like DBI or Template from scratch.

I've only very occasionally had problems. Some of the non-upward compatable changes made to Class::DBI being the only ones that ever caused serious hassles - and that was because somebody else ignored the last point above :-/

Hope this helps.