Paintings of the past depicted horror, dread and foreboding. But can art still frighten us into a new consciousness, asks Kelly Grovier.