Some % of players will not look on such as "fun". Though I'm sure there are some who love crazy weird things happening to their PCs. It's a mixed bag. Know your player.
Totally fair point. The player in question is, I believe, a player who enjoys interacting with the world in all sorts of ways and seeing the effects of those interactions, be they positive or negative. The characters had a cleared a room full of zombies and zombie cultists, and the character was then investigating a murky looking barrel of water. (That, in this case, contained nothing of interest.) When he decided to drink from the murky zombie water, I figured that was a risky move and called for a constitution save, which he failed spectacularly. And then I stalled by saying that the water tasted awful and didn't seem to settle so well in his stomach.
Which is all to say that I feel I owe the players something, but preferably something some short-term consequences that are more visually dramatic than punitive. He has plenty of other opportunities to make bad things happen to his character.
I like the worm idea, though. Thinking maybe:
Parasitic Graveworms.
The character wakes up to find himself retching up small deathly white worms. Throughout the course of the day, the worms slowly climb up his throat and into his mouth. At random moments, make a DC 13 Con save to avoid puking up another batch of worms. Disadvantage on all Charisma checks requiring opening his mouth. (Great opportunity to brush up on his ventriloquism.)
Can be cured by drinking a small amount of holy water, or with a Lesser Restoration spell. (Or any other higher level spell that also removes the effects of disease.)
But hey, when he decided to crawl into the coffin of the wight he had just fought, he found a secret compartment containing a magic sword. No investigation check required!