The scariest movies to me are Signs and Fire In The Sky, because the whole idea of a semi-realistic alien invasion/presence freaks me the fuck out.
Obviously The Exorcist is an answer too.
The stupidest horror movie I've ever seen was Garden Of The Dead AKA Tomb Of The Undead. The story had such potential, but it was just done so, so poorly.