Silent Hill 3 was the most freighting in my opinion. Between the Closers, Double Heads and Insane Cancers and the eerie distant howling in various areas it got very close to scaring me several times. And that's hard to do.
Second would have to be Origins. I can't quite out my finger on it but something is deeply disturbing about that game.
the only part where it got a bit intense was in the theater. Mostly because I hate dolls and those Ariels were the worst monster any game can throw at me.
The Sanitarium was pretty freaky too, a lot of open space made me feel like there were more than one monster in the room.
Silent hill 3 was the scariest silent hill game i played, origins wasn't really too scary it had its moments.
In order of scariest to least scariest i would say it goes SH3>SH4>SH2>SH0>SHH>SHSM haven't beaten the original silent hill yet...