Re: encrypt database column
yes good point, thanks Dave In fact, yes i know we are both thinking 'if someone is good enough to hack into the backend database then they will be good enough to decrypt the data if they really wanted' so the cf app would definitely be sufficient enough The problem with encrypting data from within your application is that the same application will often also decrypt the data, so if your application itself has a vulnerability - which is by far the most likely security problem you'd have - this won't actually protect your data. It would, however, perhaps protect your data from untrustworthy database administrators, but that's probably not the threat profile you're facing. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321473 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: encrypt database column
Who will be able to open and read your database, and from which application? Some CF app? yes, a cf app that has alot of security. and only people that have been given rights to the secure data ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321440 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: encrypt database column
Who will be able to open and read your database, and from which application? Some CF app? hmm, i am now wondering whether there is really a need to encrypt the columns, but instead just to control the access to that data through the cf application. i suppose the thing we need to think about is if someone hacks into the database, we have personal data alongside other data, which the client doesnt want, so actually yes some form of encryption on the personal data is required thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321441 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: encrypt database column
i am now wondering whether there is really a need to encrypt the columns, but instead just to control the access to that data through the cf application. You are telling yourself exactly what I was thinking ;-) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321447 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: encrypt database column
yes, a cf app that has alot of security. and only people that have been given rights to the secure data Then what's the big idea to encrypt data, if they have the rights to see it anyway? If there are columns they should not see, just not provide them with the possibility to display those columns in your CF template, no? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321448 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: encrypt database column
:) thanks claude, yes it is a good point, but what if someone was to hack into the database... i'm sure we are both thinking the same thing at this point 'well if someone is good enough to hack into a database then they will be good enough to decrypt the data if they really wanted' but we have to satisfy the people that dont understand information systems that there is as much security as possible and they do want something to answer this question! (being, what can we do if someone does get into the database!) what do you think about this? thanks again for your feedback i am now wondering whether there is really a need to encrypt the columns, but instead just to control the access to that data through the cf application. You are telling yourself exactly what I was thinking ;-) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321452 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: encrypt database column
In fact, yes i know we are both thinking 'if someone is good enough to hack into the backend database then they will be good enough to decrypt the data if they really wanted' so the cf app would definitely be sufficient enough thanks for your help claude, very much appreciated :) :) thanks claude, yes it is a good point, but what if someone was to hack into the database... i'm sure we are both thinking the same thing at this point 'well if someone is good enough to hack into a database then they will be good enough to decrypt the data if they really wanted' but we have to satisfy the people that dont understand information systems that there is as much security as possible and they do want something to answer this question! (being, what can we do if someone does get into the database!) what do you think about this? thanks again for your feedback i am now wondering whether there is really a need to encrypt the columns, but instead just to control the access to that data through the cf application. You are telling yourself exactly what I was thinking ;-) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321454 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: encrypt database column
what do you think about this? First point: if your database is correctly stored in a safe area not accessible by HTTP, the risk is really low. If is is stored in an area vieable by HTTP, then so is your template to decrypt and view data any way. It's like hanging the key of your house on the entrance door ;-) Second point, if is was really a concern and an issue, there would be some tool available on your database system to handle it. Database systems provide with other protection schemes. The best option is to use them. Database systems are develop by professionals who know what they are doing, not by customers who think they know where the danger is. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321459 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: encrypt database column
2 excellent points claude thanks again for your help with this richard what do you think about this? First point: if your database is correctly stored in a safe area not accessible by HTTP, the risk is really low. If is is stored in an area vieable by HTTP, then so is your template to decrypt and view data any way. It's like hanging the key of your house on the entrance door ;-) Second point, if is was really a concern and an issue, there would be some tool available on your database system to handle it. Database systems provide with other protection schemes. The best option is to use them. Database systems are develop by professionals who know what they are doing, not by customers who think they know where the danger is. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321460 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: encrypt database column
In fact, yes i know we are both thinking 'if someone is good enough to hack into the backend database then they will be good enough to decrypt the data if they really wanted' so the cf app would definitely be sufficient enough The problem with encrypting data from within your application is that the same application will often also decrypt the data, so if your application itself has a vulnerability - which is by far the most likely security problem you'd have - this won't actually protect your data. It would, however, perhaps protect your data from untrustworthy database administrators, but that's probably not the threat profile you're facing. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321467 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: encrypt database column
thanks for the reply claude how would i get around the issue of encypting data in integer columns? - as some of the columns are set as smallint(2) for example. just thinking of the top of my head: when a user chooses to encrypt a column of data, the code could create a replica column which is varchar(255) and places all encrypted data in that column and removes it from the original column. if they choose to decrypt it then it puts all decrypted data back into the original column. what do you think? thanks 1) seeing as this can be run on any column (even integer fields) is it possible to encrypt an integer and then place the resulting encrypted string back into the integer column of the database If the encrypted data is also an integer, then yes, but if is can be any string, forget it. Not even woth trying. 2) as this function will run on any table/column how can i write an sql statement that just loops through the column encrypting the data? This depends on the database you're using. May be a stored procedure could do it. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321399 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: encrypt database column
how would i get around the issue of encypting data in integer columns? - as some of the columns are set as smallint(2) for example. You need to create an extra column to contain the encrypted value. I know no encrypting algorithm capable of encrypting a smallint into a smallint, except may be an equivalence table, but can this still be called encryption. what do you think? Well, frankly, to be honest, before I think anything about this, I'd like to know a little more about the idea behind all this. Why do you need to encrypt a column in the first time? May be there is a better solution. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321411 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: encrypt database column
we appreciate your feedback Who will be able to open and read your database, and from which application? Some CF app? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321417 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: encrypt database column
thanks again for your response basically the database contains a mixture of non-personal and personal data. our clients wishes to be able to select which columns are personal and are therefore encrypted (this is basically to satisfy data protection people) the initial idea was to create a separate database and when the user clicked to 'secure' a column then our code would remove the column and move it to a seperate database. this of course would not solve the security issues so we dont really see the need to do this, so thought if we could just encrypt the data within the column then it would satisfy the relevant bodies i think creating a separate column would prob be the best way, unless you can think of an alternative we appreciate your feedback how would i get around the issue of encypting data in integer columns? - as some of the columns are set as smallint(2) for example. You need to create an extra column to contain the encrypted value. I know no encrypting algorithm capable of encrypting a smallint into a smallint, except may be an equivalence table, but can this still be called encryption. what do you think? Well, frankly, to be honest, before I think anything about this, I'd like to know a little more about the idea behind all this. Why do you need to encrypt a column in the first time? May be there is a better solution. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321413 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: encrypt database column
1) seeing as this can be run on any column (even integer fields) is it possible to encrypt an integer and then place the resulting encrypted string back into the integer column of the database If the encrypted data is also an integer, then yes, but if is can be any string, forget it. Not even woth trying. 2) as this function will run on any table/column how can i write an sql statement that just loops through the column encrypting the data? This depends on the database you're using. May be a stored procedure could do it. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321327 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: encrypt database column
You can change the name of a column in TQL. In MSSQL there is a stored proc called sp_rename that would do it. For getting all fields from a column in a table, that is simply select colname from tablename. If you wanted to pass the encryptes string into the db, not sure that MSSQL or MYSQL can do. Why not use CF to process the encrypetd name and pass that in using cfprocparam in a cfstoredproc tag? Maybe I am missing the complexity of your objective. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321300 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: encrypt database column
Keep in mind that a column rename is very expensive and I believe it changes the underlying dbid of that column (which can cause schema caching issues). What's going on under the hood is: Add new column copy column data Delete old column plus any indexing or FK stuff needed. -Mark -Original Message- From: Tony Bentley [mailto:t...@tonybentley.com] Sent: Friday, April 03, 2009 3:11 PM To: cf-talk Subject: Re: encrypt database column You can change the name of a column in TQL. In MSSQL there is a stored proc called sp_rename that would do it. For getting all fields from a column in a table, that is simply select colname from tablename. If you wanted to pass the encryptes string into the db, not sure that MSSQL or MYSQL can do. Why not use CF to process the encrypetd name and pass that in using cfprocparam in a cfstoredproc tag? Maybe I am missing the complexity of your objective. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321303 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
