I think ArthurQ has the right idea for an Opiate. Mucks up your head, but you become pain-tolerant, granting a Con bonus. "Speedy" drugs, like amphetamines and including MDMA, don't fog the mind. Ritalin (you know, the ADD drug) is an amphetamine, focusing the user for complex tasks. In fact, amphetamines are known as a "good" study and test taking tool amongst the post-secondary crowd.
The addiction rules in the BoVD are deep enough for casual interest in addition of drugs to a game, IMHO. My players are aware of the availability of performance enhancing substances, but the potential for addicition makes them wary of use. Especially since I don't lay them out in statblocks for them to optimize use. They just don't know for certain how addicitive (and what the save to resist) is going to be before hand. Adds some mystery.
A bit of research at Erowid (a fine and relatively impartial source of information about all varieties of alternate experience vectors) should get you pointed on the right path for any real drug you wish to add to your fantasy world.