The curves tool stretches or compresses ranges of input tones according to the line you draw. Because it represents a mapping, it's nonsensical for the line to go backwards horizontally — which means you can't possibly draw a "proper" S — you have to draw one that is tilted (that is, slanted) to the right.

These examples are in grayscale because you can actually apply curves separately in each color channel; having it gray makes the example more straightforward. I'm also making more extreme curves here than one would in real life. Anyway, here is a "neutral image", straight from RAW conversion, along with the straight-from-the-bottom-corner-to-top-corner "curve" (a line is a kind of curve!) that represents no adjustments:

One can dramatically increase the contrast by compressing the range of levels, done easily by moving the start- and end-points inward. This is similar to (but more extreme than) what you will get by hitting "auto" in the Levels dialog:

Woah! Contrast.

But in doing this, we've clipped the highlights and shadows significantly, discarding all detail in the bright and dark areas. An S curve makes this more graceful:

Still a lot of contrast, but more detail in the shadows and brighter areas like the sky, and a much more graceful transition between them. Now, were I working on processing this for real, I wouldn't make the curve perfectly symmetrical like this, and I'd pay more attention to bringing out the detail on Anya's face. (In fact, I'd do that before grayscale conversion, but that's a whole 'nuther topic.)

And, as a final exercise, back to the original question, what happens if we try to draw a non-slanted S? We can't perfectly, because the retrograde lines are impossible, but we can make the center line straight and draw the top and bottom curves attached to that....:

This does horrible, strange things to the tones in the bright and dark areas, and completely compresses the midtones. It's fundamentally non-nonsensical — although one can use the tool in this way, I can't imagine a case where it'd be actually useful. And that's why you want a slanted S: it happens to be the adjustment shape that increases contrast with graceful transitions.