You can't make something out of nothing, you have to have (or guess) some information in order to be able to enhance an image in any way. For example if you know the properties of the blurring function (and there is no image noise) then you can actually unblur a photo. However you rarely know the blur function and noise is always present so that severely limits what you can recover (Adobe recently demonstrated an unblur filter but their demo was with synthetic blur).
In short, CSI is almost pure fiction - the gains that are possible in real life are marginal, nothing like the 5x increase in resolution that is presented on TV.
Sanity check: if they could do all that, people wouldn't be paying tens of thousands for 40+ MP Hasselblad cameras, it would be cheaper to simply duplicate the software!
edit: I somehow didn't notice the original question mentioned super-resolution from video. Multiple image super-resolution is possible in reality but only up to the limitations of the sensor. It works by using a set of images with sub pixel shifts. This gives information of the values in between pixels allowing you to build up a higher resolution image. Super-resolution from video works because a moving subject creates the same sort of shifts, however the appearance of objects mustn't change that much between frames. The technique at best is only giving you the results of a higher resolution sensor, you can't overcome the limits of lens resolving power, which would be quite limited with CCTV spec lenses.
Here is an example of superresolution under good conditions:

image from photoacute.com
an improvement in resolution, yes, but still nowhere near CSI level performance.
To reference my comment above, the latest Hasselblads actually implement sensor shift super-resolution, under the name "multi-shot capture" so you still can't beat the medium format manufacturers at their own game using clever software...