In order to run code in an autonomous manner, a server must store the decryption key somewhere, and a hacker can usually find it, rendering the encryption useless. Limited exceptions can be made for keys that are required on boot and not stored, but when that happens they are stored in memory and can possibly still be had by a smart hacker.
walletpassphrase won't help, because the hacker can either:
scan your memory cache
or
just use the same session you are already using. for example, use your own JSON-RPC to send bitcoins.
Exactly, and the type of attack depends on how much access the hacker has, and how he got in. If he was able to reset the root password through a control panel for instance (like bitcoinica), he would have root access to plant a keylogger or pick up the passphrase if it were stored on disk. If he broke in through a SQL vulnerability, he could grab the passphrase from memory, even if it were not stored on disk. It's a difficult kind of situation, but HSMs such as what DeathAndTaxes is looking into can mitigate the risk somewhat.