Encoding/Decoding Hash in Kentico SQL database

Douglas Fittipaldi asked on July 14, 2022 23:20

Hello:

I'm using a roundabout way to create pages in Kentico Xperience without use of the REST API (there's a reason but it's too long to go into here). I think there's one thing that's getting in my way, which is the CMS_PageUrlPath. In that table there are two fields, PageUrlPathUrlPath and PageUrlPathUrlPathHash, where I'm guessing the value in the former is encrypted into the value in the latter. For example, in PageUrlPathUrlPath there is a value of "Home/BlankTestPage1" and in PageUrlPathUrlPathHash there is a value of "A432B45F147F2FB6F0B61C056727EF7703CC2FCFAC680A758E88D4010FED7B1E". I've tried different types of encoding but they all give me different values, so what is the formula that Kentico uses to change one value to the other? Thanks in advance.

Correct Answer

Brenden Kehren answered on July 15, 2022 15:42

This dives pretty deep into the Kentico database stored procedures. Assuming you have direct database access, check out the stored proc Proc_CMS_PageUrlPath_UpdateUrlPaths. This has the code and explanation of what you're looking for and possibly what you need to do in your SQL or C# code.

The short story is this particular SQL statement is creating the hash value from the newly input URL:

CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', LOWER(@NewUrlPath)), 2)

2 votesVote for this answer Unmark Correct answer

Recent Answers

Douglas Fittipaldi answered on July 15, 2022 19:13

Thanks Brenden, this gave me what I was looking for. Much appreciated.

For those that are interested, one "gotcha" I ran into is when I first tried the example I typed this:

CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', LOWER('Home/BlankTestPage1')), 2)

This gave me a different result from what was in the database. This is because 'Home/BlankTestPage1' is a VARCHAR. What you need to use is NVARCHAR, like so:

declare @NewUrlPath as NVARCHAR(2000) = 'Home/BlankTestPage1'
select CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', LOWER(@NewUrlPath)), 2)

0 votesVote for this answer Mark as a Correct answer