Php SQL Server database encryption


I need some advice on encrypting data within an SQL Server database that is accessed by PHP and directly through SQL Server Management Studio.

I only need to encrypt certain fields in the database and don't want to add too much overload to the server and slow the performance.

I am using the sqlsrv extension and SQL Server 2014.

Are there any best practices or existing built-in functions to do this?

Thank you
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'm guessing you want symmetric encryption (same key/password both encrypts and decrypts).

If you want the encryption and decryption to happen on the database server, then you can use the ENCRYPTBYKEY and DECRYPTBYKEY functions in T-SQL, which are pretty straightforward: 

If you'd rather put the load on the server running PHP, then your best bet would be to use the OpenSSL extension to do your cryptography. If you want some examples, then please share the specific algorithm or crypto requirements you want to use,
rwlloyd71Author Commented:
Thanks for the advice.

I have been reading up on ENCRYPTBYKEY and DECRYPTBYKEY and think that I understand the process to run an SQL to encrypt and decrypt data.

When it comes to accessing the data through a php script, should I just include the ENCRYPTBYKEY and DECRYPTBYKEY functions witiin the sqlsrv_query statement?

So if I generated a random security key, such as "xyz123", I would store an encrypted email in the database with

$sql="insert into TblData (EncryptedEmail) values (EncryptByKey('xyz123', '')"
$result=sqlsrv_query($conn, $sql);

and to decrypt

$sql="select DecryptByKey('xyz123', EncrypredEmail) from TblData"
$result=sqlsrv_query($conn, $sql);

How would I best generate and store the security key (xyz123) so that it is not exposed but available to the php script. Aloo, would I need a different key for different records or fields or could I just use one key for everything?
It's up to you whether or not to use the same key or different keys. The usage of different keys is often more secure but you need a way to know which key goes to which record.

One way to do it would be to come up with a random string that would be stored on the record itself, like "axgijlsrhdj". Then have PHP create a SHA-1 hash using that value plus some other fixed value. So when you're first creating the record:

$part1 = "axgijlsrhdj"; // this would be generated randomly
$part2 = "myfixedkey";
$key = sha1($part1 . $part2);

Then store only $part1 in the field on the record and encrypt using $key.

Later when you fetch the record, do one query to get part1 back from the database record and reconstruct the key in the same way:

$part1 = $record["key1"]; // "axgijlsrhdj"
$part2 = "myfixedkey";
$key = sha1($part1 . $part2);

Then run a second query to get the rest of the record back and decrypt the values using $key.

The downside of this is that you need 2 queries to get back the full record (the first query to get the partial key and the second query to decrypt the rest of the data) but that's the price to pay for the added security.

If you use the OpenSSL extension in PHP then you could do the decryption on the web server and get all the data back (key part and encrypted data) in one query and then decrypt it afterwards.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Just a note - there is no one perfect way of doing this. Security is like an onion - it's made up of layers and the more sensitive the information you are protecting, the more layers you should have.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwlloyd71Author Commented:
Thanks Gr8gonzo

I'm going to go with encryptbypassphrase as it seems to bypass all the certificate bits that I am not too familiar with.

It works with t-sql and can be used through php. I'll use a combination of 2 fields to generate the phrase.

Thanks for your help.
Don't do that only to "bypass the certificate bits" - certificates have absolutely nothing to do with symmetric encryption. OpenSSL is a bit of a swiss army knife - it can do a ton of security-related things, including both asymmetric encryption and symmetric encryption.

Asymmetric encryption usually takes the form of public/private key (or "PKI" for short) and that's where you're often dealing with certificates and key size bits and all that. You usually only need asymmetric encryption when you don't entirely trust the recipient or when there's a risk that the message could be intercepted, which is why we use PKI for the public internet (it's what HTTPS is based on).

Symmetric encryption doesn't use certificates. At a minimum, it just uses a key. Here's a pretty basic code example that uses AES-256:

// Your key and the original data
$key = "My secret key";
$data = "Hello world!";

// Encrypt
$encrypted = AES256::Encrypt($data, $key);
echo "Encrypted: " . $encrypted . "\n";

// Decrypt
echo "Decrypted: " . AES256::Decrypt($encrypted, $key) . "\n";

// ============================== DON'T CHANGE ANYTHING BELOW THIS LINE ==============================

// Just include the below code as-is
class AES256
  public static function Encrypt($data, $key, $cipher = 'aes-256-cbc', $options = OPENSSL_RAW_DATA)
    $key = self::PBKDF2Key($key);
    $cipher_length = openssl_cipher_iv_length($cipher);
    $IV = openssl_random_pseudo_bytes($cipher_length);
    return base64_encode($IV . openssl_encrypt($data, $cipher, $key, $options, $IV));
  public static function Decrypt($data, $key, $cipher = 'aes-256-cbc', $options = OPENSSL_RAW_DATA)
    $key = self::PBKDF2Key($key);
    $cipher_length = openssl_cipher_iv_length($cipher);
    $data = base64_decode($data);
    $IV = substr($data, 0, $cipher_length);
    $data = substr($data, $cipher_length);
    return openssl_decrypt($data, 'aes-256-cbc', $key, $options, $IV);
  private static function PBKDF2Key($key)
    return openssl_pbkdf2($key, null, 32, 1000, 'sha256');

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.