Sounds like the drive might be bad 😟
Basic troubleshooting includes what you've already done (testing known-good media), plus testing other types of media. If you put in DVDs and those are read pristinely, then you might still have a bad drive. Try several CDs that you know for a fact work in other systems.
The drive might also be dirty, but if you're able to read DVDs OK, then that's not likely.
A cheaper alternative to replacing the drive (if the computer is not in warranty) is to get an external USB drive, which can be anywhere between $50 and $100.
Do you have another Mac in the house? Or even a PC with an optical drive? If so, you could use Remote Disc to share that computer's optical drive with your MacBook. Check out this article.