does this cable have to plug into a modular psu? or could it plug into a molex coming off the psu?

The quad SATA connector supply end accepts a standard 4 pin Molex from any computer PSU whether the PSU is modular or not. The SATA connector spacing permits a small amount of slack when SATA drives are mounted in a typical PC case ...

What is the length of this cable?

I can't get an exact measurement without taking these out of my case, but somewhere in the neighborhood of 8" at least. Oh, and just in case it matters for your application, this installs upside down relative to how you would probably expect it to. The last SATA power port would need to be the "bottom" drive if you are hooking up several in sequence due to the way the connectors are oriented.