I have one (Weapon R Short Ram Intake) and it's been great. The car sounds cooler and has a little more acceleration. I don't have any problems with water as long as I don't drive into any huge puddles. I would stick with the short ram though, unless this is a weekend car. The cold air intakes pull air from under the car, which is exactly where puddles are. You don't want water in your engine.
There's one ECU issue though.If you cold-start your car up and start driving b4 it warms up, you might get a code for your 02 Sensors. Just clear that code or keep driving and you'll be fine.