What does the Bible say about drugs?

Cannabis might be referred to as "keneh bosem" or "fragrant cane".

Take also for yourself the finest of spices: of flowing myrrh five hundred shekels, and of fragrant cinnamon half as much, two hundred and fifty, and of fragrant cane two hundred and fifty, (NASB95)

Take choice spices: twelve and a half pounds of free-flowing myrrh, half that - about six and a quarter pounds - of sweet-smelling cinnamon, six and a quarter pounds of sweet-smelling cane, (NET)

— Exodus 30:23

The "fragrant cane" and "sweet-smelling cane" here are translations of the Hebrew וּקְנֵה־בֹשֶׂם (qānê beśem). It's possible that the word "cannabis" derives from "qānê-beśem".

See answer in Who says that keneh bosem is cannabis? - Mi Yodeya.

Two limestone monoliths, interpreted as altars, were found in the Judahite shrine at Tel Arad. Unidentified dark material preserved on their upper surfaces was submitted for organic residue analysis at two unrelated laboratories that used similar established extraction methods. On the smaller altar, residues of cannabinoids such as Δ9-teterahydrocannabinol (THC), cannabidiol (CBD) and cannabinol (CBN) were detected, along with an assortment of terpenes and terpenoids, suggesting that cannabis inflorescences had been burnt on it. Organic residues attributed to animal dung were also found, suggesting that the cannabis resin had been mixed with dung to enable mild heating. The larger altar contained an assemblage of indicative triterpenes such as boswellic acid and norursatriene, which derives from frankincense. The additional presence of animal fat―in related compounds such as testosterone, androstene and cholesterol―suggests that resin was mixed with it to facilitate evaporation. These well-preserved residues shed new light on the use of 8th century Arad altars and on incense offerings in Judah during the Iron Age.
— Cannabis and Frankincense at the Judahite Shrine of Arad: Tel Aviv: Vol 47, No 1

The new research applied modern chemical analysis techniques to the residues on each of the two altars. The smaller of the two altars was found to have a mixture of animal dung and cannabis that contained sufficient THC (the psychoactive compound in cannabis) to get those breathing in its fumes high, per Science News.

The residue on the larger altar was composed of animal fats and frankincense, the dried sap of trees in the Boswellia genus, according to the paper. The researchers write that the dung and animal fats were used to burn the cannabis and frankincense at temperatures that would release their respective mind-altering and fragrant smoke.
— Archaeologists Identify Traces of Burnt Cannabis in Ancient Jewish Shrine — Smithsonian Magazine.

As for heroin, which is made from opium poppies, some claim that "flower of the field" refers to poppies:

As for man, his days are as grass: as a flower of the field, so he flourisheth.
— Psalm 103:15

The voice said, Cry. And he said, What shall I cry? All flesh is grass, and all the goodliness thereof is as the flower of the field:
— Isaiah 40:6

Poppy flowers tend to bloom for only a day or two, so this makes a good comparison to how short human life is.

There is little evidence that the expression actually does refer to poppies though.
See the answers to Does "flower of the field" mean "poppy"? - Biblical Hermeneutics.SE.

But the real answer is to consider what the Bible says about alcohol.

It certainly permits its use.

For celebrating the week-long Feast of Tabernacles, people are expected to drink and enjoy themselves to celebrate the autumn harvest:

And you shall spend that money for whatever your heart desires: for oxen or sheep, for wine or similar drink, for whatever your heart desires; you shall eat there before the LORD your God, and you shall rejoice, you and your household.
— Deuteronomy 14:26

… Every man at the beginning sets out the good wine, and when the guests have well drunk, then the inferior. You have kept the good wine until now!
— John 2:10 (referring to wine that was miraculously provided by Jesus)

No longer drink only water, but use a little wine for your stomach’s sake and your frequent infirmities.
— 1 Timothy 5:23

But, it also condemns excessive drinking, to the point of drunkenness.

For the drunkard and the glutton will come to poverty, And drowsiness will clothe a man with rags.
— Proverbs 23:21

Blessed are you, O land, when your king is the son of nobles, And your princes feast at the proper time — For strength and not for drunkenness!
— Ecclesiastes 10:17

And do not be drunk with wine, in which is dissipation; but be filled with the Spirit,
— Ephesians 5:18

Do you not know that the unrighteous will not inherit the kingdom of God? Do not be deceived. Neither fornicators, nor idolaters, nor adulterers, nor h*m*sexuals, nor sodomites, nor thieves, nor covetous, nor drunkards, nor revilers, nor extortioners will inherit the kingdom of God.
— 1 Corinthians 6:10

The obvious principle here is that wine serves a good purpose, and it is only when drinking to the point of intoxication that alcohol becomes a problem.

But, in the case of opiates, cannabis, etc. the only reason for using it is for its intoxicating effects. People enjoy wine or beer with a meal, but no one takes methadone (heroin without the "good" effects) or smokes drug-free hemp.

I would suggest clear guidance is found in Romans 13.

¹ Let everyone be subject to the governing authorities, for there is no authority except that which God has established. The authorities that exist have been established by God. ² Consequently, whoever rebels against the authority is rebelling against what God has instituted, and those who do so will bring judgment on themselves. ... ⁴ But if you do wrong, be afraid, for rulers do not bear the sword for no reason.

The bibles answer is say no to drugs if that given substance is criminalised. The same would go apply to criminal-market prescription medication. Obviously the "sword" here would be the criminal justice system, as in the allegory of Lady Justice used to symbolise the justice system. This figurine (statue) holds the scales (of justice) in one hand and a sword in the other.

I suggest that, by analogy, we might apply the Biblical comments about drunkenness. E.g "Do not get drunk with wine, for that is debauchery, but be filled with the Spirit" (Ephesians ch5 v18). The point is that these substances are similarly competing with the Spirit of God in the field of moving the human will. As in the case of wine, the evil would lie not in taking the substance but in being controlled by the substance.

In both cases, again, there is a legitimate reason for concern about the effects of impaired judgement (whether moral or physical) on the lives of other people. In Galatians ch5 vv19-21, drunkenness is listed among the "anti-social" examples of "works of the flesh".