tl;dr: For $7, why not? It's secure, and versatile enough for most any job the iPhone 4 can handle (plus, it works with other smartphones too!).
After seeing this little gem in a video on Vimeo and another on YouTube (I think I was first introduced in a MacRumors.com forum thread), I decided it was worth the $14 price to buy two—one for home, and one for work. I already have a hacked-together $10 iPhone 4 tripod stand I built with an old iPhone 3G case and some hardware store parts, but this stand looks a lot more elegant!
Using this Tripod stand and mount, I can trim down my portable HD recording studio (using the iPhone 4 as the backbone) even more... Be sure to check out my article about getting external microphones working with the iPhone 4, iPad, etc.
I ordered my tripod + adapter from eBay, but you can get the same thing for $7 from Amazon: Mini Adjustable Tripod+Camera Holder for iPhone/other smartphones.
The Mini-Tripod Stand
The little mini-tripods that come with the tripod adapter are what you'd expect—relatively lightweight and flimsy, but surprisingly sturdy if you keep the iPhone/smartphone upright (vertical or horizontal). It's really only if you're trying to aim the camera downward or upward that the tripod that you have to worry about stability.
The mini tripod is about the same in build quality and fit-and-finish as the $10 Targus mini tripod I bought at Target some time ago. It's just okay.
The Smartphone-to-Tripod Adapter
Yes, you can detach the adapter from the tripod (and use it with any tripod/mount).
The spring-loaded clip that holds the iPhone (or, as it's adjustable, any other smartphone) to the base of the tripod adapter is very strong—I feel it would be okay even if I were to mount the iPhone to a car tripod mount, or upside-down. There are some foam pads on all the parts that touch the iPhone to prevent scratches. I would even dare to say the clip feels a little too tight... but it will probably get a little looser over time.
The other end of the adapter is also adjustable; it goes left and right to accommodate taller or shorter smartphones. I didn't really need to adjust it, though.
The female 1/4 x 20 tripod screw (where the tripod actually screws into the adapter) is plastic, so be sure to not overtighten the tripod screw! It feels secure enough for my purposes, though, as the iPhone is pretty lightweight, and won't cause as much stress on the screw as, say, my D90 with a vertical battery grip and a 70-200mm VR lens (5+ lbs!).
Although I'm a huge DIY fan, I think this adapter is $7 well spent. Pick up two or three so you can have a spare, or have one everywhere you go. Tripods are always—always—nice to have, regardless of what you're shooting.