SQL Server Credit Card Encryption and Decryption

By:   |   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:

ABC0x01000000BCE3FB45132F4C0E879EB5ADF984339B3C52AD7457D70FF6AE8810438FA5E92C

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:

1234-567890
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)


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Eli Leiba 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


Comments For This Article




Saturday, April 14, 2018 - 4:23:36 PM - Ramu Back To Top (75702)

Encrypt data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.

If you go with hash function, you have option to select different algorithm.

But generally, you will want to encrypt with symm/cert as others suggested in this forum.

 

 

Sunday, February 18, 2018 - 3:58:56 AM - Eli Leiba Back To Top (75233)

 Some security notes:

  1. I would like to cay that usually credit card protection requires compliance with the Payment Card Industry Data Security Standard (PCI DSS, which is mandated by the industry: see  https://www.pcisecuritystandards.org/  like what Tibor said

 

   

2. Also , The passphrase will be exposed in traces (even if the name is changed to @password). This is one of the reasons why using symmetric keys instead of passwords are preferred. An even better option is to use Always Encrypted  where you can have the Key-Encryption- Key (KEK) and the Data-Encryption-key (DEK) stored separately.

 

   

3. Additionally, in your comments, you added a script to an xp_cmdshell payload (as I understand, to execute a separate program that would perform the encryption). Please be aware that this script is susceptible to injection attacks, and an attacker could leverage bugs like this one for batch injection

 

 

Wednesday, June 28, 2017 - 3:24:52 AM - Eli Leiba Back To Top (58466)

 I agree that using using certificates is a more secure method. The purpose of this tip it to show a simple way of encryption that is very easy to implement.

 

Monday, June 26, 2017 - 7:07:16 PM - Jeff Moden Back To Top (58324)

I'll also have to add that storing anything in a supposed hidden file or registry entry is a bit crazy.  There are too many people with the correct privs to unhide them.  Also, depending on supposed encrypted procs is virtually no protection at all.  The internet is littered with methods to decrypt encrypted procs, etc.

Monday, June 26, 2017 - 6:36:29 PM - Jeff Moden Back To Top (58317)

It my be just me but the following code from the encryption proc seems to be missing something really important...

IF EXISTS (SELECT 1 FROM dbo.Customers WHERE CustID = @p_cust)
BEGIN
    UPDATE dbo.Customers
       SET Encrypted_CredCard = encryptByPassPhrase(@passphrase, @p_real_cardno)
END

I'm thinking that's going to update ALL the customers' Encrypted_CredCard because there's no WHERE clause.

I'm also thinking that this method is a whole lot less secure than using proper certificates which won't work if you copy them to a different machine.  IIRC, registries can easily be copied.

Monday, June 26, 2017 - 10:00:49 AM - Eli Leiba Back To Top (58267)

 

  • Hi
  •  
  • I suppose that if the user will bring an executable file that does the decryption of the secret phrase. 
  • So prior to calling my function,  a T-SQL procedure will call xp_cmdshell to his executable , get the decrypted string and use it as an input to the function
  • This can't be done inside my function because function cannot call procedures or executable code that may do update actions
  •  
  •  
  •  I Wrote a suggested T-SQL script :
  •  
  • Notes 
  •  
  • 1. allow xp_cmdshell option using sp_configure system procedure - 
  • 2. It's assumed that your executable reside on c:\temp directory - 
  • 3. replace the dir dos command with your executable - 
  •  
  • The additional T-SQL Scripts should look somewhat like this :

 

 

 

 

 

 

set nocount on
declare  @filepath   varchar(255),
         @cmd        varchar(255),
         @rc         int

select   @filepath = 'c:\temp\'         
select   @cmd      = 'dir ' + @filepath 

Create table #output (output varchar(255) null)
insert #output exec @rc = master..xp_cmdshell @cmd
select * from #output where output is not null
drop table #output

Regards

Eli

 

 

Sunday, June 25, 2017 - 7:54:04 AM - Eli Leiba Back To Top (58139)

Changing @passphrase variable name to the name :  @password in the procedure's code can be simply done and is a good practice.

Thanks for your comment

Eli

Wednesday, June 21, 2017 - 8:39:00 PM - Bill Back To Top (57893)

If @passphrase is replaced by @password within the body of fnGetCredNumber, I am fairly certain a trace of the SP::StmtCompleted event will not reveal function's T-SQL and its pass phrase.

 

Wednesday, June 21, 2017 - 4:28:58 PM - Tibor Nagy Back To Top (57880)

Hi Eli,

Please note that you shall never store any encryption key in clear text according to the PCI DSS requirements so you cannot store the secret phrase in a registry value or in an initialization file without encryption.

Regards,

Tibor














get free sql tips
agree to terms