With respect, that doesn't make sense in the context of what I'm trying to do. If I had a bad DBI object, I'd already know that from my connect() code.

1. connect
2. prepare
3. execute

Your proposal is that I test number 2 by failing number 1. I'm testing all of them.

It does make sense indeed! As Anonymous Monk has suggested, you are trying to mock DBI's objects interface: for each test case you'd like to develop you mock the relative object methods. Look at Test::MockObject for fine grained object mocking.

Let me try to reiterate. The "mock" testing strategy would mean preparing three different mock objects, one failing each particular call you want to handle failure of and succeeding others. First you would set connect() to fail, run your code and check whether the error handling subroutines are called. Then you would restore normal behaviour of connect() (see also: DBD::Mock to allow testing database error handling without a live database) but set prepare to fail and run your code again. Lather, rinse, repeat until all error conditions you want to be able to handle are tested.

I think there was a fundamental misunderstanding here, possibly caused by my wording.

Your answers are perfectly good answers to "how should I create a test so that I can see what happens if a particular function fails?", which is one interpretation of my question.

However my question is really "how can I make real-life code fail at the prepare() stage if the SQL code is bad?"

kschwab's answer here is the answer to the question I was trying to ask.