The Pearson correlation measures the amount of linear relationship -- it doesn't ignore variables that have a relationship that's not perfectly linear. If things increase and decrease together, some portion of their relationship is explainable as linear relationship (and some of it isn't).
For example, if $X$ is positive, then both $X$ and $X^2$ will increase or decrease together, and so be somewhat positively correlated. On the other hand if $X$ is negative, then $X^2$ will increase as $X$ decreases (becomes more negative).
Here's a case where the population mean of $X$ is large compared to its spread, and so $X$ and $X^2$ have a high Pearson correlation:

In this case the population correlation is about 0.99867 and the sample correlation was about 0.99868.
If $X$ is both positive and negative then there are parts where $X^2$ increases as $X$ increases and parts where $X^2$ decreases as $X$ increases. This may result in an overall positive, negative or zero correlation (depending on the extent to which they cancel out).
cor((-10:10), (-10:10)^2)The correlation is 0. – rbatt May 06 '13 at 19:11