We've currently developed a way of encrypting data that is submitted via an Umbraco Form by using a Custom Workflow and by modifying the umbraco.forms.js file in the App_Plugins folder to decrypt it again.
This all works great and all, however I was wondering if it's possible to override the functions in umbraco.forms.js so that we don't have to modify a core umbraco file.
The data that is submitted when you complete an Umbraco Form is stored in the database in an unencrypted format - this is not good, as a large majority of contact forms ask for personal data. SSL is fine for the transaction, but the database is still in plain text, which means if ever the site was hacked, we face the potential of exposing users details.
By using a custom workflow, we are encrypting the data directly in the database, and in order to make it readable again in the back-office we have modified the file umbraco.form.js, this is not ideal as it means everytime Umbraco Forms is upgraded we would have to manually add our decrypter code back in.
I just wondered if there was a way to override the functions in this file so it can sit along side it rather than replace it.
You make a good point, but I suppose something is better than nothing. As for SQL server, that feature is only for 2016, which a lot of our clients don't use.
Do you know if there are plans to bring encryption to Umbraco Forms?
If the site was hacked then couldn't they just go into Umbraco and export all the data via the Forms interface? Doesn't matter what encryption you use to store the data, if there's an interface to export it unencrypted.
Currently, member and user data is also susceptible to this problem.
If we were to tackle this problem, we'd really need to do it over all the data (what if it's an intranet, with super secret data for example!). Which again would lead us to consider SQL server encryption, it could be great to have this available for people on SQL 2016 SP1+.
For now we encourage firewalling and setting up SQL as a separate instance (ie: not on the same server as the webserver).
thanks for the raising this topic and actually I've been looking into this lately. I would love to see this feature as an option in the member and forms-section. I would love to see that you could check per property or per form field if it should be stored encrypted or not.
The idea behind storing it encrypted is that if someone could get their hands on the database it still isn't readable, or if someone could do SQL Injection into your website it still can only get encrypted data out of the database.
Security is all about layered security, and this just is an extra layer of defense which makes it harder for the attacker to retrieve that what they want.
Also with the new data-storage / privacy legislations on their way (General Protection Data Regulation; GDPR; starting in May 2018) this would be highly benificial if we would have an option to do this.
The basic idea is to encrypt if when it's stored, and decrypt it when it's retrieved in the backoffice.
@Sebastiaan: Any idea if there's a way to do it or maybe some points that we could hook into?
If possible we could create a package and it doesn't have to be in the core of Umbraco. But we would need the extension points.
I think this raises some excellent points, though I have nothing to provide at the moment that you could use unless you wrap all default property editors in a "decrypting property editor". This would be horrible for performance as you'd need to ask the server for each property: hey, please decrypt this data.
So we'd either need to build this in natively or.. make sure that Umbraco can work with SQL server encryption, where all this is done transparently.
Unless there's something I'm missing, I don't think we should repeat the work the SQL server team has already done.
Thanks for all the comments, while it's unfortunate to hear there is no real way of fixing this now. I hope this is considered in future builds. I appreciate that the latest version of SQL has a lot of good encryption features, it doesn't really help those who can't make the move to SQL 2016.
The only way to protect sites at the moment, is to not save those details in the database or as Jeffery suggested, create your own secure forms outside of Umbraco Forms.
In this day and age, it would be absolute folly not to make any personal data secure in the Umbraco CMS. I hope this is considered sooner rather than later.
Right Clicked on the dbo.umbracoUser table and choose Encrypt Columns...
Followed the wizard and chose userName, userogin, user Password, userEmail and set them to Encryption Type Deterministic (while Auto Creating the Key)
The wizard ends by encrypting the default admin details.
Then I update the web.config connection string to include the setting: - Column Encryption Setting=enabled
I've also made sure that the site was set to Target Framework 4.6.2
Now when I try and login, it tells me the password has failed.
I've checked the Log file and it shows this error:-
System.Data.SqlClient.SqlException (0x80131904): Operand type clash: nvarchar(125) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'umbraco-cms') is incompatible with varchar
Statement(s) could not be prepared.
Now I don't know if this is Umbraco or SQL Server that has the issue - hopefully it's SQL server.
Also to add why this is important for us, with the introduction of GDPR legislation (May 2018), if this stored personal data is not encrypted at rest. Us developers could be looking at fines of up to 4% of our annual turnover.
It's really important that any personal data be encrypted and I would have thought given this impending EU law it would be the first thing on the dev teams todo list.
For what it's worth: I don't think the umbracoUser table is even subject to GDPR - since it's made to protect consumer's data, not the people that will be editing your website because it's their job.
One wider point around GDPR and EU equivalents - as a web development agency we are seeing this pop up more and more in our contracts with larger organisations. To comply we have to have procedures in place to protect personal information - not just in production but during development too.
What scares me more than hackers on production sites is a developer's laptop left on a train, or a misplaced USB stick containing an old backup of a customer's data. Controlling copies of data is harder and more involved than database options in SQL Server.
I think GDPR is going to cause all of our customers to start asking harder questions about how data is managed, and it would be good to make it easier for Umbraco to support best practices.
I've managed to encrypt data in the database and read it back in Umbraco with out issue. I've currently encrypted the key column in cmsDictionary as a test.
However, when I attempt to add data i.e create a new dictionary item to said encrypted column. I'm currently getting this error:-
Operand type clash: nvarchar(4000) encrypted with (encryptiontype = 'DETERMINISTIC', encryptionalgorithmname = 'AEADAES256CBCHMACSHA256', columnencryptionkeyname = 'UmbCEK', columnencryptionkeydatabasename = 'umbraco-cms') is incompatible with nvarchar(1000) encrypted with (encryptiontype = 'DETERMINISTIC', encryptionalgorithmname = 'AEADAES256CBCHMACSHA256', columnencryptionkeyname = 'UmbCEK', columnencryptionkeydatabasename = 'umbraco-cms') Statement(s) could not be prepared.
Points to whoever can help, this is driving me insane.
I believe this happens when the driver being used to insert the data isn't compatible with encryption - you need NET 4.6 installed and for the underlying code to be using .NET Framework Data Provider for SQL Server.
Thanks for the response, we're already using .net Framework 4.6.2. I changed the Target Framework in Visual Studio, re-published and updated the config. Have I missed anything? The App_Pool is also set to Framework 4
Thank you again for your reply and for your suggestions. I've just installed the latest System.Data.SqlClient and no joy I'm afraid.
I think you might be right, it certainly seems like something deep in the Umbraco code might be throwing a spanner in the works - I hope I'm wrong though.
Transparent Data Encryption (TDE) is the primary SQL Server encryption option. It was first available in SQL Server 2008, and as with the SQL Server 2012 release, it's available only in the SQL Server Enterprise edition, not in the Business Intelligence, Standard, or Express editions. TDE enables you to encrypt an entire database. Backups for databases that use TDE are also encrypted. TDE protects the data at rest, which means that the database’s data and log files are encrypted using the AES and 3DES encryption algorithms. TDE is completely transparent to the application and requires no coding changes to implement.
It's not cheap though. You need to spend a few thousand dollars on enterprise edition.
Most of our clients tend to use Web or Standard Edition of SQL Server -
certainly don't have Enterprise Edition money :) . My preference would be to have the encryption in the website code rather than rely on SQL Server, but our options are limited.
I've been speaking to the nice folks at Azure Support and it's starting to look like the reason why Always Encrypted does not work with Umbraco is because the application insert parameters don't match up to what in the database.
For example as a test, I tried to encrypt a value in the table dbo.cmsDictionary, specifically the Key column.
I went through the encryption wizard in SSMS and all encrypted fine - sounding good so far.
However in the Umbraco Back Office if you attempt to create a new Dictionary Item it will fail with an error such as this:
Operand type clash: nvarchar is incompatible with nvarchar(4000) encrypted with (encryptiontype = 'DETERMINISTIC', encryptionalgorithmname = 'AEADAES256CBCHMACSHA256', columnencryptionkeyname = 'CEKAuto1', columnencryptionkeydatabase_name = 'umbraco-cms')
Now so far Microsoft and I have determined that the reason this error is occuring is because the column in the database is of type nvarchar(1000) however it seems (and I cant prove this yet) that the insert statement for adding dictionary items is actually nvarchar(4000). SQL does not like this difference.
So as a test I changed the database column to nvarchar(4000) and hey presto it worked.
I didn't really want to encrypt the dictionary table but it was a simple one to try. I also tried the dbo.UFRecordDataString table, which is one of the tables I want to encrypt and got the same error as before.
At this point, and I'm still hoping Microsoft will pull something out of the bag on this one. I believe the only way this 'Always Encrypted' option is going to work is if the Umbraco dev team review their CRUD operations to make sure the lengths match up to what they have in the SQL Database tables.
I'll let you know if Microsoft find something I can use.
We're currently reviewing the impact that GDPR will have on the data that has been stored on our customer sites so I'm unsure how far the rabbit hole goes on this, but as mentioned above what about Umbraco members? Significant amount of personal data can be stored there and these could contain email marketing preferences? Would the username have to be encrypted given it's an email address?
Great to see that Paul is trying this out, but does anyone know if there are any definite plans to bring in a level of encryption into Umbraco in a future version? 7.8 maybe? Given that GDPR comes into affect in May next year, we need a road map in place so if we need to upgrade clients to a secure version, we can start engaging with them to plan this or looking at bringing in our own functionality.
Could the Umbraco team provide us with their exact intentions on this subject. It would be great to hear straight from you guys. As a lot of people are concerned about this. If you guys have a road map / time table for encryption it would be great to see it.
They are currently getting legal advice on what is best for them to implement both within Forms and the CMS as a whole. I image they will be able to give us a better road map once this process is complete.
It's an old thread, but I'm wondering if there is any update on this issue. I'm about to use Azure for hosting an Umbraco instance (Umbraco cloud was unfortunately not an option) and the client is asking if we encrypt the database. I would like to use SQL always encrypted, but does it work with Umbraco these days? I can't seem to find much more about this subject besided a bug report for UMbraco 7.6 and two forum threads...
Umbraco Form Encryption
Hello,
We've currently developed a way of encrypting data that is submitted via an Umbraco Form by using a Custom Workflow and by modifying the umbraco.forms.js file in the App_Plugins folder to decrypt it again.
This all works great and all, however I was wondering if it's possible to override the functions in umbraco.forms.js so that we don't have to modify a core umbraco file.
Does anyone know a way of doing this?
Thanks
Paul
Maybe I'm misunderstanding the purpose but I think the only way to "encrypt" forms is by running your site on https.
What is your requirement / need here exactly?
Hi Sebastiaan,
The data that is submitted when you complete an Umbraco Form is stored in the database in an unencrypted format - this is not good, as a large majority of contact forms ask for personal data. SSL is fine for the transaction, but the database is still in plain text, which means if ever the site was hacked, we face the potential of exposing users details.
By using a custom workflow, we are encrypting the data directly in the database, and in order to make it readable again in the back-office we have modified the file umbraco.form.js, this is not ideal as it means everytime Umbraco Forms is upgraded we would have to manually add our decrypter code back in.
I just wondered if there was a way to override the functions in this file so it can sit along side it rather than replace it.
Hope this makes sense.
Thanks
Paul
No there isn't a way currently.
But if ever the site was hacked wouldn't it be trivially easy to look at the javascript to learn how to decrypt those fields?
It feels like the best protection (and most transparent, without having to alter javascript files) you could get in this case is to use SQL Server encryption - https://www.simple-talk.com/sql/database-administration/sql-server-encryption-always-encrypted/
This also protects your backups from being restored on a different machine, if your backups ever get breached.
You make a good point, but I suppose something is better than nothing. As for SQL server, that feature is only for 2016, which a lot of our clients don't use.
Do you know if there are plans to bring encryption to Umbraco Forms?
Thanks
Paul
If the site was hacked then couldn't they just go into Umbraco and export all the data via the Forms interface? Doesn't matter what encryption you use to store the data, if there's an interface to export it unencrypted.
Really depends on the entry point of the hack :-)
If someone were able to get hold of web.config but nothing else, they could dump the database and not get sensitive info in this case, for example.
Then again, it could be possible to update the database with a user account with a pre-hashed password and get into the backoffice that way.
No plans.
Currently, member and user data is also susceptible to this problem.
If we were to tackle this problem, we'd really need to do it over all the data (what if it's an intranet, with super secret data for example!). Which again would lead us to consider SQL server encryption, it could be great to have this available for people on SQL 2016 SP1+.
For now we encourage firewalling and setting up SQL as a separate instance (ie: not on the same server as the webserver).
Hi Paul,
thanks for the raising this topic and actually I've been looking into this lately. I would love to see this feature as an option in the member and forms-section. I would love to see that you could check per property or per form field if it should be stored encrypted or not.
The idea behind storing it encrypted is that if someone could get their hands on the database it still isn't readable, or if someone could do SQL Injection into your website it still can only get encrypted data out of the database.
Security is all about layered security, and this just is an extra layer of defense which makes it harder for the attacker to retrieve that what they want.
Also with the new data-storage / privacy legislations on their way (General Protection Data Regulation; GDPR; starting in May 2018) this would be highly benificial if we would have an option to do this.
The basic idea is to encrypt if when it's stored, and decrypt it when it's retrieved in the backoffice.
@Sebastiaan: Any idea if there's a way to do it or maybe some points that we could hook into?
If possible we could create a package and it doesn't have to be in the core of Umbraco. But we would need the extension points.
Regards, Jeffrey
I think this raises some excellent points, though I have nothing to provide at the moment that you could use unless you wrap all default property editors in a "decrypting property editor". This would be horrible for performance as you'd need to ask the server for each property: hey, please decrypt this data.
So we'd either need to build this in natively or.. make sure that Umbraco can work with SQL server encryption, where all this is done transparently.
Unless there's something I'm missing, I don't think we should repeat the work the SQL server team has already done.
Thanks for all the comments, while it's unfortunate to hear there is no real way of fixing this now. I hope this is considered in future builds. I appreciate that the latest version of SQL has a lot of good encryption features, it doesn't really help those who can't make the move to SQL 2016.
The only way to protect sites at the moment, is to not save those details in the database or as Jeffery suggested, create your own secure forms outside of Umbraco Forms.
In this day and age, it would be absolute folly not to make any personal data secure in the Umbraco CMS. I hope this is considered sooner rather than later.
Thanks again
Paul
Hi Sebastiaan,
I've taken your advice and deployed an Umbraco instance on to SQL Server 2016.
I've enabled encryption, on the user table and now I can't login. Just wondered if you've tried the new encryption?
Thanks
Paul
I haven't. Let me know what problems there are and let's see how we can solve them!
Sure, so basically what I've done so far is:-
Now when I try and login, it tells me the password has failed.
I've checked the Log file and it shows this error:-
Now I don't know if this is Umbraco or SQL Server that has the issue - hopefully it's SQL server.
Hope this is enough information to help.
Thanks
Paul
Have you Googled the error? From what I can tell it might have something to do with the SQL driver you're using.
We're not currently actively working on this (there's still some time until May) so if you're investigating this then that's a great help!
What I don't understand from this error message is why it's mentioning both nvarchar(125) and varchar?
Also to add why this is important for us, with the introduction of GDPR legislation (May 2018), if this stored personal data is not encrypted at rest. Us developers could be looking at fines of up to 4% of our annual turnover.
For those not aware - please read: Wikipedia: GDPR
It's really important that any personal data be encrypted and I would have thought given this impending EU law it would be the first thing on the dev teams todo list.
Cheers
Paul
For what it's worth: I don't think the
umbracoUsertable is even subject to GDPR - since it's made to protect consumer's data, not the people that will be editing your website because it's their job.Well umbracoUser table was just an example - it's more so for Umbraco Forms that I'm really interested in encrypting.
Thanks
Paul
One wider point around GDPR and EU equivalents - as a web development agency we are seeing this pop up more and more in our contracts with larger organisations. To comply we have to have procedures in place to protect personal information - not just in production but during development too.
What scares me more than hackers on production sites is a developer's laptop left on a train, or a misplaced USB stick containing an old backup of a customer's data. Controlling copies of data is harder and more involved than database options in SQL Server.
I think GDPR is going to cause all of our customers to start asking harder questions about how data is managed, and it would be good to make it easier for Umbraco to support best practices.
OK, slight update on the encryption front.
I've managed to encrypt data in the database and read it back in Umbraco with out issue. I've currently encrypted the key column in cmsDictionary as a test.
However, when I attempt to add data i.e create a new dictionary item to said encrypted column. I'm currently getting this error:-
Points to whoever can help, this is driving me insane.
Cheers
Paul
I believe this happens when the driver being used to insert the data isn't compatible with encryption - you need NET 4.6 installed and for the underlying code to be using .NET Framework Data Provider for SQL Server.
See https://blobeater.blog/2016/11/04/always-encrypted-column-encryption/
Hi Dan,
Thanks for the response, we're already using .net Framework 4.6.2. I changed the Target Framework in Visual Studio, re-published and updated the config. Have I missed anything? The App_Pool is also set to Framework 4
Thanks
Paul
Hmmm, seems you have done everything. I'm just going off what I've read; I've no practical experience.
A few guesses - maybe Umbraco is compiled against 4.5 and somehow picks up on that version? Or is it something to do with PetaPoco?
Could you install latest System.Data.SqlClient package via NuGet?
https://www.nuget.org/packages/System.Data.SqlClient/
(I'd definitely back everything up before trying that!).
Hi Dan,
Thank you again for your reply and for your suggestions. I've just installed the latest System.Data.SqlClient and no joy I'm afraid.
I think you might be right, it certainly seems like something deep in the Umbraco code might be throwing a spanner in the works - I hope I'm wrong though.
Thanks
Paul
I'm not sure why people seem to think encryption is only available for SQL Server 2016. It's available for older versions too: http://sqlmag.com/database-security/sql-server-encryption-options
It's not cheap though. You need to spend a few thousand dollars on enterprise edition.
Most of our clients tend to use Web or Standard Edition of SQL Server - certainly don't have Enterprise Edition money :) . My preference would be to have the encryption in the website code rather than rely on SQL Server, but our options are limited.
OK, so just an update on my progress here.
I've been speaking to the nice folks at Azure Support and it's starting to look like the reason why Always Encrypted does not work with Umbraco is because the application insert parameters don't match up to what in the database.
For example as a test, I tried to encrypt a value in the table dbo.cmsDictionary, specifically the Key column.
I went through the encryption wizard in SSMS and all encrypted fine - sounding good so far.
However in the Umbraco Back Office if you attempt to create a new Dictionary Item it will fail with an error such as this:
Operand type clash: nvarchar is incompatible with nvarchar(4000) encrypted with (encryptiontype = 'DETERMINISTIC', encryptionalgorithmname = 'AEADAES256CBCHMACSHA256', columnencryptionkeyname = 'CEKAuto1', columnencryptionkeydatabase_name = 'umbraco-cms')
Now so far Microsoft and I have determined that the reason this error is occuring is because the column in the database is of type nvarchar(1000) however it seems (and I cant prove this yet) that the insert statement for adding dictionary items is actually nvarchar(4000). SQL does not like this difference.
So as a test I changed the database column to nvarchar(4000) and hey presto it worked.
I didn't really want to encrypt the dictionary table but it was a simple one to try. I also tried the dbo.UFRecordDataString table, which is one of the tables I want to encrypt and got the same error as before.
At this point, and I'm still hoping Microsoft will pull something out of the bag on this one. I believe the only way this 'Always Encrypted' option is going to work is if the Umbraco dev team review their CRUD operations to make sure the lengths match up to what they have in the SQL Database tables.
I'll let you know if Microsoft find something I can use.
Thanks
Paul
Apologies in advance for creating a duplicate thread on this (over here if anyone's interested).
We're currently reviewing the impact that GDPR will have on the data that has been stored on our customer sites so I'm unsure how far the rabbit hole goes on this, but as mentioned above what about Umbraco members? Significant amount of personal data can be stored there and these could contain email marketing preferences? Would the username have to be encrypted given it's an email address?
Great to see that Paul is trying this out, but does anyone know if there are any definite plans to bring in a level of encryption into Umbraco in a future version? 7.8 maybe? Given that GDPR comes into affect in May next year, we need a road map in place so if we need to upgrade clients to a secure version, we can start engaging with them to plan this or looking at bringing in our own functionality.
Hi Simon,
Thanks for joining the thread :). I think encryption is in the works. I just hope its not too little too late.
While the law come into effect May 2018, ideally I don't want to be waiting that long before we see some significant in roads into data security.
Just because it's not the law now, shouldn't mean it's ignored until May.
Hopefully the Umbraco devs will priortise this as it's a pretty big concern.
Thanks
Paul
Could the Umbraco team provide us with their exact intentions on this subject. It would be great to hear straight from you guys. As a lot of people are concerned about this. If you guys have a road map / time table for encryption it would be great to see it.
Thanks
Paul
Hi Paul,
The HQ is aware of this concern, they posted an update here: https://our.umbraco.org/forum/umbraco-forms/87156-gdpr-and-umbraco
They are currently getting legal advice on what is best for them to implement both within Forms and the CMS as a whole. I image they will be able to give us a better road map once this process is complete.
Cheers,
Nik
Thanks for the update Nik, much appreciated.
It's an old thread, but I'm wondering if there is any update on this issue. I'm about to use Azure for hosting an Umbraco instance (Umbraco cloud was unfortunately not an option) and the client is asking if we encrypt the database. I would like to use SQL always encrypted, but does it work with Umbraco these days? I can't seem to find much more about this subject besided a bug report for UMbraco 7.6 and two forum threads...
Did you figure out this answer?
is working on a reply...