The Church has been opposed to drugs at least since Saints Paul and John.

The Greek word φαρμακεία (pharmakeia) can mean "drugs" or even "abortifacients," so Scripture certainly prohibits drugs, which includes those producing chemical abortions and potentially abortifacient contraceptives. In the following verses, the Rheims-Challoner translation translates φαρμακεία as "witchcrafts" or "sorceries." St. Jerome's Latin Vulgate uses "veneficium."

E.g.:

Ga 5:19-21:

Now the works of the flesh are manifest, which are fornication, uncleanness, immodesty, luxury, idolatry, witchcrafts {φαρμακία}, enmities, contentions, emulations, wraths, quarrels, dissensions, sects, Envies, murders, drunkenness, revellings, and such like. Of the which I foretell you, as I have foretold to you, that they who do such things shall not obtain the kingdom of God.

Rv 9:21:

Neither did they penance from their murders, nor from their sorceries {φαρμάκων} ^{[drug usage / dealings]}, nor from their fornication, nor from their thefts.

Rv 21:8

But the fearful, and unbelieving, and the abominable, and murderers, and who*emongers, and sorcerers {φαρμακοῖς} ^{[drug-dealers]}, and idolaters, and all liars, they shall have their portion in the pool burning with fire and brimstone, which is the second death.

Rv 22:15

Without are dogs, and sorcerers {φαρμακοὶ} ^{[drug-dealers]}, and unchaste, and murderers, and servers of idols, and every one that loveth and maketh a lie.

So I think this answer needs to dichotomize to be useful.

Mood altering drugs

Drugs that alter the mood, could be classed like alcohol, as something to be taken temperately, if it is possible to take them temperately. Smoking cigarettes can be done in moderation. It is quite possible that smoking marijuana cannot.

Wine was created from the beginning to make men joyful not to make them drunk

Sir 31:35

Downers

There is no prohibition against self-medication within the Catholic Church. But there is a strict prohibition against complete inebriation (here's where I conflate being stoned with being drunk, YMMV). There has been since the time of Noah.

The use of depressants to treat mental anguish might be licit. It might also be contrary to what St. John Paul II called "Salvific Suffering". See: Salvifici Doloris Which is wrapped up in the reason Jesus rejected the drugged wine offered to Him on the cross.

Self-medication may inhibit someone else's ability to be a "Good Samaritan" for us, as St. John Paul II.

Psychedelics

Drugs that produce a near instant sense of euphoria; or that cause inebriation quicker than can be said is temperate are always contrary to virtues of Temperance. Sloth and Gluttony are their fruits and they're to be avoided because of it. Any good effects that come of doing evil are also to be avoided.

A good intention ... does not make behavior that is intrinsically disordered, such as lying and calumny, good or just. The end does not justify the means. CCC 1743

Use of ecstasy or mushrooms to treat depression might be an exception. But that would be a circumstance when ones present state is improved. (The sick, need the physician, anything else is self-mutilation - which is also to be avoided)

Uppers

Drugs that give "mental clarity" should also be avoided in places where such usage could be considered underhanded cheating. They're brain stimulants that are as dishonest to use as human growth hormone is to a cyclist. The other reason to avoid them is that they often have the worst side effects and are the most addictive. Nicotine, Caffeine are ways to use uppers moderately, but even they can be abused.

Drugs to produce a desired effect

Some drugs do not alter mood, but do something else to the body:

Abortion

Drugs to procure abortion would be illicit by virtue of them causing the death of the child in the womb. CCC 2322

Contraception

Any contraception is contrary to the idea of marriage, contraceptive drugs would be too CCC 2370

Suicide

Taking medication meant to induce death is always wrong, according to the Catholic Church 2280 It can even be scandalous.

Sleep

Not inherently evil, but if you abuse sleep aids, you might fall into the sin of Sloth.

Steroids

Taking medication to increase muscle mass is not inherently evil, unless it is for self-mutilation (malice), self-aggrandizement (pride) or to have an unfair advantage over competition.
Since these are the only non-medical reasons a person would have (other than complete flippancy, which is probably sinful too) it would seem that steroids are likewise verboten.

Analgesics

The FDA has a pretty clear system for determining which drugs are OK for over the counter use, which are OK by prescription and which are just not OK to have around (i.e. schedule 1 drugs). Now, sometimes politics play a role in determining what drugs go where, but our civil society. But Sirach, at least, makes a pretty good case for listening to doctors.

Honor the physician for the need thou hast of him: for the most High created him ... The most High hath created medicines out of the earth, and a wise man will not abhor them Was not bitter water made sweet with wood? The virtue of these things is come to the knowledge of men, and the most High hath given knowledge to men, that he may be honored in his wonders By these he shall cure and shall allay their pains, and of these the apothecary shall make sweet confections, and shlal make up ointments of health, and of his works there shall be no end.

Sir 38:1,4-7

In summary, between the times of Sirach and St. Pope John Paul II, the thing that has become the Chuch has consistently erred on the side of the Natural Moral Law when it comes to drugs. First you consider what a thing is, then you ask what is it for. If a thing is bad, then using it is bad. If the thing that you use it for is bad, then using it is bad.