The general technique you're asking about is called motion tracking, of which there exist different types: Feature ("point") tracking, planar tracking, and 3D match moving. After Effects (and the bundled Mocha) is probably the best entry level software to each of these methods, but it definitely leaves room for professional growth. Apple's Motion includes a more basic toolset, is only suited to feature tracking, but is more beginner-friendly.
When text is flatly attached to a simple, clearly visible surface such as a picture frame, you can use Feature tracking to track the corners of the object, and "corner pin" an image or text into the frame. The software will do its best to automate the process of tracking each corner, but if, for instance, an object passes in front of one of the points, you may have to manually keyframe its movement.
Planar tracking goes a step further and tracks the movement of full flat surfaces. Mocha, bundled with After Effects, is a planar tracker. It's fast and flexible, and you can achieve better results more reliably, but it's a little more complicated. To mask out an obstruction, for instance, you must rotoscope out the offending object.
You would only use 3D matchmoving if you wanted your text to be three dimensional, cast realistic shadows, receive image-based lighting, reflections, transparencies, in other words, be a realistic object in your scene. This is the most complicated method because it requires a back and forth workflow through tracking, 3d modeling, and compositing software. Different combinations of software packages will lump one or more of these steps within the same application.
An example of a dedicated professional motion tracking application is Boujou, although there are others (PFtrack comes to mind).
Hope this gives you a place to start.