Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

SFMC TIP | How to use MD5 in SQL and AMPScript

Salesforce Marketing Cloud Tips

Message Digest 5 (MD5) is a hash function used for data integrity, but it is not suitable for security-critical applications. It has found use for various applications, one of which is to create an encrypted contact subscriber key. MD5 takes any string and produces a fixed-size (128-bit) hash value, that cannot be easily decrypted.

To generate MD5 hash with MSSQL we can use following SQL select clause

CONVERT(VARCHAR(32), HashBytes('MD5', LTRIM(RTRIM(Lower([EmailAddress])))), 2) as SubscriberKey
  1. HashBytes('MD5', LTRIM(RTRIM(Lower([EmailAddress])))):
    • Lower([EmailAddress]): This function converts the value in the [EmailAddress] field to lowercase. It ensures that the hash calculation is case-insensitive.
    • LTRIM(RTRIM()): These functions are used to remove leading and trailing spaces from the lowercase email address. Trimming is commonly done to ensure consistent results when hashing.
    • HashBytes('MD5', ...): This function calculates the MD5 hash of the processed email address, producing a binary output.
  2. CONVERT(VARCHAR(32), ..., 2) as SubscriberKey:
    • CONVERT(VARCHAR(32), ..., 2): This part converts the binary MD5 hash result into a hexadecimal string representation of length 32 characters.
    • as SubscriberKey: This gives the resulting hexadecimal string the alias “SubscriberKey” for use in the query’s output.

In MSSQL Server, NVARCHAR and NCHAR data types store Unicode characters, and by default, they use UTF-16 encoding. So, when you convert binary data (like an MD5 hash) to a VARCHAR in SQL Server without specifying a character encoding, it typically uses the database’s default encoding, which is UTF-16 for Unicode data types.

If you intend to hash strings in SSJS or AMPScript to match the results obtained from SQL Server (which uses UTF-16 encoding by default), you should ensure that your string encoding matches. This means that you should normalize and encode your strings in UTF-16 before hashing to achieve consistent results.

Thus in AMPScript and SSJS we will have to use UTF-16 character set for MD5 hashing.

%%[
/*
MD5(1,2)
1 - string to hash
3 - character set. Default is UTF-8
*/
SET @emailAddress = "exmample@martechnotes.com"
SET @MD5EmailAddress  = MD5(@emailAddress, "UTF-16")
]%%
<script runat=server>
  /*
  MD5(1,2)
  1 - string to hash
  3 - character set. Default is UTF-8
  */
   var email = Platform.Variable.GetValue("@emailAddress");
   Platform.Function.MD5(email, 'UTF-16');
</script>

Oh hi there 👋
I have a FREE e-book for you.

Sign up now to get an in-depth analysis of Adobe and Salesforce Marketing Clouds!

We don’t spam! Read our privacy policy for more info.

#AMPScript #JavaScript #programming #SQL #ssjs
Marcel Szimonisz
Marcel Szimonisz
MarTech consultant As a marketing automation consultant, I specialize in solving problems, automating processes, and driving innovation in my clients' marketing platforms.

I hold certifications in Adobe Campaign v6 (3x certified) and Salesforce Marketing Cloud (5x certified).

Additionally, I serve as a community advisor for Adobe Campaign, offering expert insights and guidance.

In addition to my professional pursuits, I also enjoy exploring various programming languages, CMSs, and frameworks, further enhancing my technical expertise and staying at the forefront of industry advancements.
Take a look at our subscription offering in case you are looking for a Marketing Automation Consultant.

Leave a comment

Your email address will not be published. Required fields are marked *

Similar posts that you may find useful

How to leverage queryDef in Adobe Campaign
Adobe Campaign, Marketing Automation

ACC | How to Leverage queryDef in Adobe Campaign

7 minutes read

When it comes to programatically selecting records from the database in Adobe Campaign Classic, queryDef emerges as a crucial tool. As a static SOAP method extensively employed in JavaScript, particularly within workflows or web apps, queryDef offers unparalleled capabilities. However, what many may not realize is that there are three distinct implementations of this function. … Read more

Continue reading
Adobe campaign tips and tricks
ACC Tips & Tricks, Adobe Campaign

ACC TIP | How To Convert Base64 to PDF attachment

1 minute read

In this article, I will provide a simple trick for converting Base64 encoded data to PDF using JSAPI in Adobe Campaign Classic. Due to AC’s inability to attach Base64-encoded files directly to emails, this method can prove to be highly useful. Base64 data format that would be easy to use when supperted by Adobe Campaign … Read more

Continue reading
SFMC tips and tricks
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

SFMC TIP | Invalid links in HTML

1 minute read

When the HTML template is used (loading your own HTML code) all the links that use query parameters (?a=b) will resolve to invalid links when they redirect upon user click. This happens when web analytics UTM tags are being attached to the delivery. To resolve this issue all the links with additional query parameters has … Read more

Continue reading
How to use Adobe Campaign Classic Incremental Query
Adobe Campaign, Marketing Automation

ACC Activity | How to use incremental query

2 minutes read

Adobe Campaign Classic provides a range of activities that allow you to query data from your database. Today we will take a look at the incremental query, which allows you to periodically select a target based on a specific criteria, while excluding individuals who have already been already targeted. The population that has already been … Read more

Continue reading
Adobe campaign tips and tricks
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

ACC TIP | Export connection strings

less than a minute read

Whether you change your laptop or want to save actual configuration for your colleagues. You can easily achieve that by taking all connection strings as XML. ims – connection over adobe id SSO Also similar approach is to directly change the connection string file nlclient_cnx.xml that is located under your local AppData folder. To quickly … Read more

Continue reading