title: Protect PII in web app database by encrypting with public key paired with private key protected by users' own passwords?
body: <p><strong>Goal:</strong></p>
  
  <p>I'd like to allow users to create questions and collect information from other users in a custom web app (PHP/MySQL in a shored hosting environment) and protect the data collected.</p>
  
  <p><strong>Background:</strong></p>
  
  <p>The default questions that all users answer are general enough that they can not be construed as personally identifiable information (PII), thus limiting my liability to protect it, but users who create their own questions will likely ask for PII which then becomes a liability.</p>
  
  <p>What I would like to do is protect this information in such a way that if either the hosting account or the database were compromised (or both!), the PII would not be recoverable without a significant amount of work, and even then, only a small portion would theoretically be recoverable.</p>
  
  <p><strong>Proposed solution:</strong></p>
  
  <p>Assuming MySQL's built-in <code>AES_ENCRYPT()/AES_DECRYPT()</code> functions are used to encrypt the PII table, the passphrase would need to be stored in the hosting account, so if the hosting account were compromised, the data could easily be read.</p>
  
  <p>Since the users' passwords are well protected (hashed with salt), I'm thinking of capturing their plaintext password during authentication, encrypting it, and storing it in the PHP session until the user logs out. </p>
  
  <p>A public/private key combo will be created for each user with the private key being password protected with the user's password + salt.</p>
  
  <p>Then, when PII data based on that user's custom questions are added to the DB, the user's public key would be used to encrypt the PII that they collected through the app. When the data is read (only when the user is logged in), the data would be unencrypted with the user's private key (which is unlocked with their password + salt).</p>
  
  <p>The <strong>benefits</strong> I see are:</p>
  
  <ol>
  <li>in the worst case scenario, where servers completely compromised, app code is read to find encryption keys, PHP session files are decrypted to find user's passwords, then entries in the PII table associated with that user are decrypted, then <em>only</em> the PII collected from questions of currently logged-in users could be recovered. Any users not logged in would be safe.</li>
  <li>even the DBA or similar wouldn't be able to read the PII.</li>
  </ol>
  
  <p>The <strong>drawbacks</strong> I see are:</p>
  
  <ol>
  <li>user passwords are stored in a recoverable form while they are logged in.</li>
  <li>users who forget their passwords would loose access to their data.</li>
  <li>each relatively small bit of data will take up much more space in the DB due to encryption.</li>
  </ol>
  
  <p><strong>My question:</strong> Is there a better way to do this?</p>