Nearing my 40th birthday, and having feasted on the horror genre since I was a rugrat, there have only been a few films which really have gotten under my skin, even as an adult.
The Exorcist and Paranormal Activity. As Werewolf Girl alluded to, the fire and brimstone religious indoctrination you are assaulted with as an impressionable child seems to leave your adult head still rooted deeply in those fears. I'm an agnostic, and have been for decades. I'm also probably the least religious person you could ever meet. Religion is just not on my daily radar. At all.
However, demons and demonic possession, when presented to me, really touch a nerve in me. I saw The Exorcist when I was growing up, and have never revisited it. I've been avoiding it all these years like a little kid.
Paranormal Activity was a case to where I wasn't actively scared while watching the film itself, but when I was home alone later that night, in the silence and in the shadows of my abode, its impact was undeniable. I literally didn't get an honest night's sleep for a week after seeing it. In my estimation, given that my first viewing came so well into my adult years and that its effect was so profound, I have to say that Paranormal Activity is the scariest film that I have ever seen.
The sequel? Not so much...
The only other film that owns me is actually outside of the demonic possession genre, and that's Brian De Palma's Dressed to Kill (1980), which I saw as a teenager. The killer's look has literally haunted my nightmares and the ending's [spoiler]haunting dream sequence[/spoiler] just creeps me out, to this day.
Like The Exorcist, I've also managed to avoid revisiting Dressed to Kill since my first viewing.
Like a little kid, safe underneath my covers.