I used Creatine in my 20s for about six or seven years. I had no known negative health effects from it. Creatine generally helps hold water in your muscles, which can help you gain "mass". In addition, there is some evidence that it helps prolong your ability to do work while lifting, thus allowing another rep or another set, which, of course, makes you stronger.
There is little to no evidence that Creatine itself actually makes you stronger directly or in any anabolic way.
While on Creatine, you're usually encouraged to drink more water. Generally, when I would take it, I'd put on about five lbs right away, mostly due to water weight retained in muscles.
I don't know much about the negative medical issues, but what I'll say is I generally found creatine powder unpleasant to take, felt like I was bloated quite a bit (from water retention), and found the expense not worth the perceived benefits.
There are other studies out there that show mild gains in endurance from taking Creatine, but for most endurance athletes the water retention weight gain is off-putting, so it's not widely used in my athletic endeavors anymore.
In my experience learning about it and supplementing with it, I doubt there would be any benefit from taking it. As others have mentioned, the supplement industry is a huge, little-regulated marketplace.
For another comment: creatine does come in pill form, but it's generally consumed either directly as a powder or as part of a powder-sugar mix, the theory being the insulin spike caused by the sugar allows your body to use more of the creatine itself. Then you can get into the $80 and $100 creatine powders mixed with HMB...
Ten years after I took my last creatine supplement, I was able to put on as much muscle mass and was stronger deadlifting and squatting by doing this neat-o thing called "hard work" via CrossFit (which can also be hard on your kidneys, by the way!).
It's a money grab, IMO.
The no-sex thing is an old wives tale too. ;-)
"So we beat to our own drummer in the sun;
We ask for nobody's permission to run.
I just wanna live in a world like that;
Now I'm gonna live in a world like that!" - World Like That, O.A.R.