A sleeper car is primarily an automobile which has been souped up, which is to say had its performance increased in the form of added horsepower/torque, in such a way that the performance is not obvious. This is the opposite of a rice rocket or rice burner (rice rocket originally applied only to motorcycles) which is a car which has been dressed up to look like it is faster than it really is. Alternately, it can be a powerful version of a car which has been dressed down such that it looks like a slower version of the same vehicle, for example a Ford Mustang GT dressed down to look like the V6 model, or a Chevrolet Camaro Z28, SS, or IROC rebadged as the RS. It can also refer to a car which is a good performer, and mechanically sound, but outside appears as a beater, which is to say too damaged to perform well.
Besides people led by the imp of the perverse, sleepers tend to be driven by men and women who have escaped their twenties who prefer not to be pulled over by the law frequently but still want to drive a fast car. There is a sense of satisfaction in "blowing away" (not literally, nor in the sense of firearms) the person sitting next to you who thinks their car can beat yours because it makes you look like a soccer mom. This is the kind of car where when people talk about it they say things like "that car doesn't look like much but every time it goes by the walls shake."1
A sleeper is quiet when it's "sleeping", since cars don't snore, but you can typically tell the difference when it's started and gives off its roar. Of course, even most modern performance vehicles are not very loud at idle because the average displacement of the modern engine is much lower than what it used to be 20 or 30 years ago.
In response to other writeups in this node: The most powerful non-turbo Neon has 160hp. A honda civic with a 2.2l DOHC prelude engine swap has over 200. The neon is intended to be a sports car in some configurations, and based on the specs and design is not at all a sleeper. Nor is a civic a sleeper when it is outfitted (for example) with a turbo and a big exhaust. However, if it has a stock-appearing exhaust and the prelude engine swap, THAT is a sleeper. Celicas are also sports cars and as such they aren't sleepers. Just because people don't know or don't want to know that a specific car is fast, that doesn't make it a sleeper. The Escort ZX2 weighs almost 2500lb and has only 130hp and about 130ft-lb, unless it's got a five speed and is gear limited to about 100mph there is no possible way that it runs a 12 second quarter... Maybe 12 seconds 0-60 :P (Actually, 1998 Ford Escort ZX2 is 0-60 in 7.4, with a 15.7 second quarter mile.)
Thanks to Transitional Man
for this tidbit