TCP Sequence Prediction and it's prominence in modern systems/networks

TCP sequence prediction was a hot topic around 2001, but most vendors have patched their OS quite quickly around that time. See this CERT advisory from September 2001, which includes statements from various vendors. Basically, if the TCP sequence prediction attack works against a system, then that system was not updated with security fixes from more than a decade, which almost certainly means that it has much bigger security issues.

To generate unpredictable sequence numbers, implementations are encouraged to follow the technique described in RFC 6528 (which supersedes the older RFC 1948). This is a custom PRNG with MD5. Cryptographically speaking, this PRNG kinda sucks: apart from using a hash function of questionable robustness, it also uses it "wrong" (that's a Merkle-Damgård construction; care should be employed when simply concatenating a key with some known input; HMAC should be used instead, as a building element); and the overall scheme is not very fast (a good stream cipher would be much better). However, speed is not a strong issue here (a low-end system will still be able to compute that algorithm millions of times per second; processing of incoming SYN requests will be the bottleneck, not the hashing) and we don't need really high cryptographic strength, since the attacker can "get lucky" with probability 2^-32 anyway, so the PRNG needs not be better than that (typical goal for cryptographic algorithms is 2^-80 or less).

Summary: TCP sequence prediction attack is a thing of the past; nice to know, but not applicable to systems which have been maintained for the last ten years or so.

Personally I've yet to find a modern system that has had this vulnerability.

Many, if not all, modern operating systems have been fixed to use unpredictable initial sequence numbers as proposed by RFC 1948. RFC 1948 was designed specifically defend against sequence number attacks that you've defined. This was originally an issue in the early 2000's and as a result, most new developments have been patched before release.

Nmap will provide you with TCP Sequence information and makes an educated guess as to how hard the system is to TCP sequence prediction.

To date, all live systems that I've personally tested (that haven't been windows 95 or NT machines) have been defined as 'Good luck'('impossible') by Nmap.

In addition to D3C4FF's answer, the second case is of course:

If I am in the same subnet as one of the endpoints, I can inject packets into existing TCP streams by predicting sequence numbers.

This of course hinges on the fact that the attacker can see the sequence numbers being used by an existing TCP stream - this is not about predicting initial sequence numbers.

I personally have used TCP RST injection in this style to turn off a housemate's internet radio after he refused to turn the volume down.

As for how widespread this method is in the wild, I would say that it is just one more attack available to malware authors. If I was writing a rootkit, and I wanted to wreak havoc in your internal network, TCP sequence prediction is just one on a very long list of tools that I might consider.