Function to encrypt hash(sha512, sha1, sha256, MD5)

[Anonymous]

Hi.

Enable a function to be able to insert an encrypted password to SQL Server (or other server) and when consulting in the database option to decrypt in the same way.

Example:

Insert into SQL Server

Set(password,hash('sha512',Input_Text.Text));

Query in SQL Server from PowerApps

If(hash('sha512',Input_Text.Text)=Row[Table_SQL],Notify('Correct',Success),Notify('Wrong',Error);

Regards.

Jesús Aguirre

[ValentinGasenko]

Guys, what do you think about this? 

https://github.com/DynamicSadFun/PowerAutomate-HashConnector

You can use this connector in PowerApps and encrypt anything you want.

[Lucian]

Hi @ValentinGasenko ,

Your idea might function, but it seems too much overhead for a simple user need.

I have found also another "workaround" - running an SQL Query against any available SQL server:

SELECT CONVERT(VARCHAR(32), HASHBYTES('MD5',CONVERT(NVARCHAR(32),'abcd')),2) as HashString; 

But this also could involve too much overhead as will need access to a premium connector.

So I hope Microsoft will implement something much easier in Power Automate or at least Power Apps.

Kind Regards,

Lucian

[ValentinGasenko]

Hi @Lucian ,

I have attached instructions in my repository. In the end, it all comes down to a banal institution of a custom connector. Even a junior analyst can handle it (I hope).
By the way, about the new features from Microsoft. Have you seen this?

https://docs.microsoft.com/en-us/connectors/custom-connectors/write-code

https://docs.microsoft.com/en-us/connectors/custom-connectors/define-blank#step-4-optional-use-custo...

Now it will be possible to add your own custom code to the connector, with the help of which, in particular, it will be possible to encrypt information.

[maksii]

@ValentinGasenko Nice hint for custom code approach.

Just checked for my custom connector. Generating a signature on a short url params adds only a second to the execution time of the action.

private async Task<HttpResponseMessage> HandleForwardOperation()
	{
		var signature = Hash("StringToHash");
		this.Context.Request.Headers.Add("signature", signature);
		// Use the context to forward/send an HTTP request
		HttpResponseMessage response = await this.Context.SendAsync(this.Context.Request, this.CancellationToken).ConfigureAwait(continueOnCapturedContext: false);
		return response;
	}

	static string Hash(string input)
	{
		using (SHA1Managed sha1 = new SHA1Managed())
		{
			var hash = sha1.ComputeHash(Encoding.UTF8.GetBytes(input));
			var sb = new StringBuilder(hash.Length * 2);

			foreach (byte b in hash)
			{
				sb.Append(b.ToString("X2"));
			}

			return sb.ToString();
		}
	}

[Lucian]

Hi @ValentinGasenko ,

Finally I have managed to test your solution, was not so easy for a beginner like me ;-), but the final result is different from the SQL HASHBYTES.

More exactly, the following SQL command:

SELECT CONVERT(VARCHAR(32), HASHBYTES('MD5',CONVERT(NVARCHAR(32),'abcd')),2) as HashString; 

would give as result:

E4A5FC25F9BBE59EC5606FB8FC093C75

But with your code, the same "abcd" message, would return:

e2fc714c4727ee9395f324cd2e7f331f

What could be wrong?

And another question, also for @maksii , using this custom code approach how the connector should be created (which would be the host, security, definition) and the azure function would be still needed or the code will run directly in the connector?

I have tried the samples from Microsoft/Hello World but I could not create a valid connector for this to work. Which action should be added to the connector?

A step by step example would be nice... 🙂

Kind Regards,

Lucian

[maksii]

@Lucian As per your first part, @ValentinGasenko consumes out of the box(.Net) cryptography logic for the generation of hash based on the most popular algorithms. He will need to make a small bit of adjustments to provide .net analogue of SQL hashstring functionality.
a quick search pointed me into a similar discussion sql server - TSQL md5 hash different to C# .NET md5 - Stack Overflow

As for connector:
Theoretically, you can create a dummy custom connector that will make get request to google.com with any definition. At Code section enable custom code and write up a small script based on Write code in a custom connector | Microsoft Docs Forwarding and transform script.
Inside of your code you will need to get a header or get a parameter(based on where you will place it) and return value back without calling and remote host at all.

No need to azure function or anything else. All logic will be inside of your connector. But keep in mind, that this functionality is still on Preview, and GA(global availability) is still unknown.

I'll take a look if can share with you a bit more complex and full example later this week.

[Lucian]

Hi @maksii ,

Thank you for your quick response, indeed that discussion points out the differences between .NET and SQL.

I have managed to get the line in @ValentinGasenko code:

md5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(text));

and replacing it with this one:

md5.ComputeHash(ASCIIEncoding.Unicode.GetBytes(text));

I could get the same result as SQL:

e4a5fc25f9bbe59ec5606fb8fc093c75

Thank you!

But I'll wait for when you'll have time for the "direct option" in "Write Code" to avoid the Azure Functions 😉

Kind Regards,

Lucian

[maksii]

@Lucian Small example that may help you with custom connector code
PowerAutomateCustomConnectors/CustomDummyHash at main · maksii/PowerAutomateCustomConnectors (github...

Just import the connector into your PowerAutomate, Add script from the repository, and configure your flow to consume it. You may need to extend flow definition and script a little bit to aim your goal.

[Lucian]

Wow... great workaround @maksii with this Write Code option - especially with that "dummy host".

Also nice short article - (1) PowerAutomate Dummy Custom Code Connector | LinkedIn 👍

It really works!

@ValentinGasenko it would even greater if you could add to your initial repository sample, instruction to use "write code" only option as replacement to Azure Functions that require an Azure subscription that is not always at users hand. 😉

Thank you!

[ValentinGasenko]

Hi @Lucian ,

Of course, I'll add it. This feature was released by Microsoft later than I wrote the azure function. @maksii and I investigated it and realized that it is very promising)