pin point eyes by iggy and the stooges.
I'm pretty sure the whole song was improvised
I also like cod'ine by Buffy Sainte-Marie (surprisingly about heroin rather than codeine). A few big 60s bands did some pretty good covers of it. In order of least to most favourite: Janis Joplin (I don't like her cover), Quicksilver Messenger Service, The Litter, Donovan
Cold Turkey by John Lennon, about withdrawal from heroin, something I'm to young to have gone through
Needle in the Hay by Elliott Smith. Not a great song but it's about heroin, so
Ball and Biscuit by The White Stripes, just guessing but I think by 'ball' they mean speedball
There are so many, though, I could never list them all. Opiates are pretty popular with musicians, believe it or not
