SQL Server Credit Card Encryption and Decryption

By: Eli Leiba   |   Updated: 2017-06-21   |   Comments (9)   |   Related: > Encryption

Problem

Credit card data is very sensitive and has a variety of regulations around the globe.  For our application, the business requirement is not to store this data in clear text in the customers table in the database, so we are looking for options to store only the hashed and encrypted value. The reason for storing the encrypted value of the credit card in the customers table in the database is to give the customer the option to re-use the card without having to retype the card’s value again as input. The system needs only the customer ID, the encrypted card number value and the secret phrase in order to recalculate the real credit number.  How can we address this need?

Solution

First and foremost, be sure you are well aware of the legal requirements to store sensitive data prior to considering any solution.  For our application, the method for solving this problem involves an encryption T-SQL stored procedure and a T-SQL decryption function. These objects will use the built-in SQL Server ENCRYPTBYPHRASE and DECRYPTBYPASSPHRASE functions.  The stored procedure will get a secret phrase that is not stored in the database along with the card number as input.

There are two items that are important to note for the Customers table:

1. The customers table has a key column named CustID varchar(20) NULL
2. The customers table has a column name Encrypted_CredCard varbinary(256) NULL

This method of using a T-SQL stored procedure for the encryption process and a T-SQL Scalar UDF for the decryption process is good for generalizing all calls to the built-in SQL Server ENCRYPTBYPHRASE and DECRYPTBYPASSPHRASE functions in the application code. Another advantage for this option is that these objects can be encrypted using the WITH ENCRYPTION option and to further improve the sec

SQL Server Encryption Stored procedure

CREATE PROC dbo.usp_updateEncryptedCredNo (@p_cust VARCHAR(20),@p_real_cardno VARCHAR(20),@passphrase NVARCHAR(128))
AS
BEGIN
   SET NOCOUNT ON
   IF EXISTS (SELECT 1 FROM dbo.Customers WHERE CustID = @p_cust)
   BEGIN
      UPDATE dbo.Customers
      SET Encrypted_CredCard = encryptByPassPhrase(@passphrase, @p_real_cardno)
   END
   ELSE
   BEGIN
      INSERT INTO dbo.Customers (CustID,Encrypted_CredCard)
      VALUES (@p_cust,encryptByPassPhrase(@passphrase, @p_real_cardno))
   END
END

SQL Server Decryption Function

CREATE FUNCTION dbo. fnGetCredNumber (@p_cust VARCHAR(20),  @passphrase NVARCHAR(128))
RETURNS VARCHAR(20)
AS
BEGIN
   DECLARE @credno varchar(20)
   SELECT @credno =  CONVERT (varchar(20),DECRYPTBYPASSPHRASE( @passphrase,Encrypted_CredCard))
   FROM dbo.Customers
   WHERE CustID = @p_cust 
   
RETURN @credno
END
GO 

Sample Executions

1. Suppose we want for customer "ABC" to encrypt the following input as their credit card number: 1234-567890
2. We get the following phrase from a secret registry value that is not stored in the database:

"CRAZY LITTLE THING CALLED SQL!"

We execute the procedure:

exec dbo.usp_updateEncryptedCredNo 'ABC', '1234-567890', 'CRAZY LITTLE THING CALLED SQL!'  

We get the following customer encrypted ID card number:

When we want to decrypt the card number we call the decryption function:

select dbo.fnGetCredNumber( 'ABC', 'CRAZY LITTLE THING CALLED SQL!' ) 

We get the decrypted credit card value:

Next Steps

• If you liked this solution you can compile and use these procedure and function in your code.
• Please hide the secret phrase that you are using in a hidden registry value or an initialization file. The registry value can be read by SQL Server only at execution time using the xp_regread extended stored procedure
• You can create hidden registry values using the reghide utility from windows sysInternals. See - https://technet.microsoft.com/en-us/sysinternals/reghide.aspx
• If you want to hide the value in an initialization INI file you can hide the file in Windows. See -  https://www.howtogeek.com/194671/how-to-hide-files-and-folders-on-every-operating-system/
• The SQL Server Stored Procedure and Function were tested on this SQL Server version:
  • Microsoft SQL Server 2014 - 12.0.2000.8 (Intel X86)
  • Feb 20 2014 19:20:46
  • Copyright (c) Microsoft Corporation
  • Standard Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

About the author

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips