Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 20 total |
Security hole in SQL |
Mon, Mar 5 2007 12:12 AM | Permanent Link |
Dave Harrison | This may have been discussed before, but to me it's quite serious and I
can't use SQL because of it. I need to have some of my tables encrypted and I thought DBISAM did a pretty good job. That dream was shattered when I discovered the canned result set from SQL queries on an encrypted table are sitting in the private directory without any encryption. I was able to view the contents of these temporary files with a hex viewer. The data that I thought was encrypted, is there in plain site. The specifications for my project requires the data remain encrypted and not be accessible without the proper password. My boss may have a heart attack if he knew we were shipping data to clients that is in the clear. Unfortunately live queries face the same problem. If I delete the .idx file, and run the application, DBISAM will build an empty index file and allow the program to run. This is great, but now any SQL with an Order By clause will be a canned query and you guessed it, the unencrypted copy is now in the temp directory. My question is, why can't these canned queries be encrypted with the same password if the original tables were encrypted? Why strip away encryption on all canned queries? Even canned result sets from table joins should remain encrypted if one or more of the tables were encrypted. Use the same password as one of the encrypted tables, it doesn't matter which one, as long as the data remains secure. Can I assume your TnxTables's don't have this problem? Dave |
Mon, Mar 5 2007 8:05 AM | Permanent Link |
"Robert" | "Dave Harrison" <daveh_18824@spammore.com> wrote in message news:21A481CB-0F4B-4CD3-B433-9DE3D5292ED5@news.elevatesoft.com... > This may have been discussed before, but to me it's quite serious and I > can't use SQL because of it. > > I need to have some of my tables encrypted and I thought DBISAM did a > pretty good job. That dream was shattered when I discovered the canned > result set from SQL queries on an encrypted table are sitting in the > private directory without any encryption. I was able to view the contents > of these temporary files with a hex viewer. The data that I thought was > encrypted, is there in plain site. The specifications for my project > requires the data remain encrypted and not be accessible without the > proper password. My boss may have a heart attack if he knew we were > shipping data to clients that is in the clear. > First of all, is it really that big of a problem if your boss has a heart attack? You might get his job. Well, just some free career advice, anyway. Seriously now, interesting problem. Obviously one performs a query to eventually show unencrypted data to the user, in the form of a report or a dbgrid. When you say you are "shipping data to clients", are you talking about actually sending a dataset to another computer, or presenting the results of the query? If you go thru the process (more or less) when printing a report 1. Parse and prepare query 2. Read tables (whether or not encrypted), store results in memory or in temporary files 3. Prepare the final output dataset (which again could be a temporary disk file) 4. Format each row of the resulting dataset, and send to the printer At which point should the data be unencrypted? Remember thet you can "select into" a table, and that table can be encrypted. I don't really see a solution to the temporary datasets, other than making the temp folder available only to this user. Robert |
Mon, Mar 5 2007 9:03 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Robert
The obvious alternative is to use memory tables. Cleaned up when the app closes and unlikely to be looked at by someone with a hex editor. Still obtainable if you work at it but so is everything. Roy Lambert |
Mon, Mar 5 2007 9:22 AM | Permanent Link |
"Robert" | "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:AAB1741C-C020-4417-B108-AD229ADEC35C@news.elevatesoft.com... > Robert > > The obvious alternative is to use memory tables. Cleaned up when the app > closes and unlikely to be looked at by someone with a hex editor. Still > obtainable if you work at it but so is everything. > The problem is that more and more customers are requiring that you specifically tell them of any potential areas of exposure. Example: say I run a large query, getting all kinds of encrypted data, which I know creates a temporary dataset. Then I kill the application, the dataset remains, unencrypted, in the temp folder. Then I FTP the dataset to the competition, who downloads DBSYS for free and has a field day with my customer's data. One of those that comes up during acceptance testing is enough to kill a sale. The fact that DBISAM is "non standard" (read, non Microsoft or Oracle) makes it worse. I don't know what the answer is, but I see the potential problem. Robert |
Mon, Mar 5 2007 9:54 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< I need to have some of my tables encrypted and I thought DBISAM did a pretty good job. That dream was shattered when I discovered the canned result set from SQL queries on an encrypted table are sitting in the private directory without any encryption. >> Use the ENCRYPTED WITH clause: http://www.elevatesoft.com/dbisam4d7_select_statement.htm << My question is, why can't these canned queries be encrypted with the same password if the original tables were encrypted? Why strip away encryption on all canned queries? >> a) Not everyone wants the result sets to be encrypted by default. Some developers only want the long-standing tables on disk encrypted. b) There is the possibility that a query could contain a join between an encrypted table and a non-encrypted table. Which do we use to determine whether to use encryption or not ? c) The database server doesn't have this issue since the canned result sets are usually never accessible by clients directly. << Even canned result sets from table joins should remain encrypted if one or more of the tables were encrypted. Use the same password as one of the encrypted tables, it doesn't matter which one, as long as the data remains secure. >> We can't just arbitrarily pick an encryption password from one of the tables. In the case of the INTO clause, the developer needs to know that the result set is encrypted with a specific password that can be used to open it afterwards without having to try and guess which one was used. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Mar 5 2007 10:46 AM | Permanent Link |
Dave Harrison | Robert wrote:
> "Dave Harrison" <daveh_18824@spammore.com> wrote in message > news:21A481CB-0F4B-4CD3-B433-9DE3D5292ED5@news.elevatesoft.com... > >>This may have been discussed before, but to me it's quite serious and I >>can't use SQL because of it. >> >>I need to have some of my tables encrypted and I thought DBISAM did a >>pretty good job. That dream was shattered when I discovered the canned >>result set from SQL queries on an encrypted table are sitting in the >>private directory without any encryption. I was able to view the contents >>of these temporary files with a hex viewer. The data that I thought was >>encrypted, is there in plain site. The specifications for my project >>requires the data remain encrypted and not be accessible without the >>proper password. My boss may have a heart attack if he knew we were >>shipping data to clients that is in the clear. >> > > > First of all, is it really that big of a problem if your boss has a heart > attack? You might get his job. Yeah, I hadn't thought of that. I'll spring it on him during the monthly meeting with the client. > > Seriously now, interesting problem. Obviously one performs a query to > eventually show unencrypted data to the user, in the form of a report or a > dbgrid. When you say you are "shipping data to clients", are you talking > about actually sending a dataset to another computer, or presenting the > results of the query? We don't show all the data from the table to the user. There are columns that are retrieved that contain proprietary data and scripts. There is also registration info, internal passwords and checksums that we don't want the user to see. There is also encrypted data that is processed (number crunched) before the user see the summarized data. I also see a problem if companies charge for their data and the user gets the data for free if he figures out where the temp data is stored. > > If you go thru the process (more or less) when printing a report > > 1. Parse and prepare query > 2. Read tables (whether or not encrypted), store results in memory or in > temporary files > 3. Prepare the final output dataset (which again could be a temporary disk > file) > 4. Format each row of the resulting dataset, and send to the printer > > At which point should the data be unencrypted? The data should remain encrypted on disk at all times. That's what the developer expects and that's what the client expects. It's like putting your money in a bank, you expect the doors to be locked at night and your money safeguarded. If you see a sign on the bank door saying "Due to malfunctioning alarm system, doors remain unlocked and alarms turned off Sunday night." Shouldn't this be a cause for concern? > Remember thet you can "select into" a table, and that table can be > encrypted. Unless of course when creating that table, more temporary tables are created because of sorting etc., and that becomes unencrypted. Dave |
Mon, Mar 5 2007 10:52 AM | Permanent Link |
Dave Harrison | Roy Lambert wrote:
> Robert > > The obvious alternative is to use memory tables. Cleaned up when the app closes and unlikely to be looked at by someone with a hex editor. Still obtainable if you work at it but so is everything. > > > Roy Lambert > Roy, Memory tables are nice, but not practical for large tables. I'd have to dramatically increase the minimum requirements for my application. I'm doing a lot of full text searching on large tables. There is also the delay incurred when loading thousands of rows of data into memory. Dave |
Mon, Mar 5 2007 10:55 AM | Permanent Link |
Dave Harrison | Robert wrote:
> "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message > news:AAB1741C-C020-4417-B108-AD229ADEC35C@news.elevatesoft.com... > >>Robert >> >>The obvious alternative is to use memory tables. Cleaned up when the app >>closes and unlikely to be looked at by someone with a hex editor. Still >>obtainable if you work at it but so is everything. >> > > > The problem is that more and more customers are requiring that you > specifically tell them of any potential areas of exposure. Example: say I > run a large query, getting all kinds of encrypted data, which I know creates > a temporary dataset. Then I kill the application, the dataset remains, > unencrypted, in the temp folder. Then I FTP the dataset to the competition, > who downloads DBSYS for free and has a field day with my customer's data. > One of those that comes up during acceptance testing is enough to kill a > sale. > > The fact that DBISAM is "non standard" (read, non Microsoft or Oracle) makes > it worse. I don't know what the answer is, but I see the potential problem. > > Robert > > Robert, Yes, there is also the legal aspect to consider. I hadn't thought of that. Maybe it's time to increase my liability insurance? Dave |
Mon, Mar 5 2007 11:48 AM | Permanent Link |
Dave Harrison | Tim Young [Elevate Software] wrote:
> Dave, > > << I need to have some of my tables encrypted and I thought DBISAM did a > pretty good job. That dream was shattered when I discovered the canned > result set from SQL queries on an encrypted table are sitting in the private > directory without any encryption. >> > > Use the ENCRYPTED WITH clause: > > http://www.elevatesoft.com/dbisam4d7_select_statement.htm That's great. I'll implement it on all my queries that involve sorting or table joins. I meant to say "Can I assume your TDBISAMTables's don't have this problem?". Your TDBISAMTables don't created temporary tables do they? If so, I'll likely use tables instead of queries whenever possible. > > << My question is, why can't these canned queries be encrypted with the same > password if the original tables were encrypted? Why strip away encryption on > all canned queries? >> > > a) Not everyone wants the result sets to be encrypted by default. Some > developers only want the long-standing tables on disk encrypted. My interpretation is, if the data is encrypted on disk, then it should stay encrypted in the temporary files. If you ask a programmer "Is it ok to remove the encryption on the data for intermediate tables?" I'll bet you the answer is a resounding "NO!". > > b) There is the possibility that a query could contain a join between an > encrypted table and a non-encrypted table. Which do we use to determine > whether to use encryption or not ? If any of the tables are encrypted, then the temporary tables should remain encrypted. > > c) The database server doesn't have this issue since the canned result sets > are usually never accessible by clients directly. You're assuming of course that the client is using a dedicated database server that is in a secure location. Often that is not the case. Client's are "cheap" and often put the DBISAM server on machines that are used for other purposes, which may allow users access to the temporary unencrypted files. > > << Even canned result sets from table joins should remain encrypted if one > or more of the tables were encrypted. Use the same password as one of the > encrypted tables, it doesn't matter which one, as long as the data remains > secure. >> > > We can't just arbitrarily pick an encryption password from one of the > tables. For temporary tables, I think you have to. > In the case of the INTO clause, the developer needs to know that > the result set is encrypted with a specific password that can be used to > open it afterwards without having to try and guess which one was used. > When using "Into", if the new table is permanent then you're right, the user needs to explicitly specify the password like he would for any other permanent table. But for temporary tables that were created from encrypted data and is beyond the developer's control, these tables should still be encrypted. Otherwise this is a hole in the database's security. It's going to be embarrasing if DBISAM developers now have to disclose to their clients (I think they are legally obligated to do so), that the data is no longer encrypted if a canned result set is produced. That means the client's data can be sitting on the disk unencrypted when a table is sorted on a non-indexed column or when tables are joined. There is mounting legal pressure and legislation enacted to force companies to tighten security to stop hackers (& employees) from gaining access to their critical data. Release of "secure" client information could be embarrassing to the company and can produce a litigation nightmare that could see fines in the tens of millions of dollars. I don't see any alternative but to fix the problem. Sure we can both argue the technical merits of whether the temporary data should remain encrypted or not, but in the end, it's the legal ramifications that will determine whether the problem is fixed or not. Dave |
Mon, Mar 5 2007 12:28 PM | Permanent Link |
"Robert" | "Dave Harrison" <daveh_18824@spammore.com> wrote in message news:40819DCC-ABCF-4BD7-8E10-F5CA36AE3A72@news.elevatesoft.com... << Even canned result sets from table joins should remain encrypted if one >> or more of the tables were encrypted. Use the same password as one of the >> encrypted tables, it doesn't matter which one, as long as the data >> remains secure. >> >> >> We can't just arbitrarily pick an encryption password from one of the >> tables. > > For temporary tables, I think you have to. Why? There's no reason for anybody other than the engine at that time to know what the password is. Create a password on the fly, it will only be used to encrypt/decrypt that temporary dataset. If the application crashes, the temp dataset is unusable, but who cares? In fact, this issue is giving me the creeps, I'd rather see all temp files encrypted as a default. Do a google "sox compliance" and you'll see the importance of this issue. I wish I had not read this thread Temporary files remaining on the workstation were an annoyance, now I see they can be a liability. I wonder if one can force the temporary folder to be some sort of secure folder? Can you encrypt at the folder level? Maybe that's the solution. > > It's going to be embarrasing if DBISAM developers now have to disclose to > their clients (I think they are legally obligated to do so), that the data > is no longer encrypted if a canned result set is produced. That means the > client's data can be sitting on the disk unencrypted when a table is > sorted on a non-indexed column or when tables are joined. > > There is mounting legal pressure and legislation enacted to force > companies to tighten security to stop hackers (& employees) from gaining > access to their critical data. Release of "secure" client information > could be embarrassing to the company and can produce a litigation > nightmare that could see fines in the tens of millions of dollars. I don't > see any alternative but to fix the problem. Sure we can both argue the > technical merits of whether the temporary data should remain encrypted or > not, but in the end, it's the legal ramifications that will determine > whether the problem is fixed or not. > I'm certainly not a lawyer, but I do work with large companies and tend to agree with you, though I had never previously considered this issue. Robert > Dave |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |