It would seem to me that most payment systems would encrypt all the
credit card information at [the database] (making the information itself
You are correct that all payment systems should encrypt the credit card information at the database. However, your assumption that this makes the information unobtainable is incorrect.
Most simply, the data in the database is there to be used. In order to be used, it must be decrypted. Therefore, the legitimate business systems and users must be able to decrypt it. An attacker will either access it while it is legitimately decrypted, attempt to compromise the encryption keys, or use the legitimate processes that leverage those keys.
Here's a quote from the PCI Security Standards Council's PCI Data Storage Do’s and Don’ts
Some cryptography solutions encrypt specific fields of information
stored in a database; others encrypt a singular file or even the
entire disk where data is stored. If full-disk encryption is used,
logical access must be managed independently of native operating
system access control mechanisms. Decryption keys must not be tied to
user accounts. Encryption keys used for encryption of cardholder data
must be protected against both disclosure and misuse. All key
management processes and procedures for keys used for encryption of
cardholder data must be fully documented and implemented. For more
details, see PCI DSS Requirement 3.
So, let's consider three methods of encrypting card data that is in a database, and the weaknesses which allow attackers to get the card data anyway:
- Full-disk Encryption
- Transparent Data Encryption
- Application-layer Encryption
With full-disk encryption (and with file-level encryption of database files at the OS level), the data is only encrypted when the database is not running. In order for the database to run, the files must be decrypted. With something like Bitlocker, EFS, or LUKS+dm-crypt, once the filesystem is unlocked (usually at boot time) the data is readable just as if it was not encrypted.
Once the files are unlocked and the database up and running, the attacker can query cards out of the database using SQL. They can copy the decrypted database files off the system and peruse their contents. They can even use something like 'strings' and 'grep' to pull information out of the files from the command line.
Full-disk encryption protects the data if someone steals the server or the disk. It does not protect from an attacker who gains access to the running system. It does encourage dual control (DBAs might require IT to unlock the disk for them).
Transparent Data Encryption
TDE is similar to full-disk encryption, except that it it maintained at the DBMS (Database) level rather than the OS level. And it has the same weakness; once the database is spun up and the keys provided to unlock the data, the data is available to users, and abusers, of the system as if it were not encrypted.
Once the database is up and running, the attacker can query cards out of the database using SQL. That's the whole "Transparent" thing.
Unlike full-disk encryption, TDE often puts both the keys and the encrypted data into the hands of the DBAs.
Under Application-layer Encryption, the encryption keys are held by the application, and sensitive data is stored encrypted in the database. Since the database and the application code usually run on different servers, this provides a layer of separation - an attacker who gains complete control over the database server cannot decrypt the data unless they also compromise an application server.
Data is decrypted in a "just in time" fashion; only discrete card numbers are decrypted as needed. Unlike the other two options, the entire data set is not sitting around decrypted in a running system.
An attacker needs to compromise both the keys and the data, or compromise a decryption process (e.g., if they can run the application program that pulls a card out and decrypts it, then they can pull and decrypt until they're caught).
Application-layer systems support the advantage that decryption can be logged by the application. If the attacker compromises a process rather than a key, they may generate a logging trail which makes their activity obvious. If it is normal to see 200,000 decrypts an hour, and that jumps to 2,000,000, something's going on.
Dual-control is somewhat standard with Application-layer encryption.
So - three different methods, some stronger, some weaker. All meet PCI requirements. All can be compromised by an attacker, because all allow legitimate users to access sensitive data.