There are three games that have scared me throughout my career in gaming. They are (with rating):
~Silent Hill 2 (6 out of 10) : This will always be the king of games for me, that have the best atmosphere to make me want to empty my bowels.
~That one Haunted Castle secret mission in an early Medal of Honor game (8 out of 10) : BEWARE THE DANCING DOGS!!!!!!11
~And finally (100 out of 10):
EMERALD WEAPON from FF7!
No bullshit! There I was, cruising along underwater with nothing on my mind, just chilling to find what things I can screw with next, and then BAM! This giant fucking thing comes out of nowhere, heading straight at me at 1,000,000,000,00 BA ZILLION MPH and here I am in my piddly little submarine trying to escape! Then it gets me into battle, fucks me over, and I have been afraid to go underwater in any game ever since. Even the fish levels from Super Mario Bros. is malicious!
To be fair though, FF7 had a bunch of moments that at least made the hairs on theb ack of my neck stand up. Examples being when Jenova breaks out (or anything with Jenova for that matter), and the Hautned Mansion at the Gold Saucer. ^^
And especially, for my fellow FF7 ultra geeks. Who has goen into the dbug room and done the few creepy-ass things in there? Remember, this IS Hades. ^^
EDIT: I'd like to add the ShinRa Mansion to the list. The laboratory down there invokes bad memories...