Reminds me of a Gregory Bateson quote:

"If the hangover preceded the binge, drunkenness would be considered a virtue & not a vice."

Hm, a drug where the negative effects are first... Well, almost all medicines fit this category: unpleasant taste is common.

Illegal drugs that people want to take? Ayahuasca comes to mind:

Author Don Jose Campos claims that people may experience profound positive life changes subsequent to consuming ayahuasca.[5] Vomiting can follow ayahuasca ingestion; this purging is considered by many shamans and experienced users of ayahuasca to be an essential part of the experience, as it represents the release of negative energy and emotions built up over the course of one's life.[6] Others report purging in the form of nausea, diarrhea, and hot/cold flashes.