The worm is found in some brands of mezcal. Mezcal is a Mexican type of traditional hard liquor, but it's different from tequila. According to some people, tequila is essentially a type of mezcal, not the other way around. That doesn't change the fact, though, that no one ever puts a worm in the tequila. The worm is only found in some brands of mezcal from the Oaxaca area.
The urban legend I was told when I first had mezcal was that it was made from a psychotropic cactus, chemically similar to mescaline- thus the name. The worm supposedly absorbed a lot of the mescaline, so if you ate it you would trip for days.
The reality is that mezcal has nothing to do with mescaline. It's made from a type of agave plant called the maguey, which sometimes gets infested with moth larvae. When this happens, it results in second-rate mezcal, so the presence of a moth larva in the mezcal is almost the exact opposite of a mark of quality.
If you drink the mezcal and eat the worm, you are not consuming liquid mescaline and then chewing on a psychoactive larva. All you are doing is drinking some booze and eating a maggot. Sorry, frat boys, but those are the facts.