I've worked on some seriously contaminated sites, and I have close and personal experience with the fact that you can find contamination anywhere. Rural? Suburban? Expensive neighborhood? Doesn't matter. Especially in the northeast US, which has been industrialized for hundreds of years, you can't rely on looks to determine if the subsurface is clean. There are too many old industrial areas that have gone to seed decades before redevelopment, too many hobbyists with extensive solvent collections, and too many old farmers who accepted drums of sketchiness before RCRA (the resource conservation and recovery act) forced people to track hazardous waste from the cradle to the grave.
So, would you buy a house in/on/over a contaminated site?
As an environmental professional, my answer would be "it depends". First of all, I'd want a deep discount, because even if I'm ok with living there, it doesn't mean I wouldn't have a tough time selling later. But setting costs aside, for me it would depend on the type of contamination and the relative risk.
I wouldn't buy a house with a contaminated drinking water supply. I'm not a big fan of private well water in general because it's another system to maintain/worry about. I wouldn't want to buy a house with contaminated well water because if I need to have a treatment system, then I'd need to either maintain it or accept that someone else (a regulatory agency or whoever "owns" the contamination) will be trooping into my basement on a regular basis for the foreseeable future to sample/maintain it.
Likewise, I wouldn't buy a house with a VOC (volatile organic compound) problem in the shallow subsurface. I'd be resigned to regular monitoring of the soil gas beneath the house and the indoor air, and I may have to deal with the noise and bother of a soil vapor extraction system to remove contaminated air from under the basement floor.
I would be ok with a house above a plume of contaminated groundwater as long as I'm not drinking it and it's deep enough/non-volatile enough that it would not pose a concern of contamination getting into the soil gas and/or the basement. I would be perfectly fine with non-volatile contamination in the soil, such as metals or asbestos. Worst case scenario, I'd build out a seriously extensive front and back patio, arrange to truck in a foot or so of fresh dirt around the rest of the yard to cover any surface contamination, plant vegetables in raised beds, and keep any digging to a minimum.
In reality, most of the contamination you'll find in and around private homes is from the house itself. House built before 1978? You probably have a lead problem at the drip line of the outside wall and several feet out from the house as well as on the inside. Same lead problem for houses built near busy roads. Have a reasonably high-end house built before about 1980? You may have asbestos insulation around pipes and in the ceiling. Do you keep a collection of stains, paints, and paint thinner from various projects in the basement? Do you smoke? The VOCs from that stuff will overwhelm the contaminants from a nearby groundwater plume.
My tolerance for contaminated property would probably be considered to be reasonably high. But I realize that's easy for me to say - I'm not the person trying to unload a house that's lost a huge chunk of value because of real or perceived contamination.