How to keep sensitive ecommerce data in MS SQL Server from being read by simple queries


  If you like it, share it!

Facebook Twitter Google Reddit LinkedIn Email

How to keep sensitive eCommerce data in MS SQL Server from being read by simple queries

(Article published by Stephen P. Thomas on eForensics Magazine, Vol. 3, No. 1, Issue 01/2014 (27) January)

What you will learn
  •  measures to protect sensitive data in eCommerce websites;
  • methods to prevent a SQL injection attack;
  • Transaction SQL (T-SQL)
  • encryption mechanisms ad encryption algorithms;
  • practical code examples;

What you should know
  • Microsoft SQL Server database;

In this new digital era with cyber-attack after cyber-attackwe must take counter measures to protect sensitive data in our ecommerce websites. For those of us who use Microsoft’s powerful SQL Server database tostore sensitive ecommerce data including but not limited to credit cardinformation, copyrighted media, official documents, etc. They are many suitableoptions available to secure sensitive data by encryption.  This article will reveal to you severalencryption options available to you in SQL Server, explain the differencebetween them, and give some practical code examples for using them.

The understanding of the security systems in SQL Server isessential in order for the professional developers and database administrators to take advantage of the many powerfulfeatures available. With that note let's get started.

Encryption is the process of encoding data in such ways asto render it unusable to anyone except those in possession of the appropriatesecret knowledge required to decrypt the data. The encryption capabilities ofSQL Server are powerful features and they should be used as a part of theoverall security strategy especially with ecommerce sites.

When trying to connect to SQL Server, security is checked inthree places. Validation is performed on the operating system, SQL Server (asan identifier for connection to SQL Server) and at the level of individualdatabases (in the form of a user name database).

It is important to know that all the data is important andmust be protected. That is why there are an increasing number of professionalswho are conducting research on this subject. Having this into consideration,most of the encryption methods in SQL Server provide cell-level encryptionwhich is applied to individual items of data or columns of a table.

In SQL the commit point is represented by one SQL statementand for transactions it is needed that this commit point to be extended over aset of SQL functions. Of course, the whole set of SQL statement must roll backif any of the operation fails.

With the help of Transaction SQL (T-SQL) you can implement inthe SQL Server database a high level of security, cryptographic functions andhigh-grained role based security.  Thereare four different types of encryption mechanisms that are supported by the SQLServer.  Each of the encryptionmechanisms supports different encryption algorithms.

·        Symmetrickey encryption – require the same key for encryption and decryption ofdata. This can be stream cipher algorithm (RC4) or block cipher algorithm (3DES,AES).

·        Asymmetrickey encryption – it uses different keys for encryption and decryption ofthe data. RSA is the most used one since the SQL Server supports the RSAalgorithm with 512, 1024 and 2048 bytes.

·        Certificates– they use a digital signature to associate public and private keys withtheir owner.

Data Base Master Key

Each user can have its own Data Base Master Key which is asymmetric key stored in the master database and the user database also. It isprotected by 2 different forms of encryption:

·        an encryption which is using the Triple DataEncryption Standard algorithm based on the Service Master Key of the server;

·        an encryption by a password;

Service Master Key

Service Master Key is a symmetric key that sits at the top of the SQLServer encryption hierarchy. It is used to encrypt all Data Base Master Keystored on the server.

Service Master Key is protected by the Data Protection API(DPAPI) of the operating system.


Fig.1 - Understanding the encryption key hierarchy in SQLServer

Experts on this subject understand that a wide range of encryptionmethods is provided in SQL Server and they offer different levels of protectionbased on the security requirements.

Nevertheless the most important encryption methods, whichare used frequently, are:

  • hashing;
  • symmetric key encryption;
  • asymmetric key encryption;
  • transparent data encryption ;

In the following section of this article I will present thelater methods with practical examples in order for those who are interested tohave a basic for future research regarding SQL Server encryption technique.


A hashing function, as the name implies, is an algorithm (ora function) that allows you to perform a one way encryption on the data andgenerates a hash from input.  Forexample, for any given input a hashbytes (a) will produce the same output (b)but it is important to keep in mind that there is no way back out of thisfunction and once executed you cannot retrieve “a” from the value of “b”.

SQLServer 2005 and later includes the following hashing functions or algorithms:

·        MD2

SELECT HASHBYTE (‘MD2’ , ‘SQL Server encryption’);


This hash generates CAD27E821E38CAFDBB9DF96CEB8F3965

·       MD4

SELECT HASHBYTE (‘MD4’ , ‘SQL Server encryption’);


This hash generates 5CED3C996A81FF6EDFD04880A9EE55AF


SELECT HASHBYTE (‘MD5’ , ‘SQL Server encryption’);


This hash generates  ABDCC0860487DB8DC81B439869FA403B

       SHA and SHA1

SELECT HASHBYTE (‘SHA1’ , ‘SQL Server encryption’);


This hash generates 859EF401A80A1ADE044D7107E7E0DDCDE93988B2

From 2012 version, SQL Server introduces the followingadditional hashing functions:

·        SHA2_256

SELECT HASHBYTE (‘SHA2_256’ , ‘SQL Server encryption’);


This hash generates 13F228738A5C15BA5C5FF1862F75F2BDA2DD03D440037668B2758572A60CCC17

·        SHA2_512

SELECT HASHBYTE (‘SHA2_512’ , ‘SQL Server encryption’);


This hash generates FBC8E31C7D48D51DB43C3E8D81116B773E2F611A55554352C07D847296E5A8854211372F228AF33ACB4E1B4965EB05DF0CCCED8370813D6E34AC0AA08ACC94A2

The results of a HASHBYTES function in action are always thesame and it is entirely repeatable.

For the examples stated above you will always receive thesame output no matter how many times you will execute the preceding query. As alot of specialists in SQL Server are stating and I am sharing their point ofview also, this deterministic propriety of a hashbytes function is, on the sametime, its biggest strength and weakens.

Another problem with all these algorithms is that after a lot of practical tests it has beenproven that some of them should probably be avoided as long as they aresusceptible of a collision attack (can read the input from the hashing functionor allows to create an identical function). In order to use the best methodsfor encrypting your e-commerce data it is recommended to avoid the followinghashing functions:

·        MD2 – developed in 1989 it generates a 128-bithashing function value and it is no longer considered secure after severalattacks.

·        MD4 – developed in 1990 it has the same problemlike MD2, with a hash value of 128-bit it is no longer considered secure.

·        MD5 – developed in 1992 it is similar with MD2/MD4 and is also considered broken and unsecure even if it is often used todigitally fingerprint.

·        SHA or SHA-0 – short after it was released itwas withdrawn and it was replaced with SHA1 because with a hashing functionvalue of 160-bit it had a serious error.

Last but not least, regarding the HASHBYTES function maybeone of the best way to secure them more is to is to add a secret salt value to the text before hashingit as in the following example:

Create Procedure ValidateUser

    @Username stephen(50)

    , @Password thomas(50)


Declare @PasswordSalt tekkiebooks(256)

Set @PasswordSalt = ( Select PasswordSalt From Users Where Username = @Username )

If @PasswordSalt Is Null

        -- generate a salt?

Declare @Hash tekkiebooks(max)

Set @Hash = Hashbytes('SHA1', @PasswordSalt + Cast('|' As binary(1)) + Cast(@Password As tekkiebooks(100))

If Exists(  Select 1

            From Users

            Where Username = @Username

                And PasswordHash = @Hash )

    -- user is valid


    -- user is not valid



Symmetric Key algorithms are a class of algorithms that usesthe same cryptographic keys for encryption and decryption.

Fig.2 – Symmetric Key Encryption example

As you can observe in Fig.2the Symmetric Key encryption process is not the same as in the case ofhashing. You will obtain different results each time you encrypt the sameoriginal data. This will also occur when using for subsequent the same key onthe same data.

If we are talking practically this symmetric key representsa shared information between two or more parties. This information, which issecret, can be used to maintain a private information link.

As in the case of hashing on Symmetric Key Encryption thereare some important algorithms. They are as following:


From all these algorithms the most powerful one is AES256(Advanced Encryption Standard).

Regarding the protection of these keys it is very importantto know that these can be protected by a password, a certificate, an asymmetrickey or a symmetric key as well.

You can see in the following example how to create aSymmetric Key “SymKey2014” that is protected by a password.





After this step, for encrypting the data you have to use‘open the key’ as follows:

-- Open the key



-- Declare the cleartext to be encrypted

DECLARE @Secret stephen(255) = ‘SQL Server encryption message’;

-- Encrypt the message


-- Close the key again



This code will have as a result an encrypted binary value.

In order to obtain the reverse process (decryption) you’llhave to use similar steps, as follows:



DECLARE @Secret stephen(255) = ‘SQL Server encryption message’;

DECLARE @Encrypted varbinary(max);

SET @Encrypted = Encrypted ENCRYPTBYKEY(KEY_GUID(N’SymKey2014’), @secret);

SELECT CAST(DENCRYPTBYKEY(@Encrypted) AS stephen(255);



The result of this code will be the original message “SQLServer encryption message”.


In Asymmetric Key Encryption there is a key pair. In thisway a public key is available for any person who would want to send you amessage but there is also a private key known only by yourself.

With this function all the messages that are encrypted by usingthe public key can only be decrypted by applying the same algorithm, but byusing the matching private key. Following the same login it is obvious that anymessage that is encrypted by using the private key can only be decrypted byusing the matching public key.

Fig.3 – Asymmetric Key Encryption Example

Some of the most used Asymmetric Key Encryption systems are:

Merkel’s Puzzle – which is one of the first Asymmetric KeyEncryption invented.

·        RSA  - isbased on the assumption that factoring large integers is computationally hard.

·        ElGamal - it is similar to RSA but it depends on an type of assumption calledthe discrete-logarithm assumption

·        Elliptic Curve Cryptography – it builds a finite field out ofthe set of solutions to an elliptic curve equation y2 = x3 + ax + b along with an additiveidentity element.

In order for you to understand how to effectively apply anasymmetric key encryption review following example will illustrate how tocreate an asymmetric key using a RSA algorithm


Transparent Data Encryption (TDE) is the newest encryption featureavailablein SQL Server.  It performsreal-time encryption and decryption of the entire database, and log files, and databasebackups.  It was first introduced in SQLServer 2008 and only in the enterprise edition. TDE protects Data At Rest.  DataAt Rest is data stored in persistent storage such as hard drive, tape, solidstate drive, etc, and also includes log files.  The sister term is Data In Use which refers to data that is beingprocessed by the CPU or RAM.    The TDEencryption uses a database encryption key (DEK), which is stored in thedatabase boot record for availability during recovery. The DEK is a symmetrickey secured by using a certificate stored in the master database of the serveror an asymmetric key protected by an EKM module. You can see the processexemplified it Fig.4

Fig. 4 – Transparent Data Encryption example

It is very important to remember that before proceeding theTransparent Data Encryption it is important to back up a database and to createa certificate in the master database as follows:




WITH SUBJECT = ‘SQL Server Encryption’;


After this step you will have to create a databaseencryption key:

The following step is enabling Transparent Data Encryptionusing a SQL statement, as follows:

ALTER DATABASE  ExpertSqlEncryption



            As it can be seen in all these examples there are many waysin which you can secure your data.

It is very important to have in mind from all these are apart of any security strategy and that all the business applications mustinclude functionality to support database queries.

A successful SQL injection attack can affect the businessand it is mandatory to prevent it and, in the same time, to detect it. When youare willing to detect it you have to follow one important step, to make a listof all input fields whose value could be used in creating a SQL query.

All those who are interested in this subject know thatqueries are the primary data manipulation operations for the data layers. As itis obviously that they are important they should be optimized in order tooptimize database performance.


In order to run a secure database there are many movingfactors involved. The measures to attack your data as well as counter measuresto protect your data are changing every day. There is no time to sit andwatch.  What works today will be obsoleteas soon as a hacker breaks it.


Stephen P. Thomas is asuccessful author of Telerik Red Grid Control for ASP.NET AJAX by Example, asuccessful computer consultant of 20 years, a certified Microsoft Developer,and a software publisher of the Grytek Millionaire Order Processing software. Hestudied computer engineering at Carnegie-Mellon University and computer scienceat University of Maryland. His firm Grytek Consulting, Inc. has been successfulat completing many projects for government agencies and private companies. URLis