All creatures are good because God created them (Gen. 1:31). It's their immoderate usage which can be evil. Holy Scripture distinguishes alcoholic drinks, medicines, and harmful drugs.

Alcoholic Drinks

The drinking of alcohol, in moderation, is mentioned positively in:

Proverbs 31:6-7
6 give strong drink to them that be sad, and wine unto them, that are of a pensive mind:
7 let them drink, and forget their poverty, and not remember their sorrow any more.

The Wedding Feast at Cana (John 2:1-11), where Jesus performs His first miracle of turning water into wine, also shows that wine is good.

The immoderate usage of alcohol (drunkenness) is condemned frequently in Holy Scripture, e.g. in the verses quoted below.

Medicines

Medicines should not be despised; they are a good thing, as discussed in:

Ecclesiasticus (Sirach) 38:2,4
2 …all medicine is of God…
4 The Highest hath created medicines of the earth, and a wise man will not abhor them.

Harmful Drugs

The Greek word φαρμακεία (pharmakeia) can mean

1. drug,
2. purgative,
3. emetic (substance that induces vomiting),
4. abortifacient (substance that causes an abortion),
5. potion,
6. spell,
7. poison,
8. witchcraft;

so Scripture certainly prohibits the usage of harmful drugs like those that induce abortion, potentially abortifacient contraceptives, and other deadly substances. In the following verses, the Rheims 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."

^{(Notice, this verse also condemns immoderate use of alcohol by mentioning drunkenness.)}

Rv 9:21: "Neither did they penance from their murders, nor from their sorceries {φαρμάκων},* nor from their fornication, nor from their thefts."

^{*The New American Bible (NAB) (and NABRE) translates φαρμάκων here as "magic potions."
(source: Matt Gutting's comment below)}

Rv 21:8: "But the fearful, and unbelieving, and the abominable, and murderers, and who*emongers, and sorcerers {φαρμακοῖς}, 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 {φαρμακοὶ}, and unchaste, and murderers, and servers of idols, and every one that loveth and maketh a lie."

I will attempt to answer this question from a natural-law perspective (to complement Geremia’s excellent Scriptural analysis).

The Church’s current teaching on drugs

As the O.P. points out, the Catechism of the Catholic Church [CCC] states the following in number 2291 (which I repeat for the reader’s convenience):

The use of drugs inflicts very grave damage on human health and life. Their use, except on strictly therapeutic grounds, is a grave offense.

Drug dosage and potency

So, what is the problem with the non-therapeutic use of drugs? The fact that they cause “very grave damage on human health and life.” Said in other terms, drugs (when used for recreation) are essentially poisons that happen to have pleasurable side-effects, which can provoke psychological or (in some cases) physical addictions.

In medicine, there is a maxim that states “the dose makes the poison.” The principle is simple: any chemical substance—even water, sugar, or oxygen—is toxic when taken in an excessive quantity. (For instance, water poisoning requires a lot of water, but it is possible.)

The problem with the most problematic drugs—cocaine, heroin, methamphetamine, and so forth—is that it takes a very small amount to produce a catastrophic effect. It is easy to overdose, and even with a “normal” dose, the person becomes fully intoxicated: that is, he loses control of his actions. Moreover, these drugs are susceptible to various kinds of addiction (physical and psychological), as well as severe long-term health effects. Because these drugs are so potent, there is generally no way to produce a mild dose, except in a controlled medical environment. For this reason, such substances should only be used for therapeutic, never recreational, purposes. (And as far as I know, of these, only the narcotics—morphine and its relatives, including heroin or “diamorphine”—are currently medically useful. It is also sill possible to use cocaine as a topical anaesthetic and vasoconstrictor, which is very different, obviously, from its internal use.)

Other drugs are less potent and therefore somewhat subject to debate; for example, marijuana (cannabis). (The Church does not have an official teaching on particular drugs, and so it is up to moral theologians to discuss these issues, in the light of the findings of medical science; this debate is ongoing as we speak. It seems to me that it is an exceptionally bad idea to smoke marijuana, either recreationally or as a therapy, since it is certainly a good deal more potent than either tobacco or alcohol. Using the active ingredient, THC, as a medicine for pain relief or other purposes, could be legitimate, much as morphine is.) In any event, it is clearly immoral to use marijuana to the point of intoxication. (See below.)

On the other hand, alcohol poses no particular health risks when it is used in moderation.* Evidently, its abuse leads to intoxication in the short term, and even if it does not, excessive drinking leads to long-term health problems.

The reason that alcohol is so much less problematic is that it that it is much easier to “dose” than other substances: keep your drinking to one or two per day, and you will never have a problem.

The fundamental pitfall to avoid: intoxication

The problem common to all substance abuse is intoxication, the condition in which the person loses control over his actions; drunkenness is one example. However, practically all of the drugs mentioned in this answer produce an intoxicating effect, which may vary in its particular manifestations (e.g., some drugs are stimulants, whereas others are depressants), but all of them are capable of acutely and severely impairing judgment and self-control.

This condition places both the intoxicated person and others in grave danger of life and health. A drunk person can easily do or say things that he would never do if he were sober—even to the point of violence. The particular kind of intoxication produced by a given drug may vary, but the very fact of losing self-control poses a grave danger to self and others.

For that reason, deliberately becoming intoxicated, by any means, is always gravely and intrinsically immoral.

The so-called “hard” drugs have the property that, practically speaking, even the minimum dose produces intoxication. Therefore, these are never to be taken recreationally or outside of a controlled medical environment. (E.g., properly packaged oxycodone will be correctly dosed if used according to the doctor’s instructions; heroin bought on the street will not.)

The so-called “soft” drugs are somewhat less potent, and hence the strict immorality of taking them is open to debate. (I tend to think they are more potent than people appreciate, hence I still think it is an exceptionally bad idea to take them, even once.)

On the other hand, alcohol is clearly safe to take in moderation, since it does not even produce intoxication when taken in small amounts. In fact, the proper enjoyment of alcoholic beverages does not entail intoxication at all.

(One difference, for instance, between marijuana and alcohol, is that plenty of people take alcohol for the taste; no one takes marijuana for the taste, but principally for its systemic effects.)

Short-term and long-term effects

The secondary consideration is the short-term and long-term health effects.

Some drugs (e.g., cocaine) have the potential to cause catastrophic health effects even with one use; and others (e.g., heroin) cause extremely debilitating physical addiction. Even apart from the intoxication, that would be reason alone for making it immoral to take these drugs recreationally.

They also cause terrible long-term effects.

With other drugs (e.g. marijuana), these health effects are debatable. Nevertheless, the problem of intoxication remains.

However, with alcohol, its prudent and moderate use poses practically no health risks, as mentioned. (Naturally, its excessive use poses plenty of risks.)

Summary

The problem with drugs (especially “hard” drugs, but to some extent all recreational drugs) is fundamentally that their principal effect is intoxication, which, when deliberately provoked, is gravely and intrinsically immoral.

With some drugs, there is the added danger of severe and lasting dangers to health and life (apart from the intoxication itself).

Some drugs (e.g., the “hard” drugs), are so potent that it is practically impossible to take them and avoid intoxication. Others (e.g., the “soft” drugs) are less potent, but still easily produce intoxication.

For alcohol, on the other hand, its principal purpose (when used properly) is not intoxication. Its systemic effects (when used in moderation) are very mild—in fact, are hardly perceptible—and pose no particular health problems, nor—crucially—do they produce a loss of self-control. Therefore, whereas drinking in order to get drunk is clearly immoral (especially when followed by a dangerous activity, such as driving), drinking in moderation is not morally problematic at all.

The Catechism sums this teaching up follows:

The virtue of temperance disposes us to avoid every kind of excess: the abuse of food, alcohol, tobacco, or medicine. Those incur grave guilt who, by drunkenness or a love of speed, endanger their own and others' safety on the road, at sea, or in the air (no. 2290).

* Health agencies other than the CDC, such as Chief Medical Officer of the United Kingdom, have made the claim that there is no safe level of alcohol consumption. However, if one consumes alcohol at the rate recommended by the CDC (a maximum of two drinks per day for men, and one drink per day for women) or less, then the long-term health risks are vanishingly small. In any case, a small health risk does not make an action immoral; skiing, for example, poses a risk of significant injury or even death, and yet that risk is still relatively small. Only a grave risk to health makes an action immoral.