Re: Corrupted table
I switched the tmp directory to a much bigger partition and I am still getting the error. Any other ideas? TIA, Zak --- On Wed, 3/10/10, Walter Heck wal...@openquery.com wrote: From: Walter Heck wal...@openquery.com Subject: Re: Corrupted table To: Zakai Kinan titanyen2...@yahoo.com Cc: mysql@lists.mysql.com Date: Wednesday, March 10, 2010, 8:29 AM That message usually means you ran out of space in your temp folder location. cheers, Walter Heck -- Engineer @ Open Query http://openquery.com | http://openquery.com/blog On Thu, Mar 11, 2010 at 02:16, Zakai Kinan titanyen2...@yahoo.com wrote: Is there a way to know exactly which table is corrupted with such an error: [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_7885_0.MYI'; try to repair it. TIA, ZK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=titanyen2...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Encryption
Hi What sort of information are you looking to encrypt ? If it is for user passwords I'd recommend SHA256 which is one way encryption. Or are you looking to encrypt more sensitive information like card holder data ? Regards Neil On Fri, Mar 19, 2010 at 4:22 PM, Jim j...@lowcarbfriends.com wrote: Thanks for the reply, John. What you are describing seems to be the approach I've seen on the few places I've seen this topic discussed. I've been considering something along those lines, essentially a two part key. Part one of the key is made from some data that is in the record I want to protect and it is different for each record, very much like you suggest. Part two of the key is some constant key value I store somewhere. The full key is created based on some defined manipulation of the two parts, much like you suggest I believe. But, then the issue comes of where to store part two of the key. In your case, you are storing it in a stored procedure and I assume that stored procedure resides on the same mysql server that holds the data you want to protect. That's where I start questioning the security of that approach. The assumption being if someone got full control of that mysql box then essentially all your eggs are in one basket. I was thinking in terms of a most secure solution, you could have a separate server (perhaps a mysql server) that for the purpose of this example only serves part two of the key. That server is well protected and non-public as is the mysql server that stores the data. This way, two servers have to be compromised in order to gain all the parts of the key and data. But, of course, that's kind of a waste of a server and can you afford that and the extra resources that go along with maintaining another server. So, I was thinking, is it really so bad to store only one part of the key in source code. That source code resides on a separate server from the mysql server. Yes, the server that stores the source code is a public server, but at least it's two servers that have to be compromised to give up all the components needed to gain access to the encrypted data. I suppose maybe if I ask you to expand on what you mean by the following that would be helpful to further understand your approach: I then store the logic in a database stored procedure and use database security to prevent unauthorised access. Thanks, Jim On 3/19/2010 6:39 AM, John Daisley wrote: Jim, I tend to derive a key based on a separate character string and the contents of the data in the same or a related table. This means each row has a unique encryption key and you never have to have the whole key stored somewhere (you don't even know it :p ). Biggest advantage to this is should someone get hold of your data they have to work out your character string and the logic for deriving the key or attempt to hack each and every individual row of the table because no two rows will ever have the same key. For example, in a table with the columns `username`, `email_address`, `password`, `jointime` (where password is encrypted with AES_ENCRYPT) I may Use a charcter string of awfully_complex_char_string- and derive the key like so CONCAT(awfully_complex_char_string-,SUBSTRING(`email_address`,1,LOCATE(@,`email_address`)-1),CAST(`jointime` AS CHAR)) I then store the logic in a database stored procedure and use database security to prevent unauthorised access. At no point do I have this logic outside the database in any external application or script! That would be silly :) Regards John Daisley On Thu, Mar 18, 2010 at 7:26 PM, Jim j...@lowcarbfriends.com mailto:j...@lowcarbfriends.com wrote: In terms of encryption functions AES_DECRYPT and AES_ENCRYPT, can anyone point to any good links or offer any suggestions in terms of best practices on storage of the associated symmetric key? I've found very little information on this when searching. Does MySQL offer any asymmetric encryption capabilities? What are people using in terms of a good solution for encrypting specific columns of table data while providing protection of the key? Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: MySQL Storage Engine
Thanks for all the responses and useful information. Cheers Neil On Thu, Mar 18, 2010 at 6:35 PM, mos mo...@fastmail.fm wrote: At 04:18 AM 3/18/2010, Tompkins Neil wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Neil, I would recommend using MyISAM if the queries where 90% reads and less than 10% writes. Since you are running a webpage you will of course need to use parameterized queries to avoid sql injection attacks. Innodb is fine if the table is small enough that it will fit into memory. If you don't have enough ram on your machine then innodb can be quite sluggish. If you need transactions, then Innodb is necessary. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: MySQL Encryption
Hi Neil. Information (in most cases a string 100 chars, but that's probably not important) that actually needs to be decrypted, so a hash won't do. Jim On 3/20/2010 5:09 PM, Tompkins Neil wrote: Hi What sort of information are you looking to encrypt ? If it is for user passwords I'd recommend SHA256 which is one way encryption. Or are you looking to encrypt more sensitive information like card holder data ? Regards Neil On Fri, Mar 19, 2010 at 4:22 PM, Jim j...@lowcarbfriends.com mailto:j...@lowcarbfriends.com wrote: Thanks for the reply, John. What you are describing seems to be the approach I've seen on the few places I've seen this topic discussed. I've been considering something along those lines, essentially a two part key. Part one of the key is made from some data that is in the record I want to protect and it is different for each record, very much like you suggest. Part two of the key is some constant key value I store somewhere. The full key is created based on some defined manipulation of the two parts, much like you suggest I believe. But, then the issue comes of where to store part two of the key. In your case, you are storing it in a stored procedure and I assume that stored procedure resides on the same mysql server that holds the data you want to protect. That's where I start questioning the security of that approach. The assumption being if someone got full control of that mysql box then essentially all your eggs are in one basket. I was thinking in terms of a most secure solution, you could have a separate server (perhaps a mysql server) that for the purpose of this example only serves part two of the key. That server is well protected and non-public as is the mysql server that stores the data. This way, two servers have to be compromised in order to gain all the parts of the key and data. But, of course, that's kind of a waste of a server and can you afford that and the extra resources that go along with maintaining another server. So, I was thinking, is it really so bad to store only one part of the key in source code. That source code resides on a separate server from the mysql server. Yes, the server that stores the source code is a public server, but at least it's two servers that have to be compromised to give up all the components needed to gain access to the encrypted data. I suppose maybe if I ask you to expand on what you mean by the following that would be helpful to further understand your approach: I then store the logic in a database stored procedure and use database security to prevent unauthorised access. Thanks, Jim On 3/19/2010 6:39 AM, John Daisley wrote: Jim, I tend to derive a key based on a separate character string and the contents of the data in the same or a related table. This means each row has a unique encryption key and you never have to have the whole key stored somewhere (you don't even know it :p ). Biggest advantage to this is should someone get hold of your data they have to work out your character string and the logic for deriving the key or attempt to hack each and every individual row of the table because no two rows will ever have the same key. For example, in a table with the columns `username`, `email_address`, `password`, `jointime` (where password is encrypted with AES_ENCRYPT) I may Use a charcter string of awfully_complex_char_string- and derive the key like so CONCAT(awfully_complex_char_string-,SUBSTRING(`email_address`,1,LOCATE(@,`email_address`)-1),CAST(`jointime` AS CHAR)) I then store the logic in a database stored procedure and use database security to prevent unauthorised access. At no point do I have this logic outside the database in any external application or script! That would be silly :) Regards John Daisley On Thu, Mar 18, 2010 at 7:26 PM, Jim j...@lowcarbfriends.com mailto:j...@lowcarbfriends.com mailto:j...@lowcarbfriends.com mailto:j...@lowcarbfriends.com wrote: In terms of encryption functions AES_DECRYPT and AES_ENCRYPT, can anyone point to any good links or offer any suggestions in terms of best practices on storage of the associated symmetric key? I've found very little information on this when searching. Does MySQL offer any asymmetric encryption capabilities? What are people using in terms of a good solution for encrypting specific columns of table data while providing protection of the key? Thanks, Jim --
RE: MySQL Encryption
Jim In the case of our encrypted data no user, application or script is given access to the tables in question. Access is only granted via a couple of stored procedures and to be honest if you didn't know which ones you would have a hard job finding them as we have hundreds. Problem with keeping any part of the key in a place other than the mysql server is you add complexity and give yourself a whole bunch of new security concerns as you then have to transmit the 'key part' to the mysql server over a network. For someone to take complete control of our mysql server and compromise our data they would need to guess a username and password for the box in under 3 attempts, then guess the root password and then guess a valid mysql username and password. The biggest headache for us, and one which is often overlooked is 'How do we keep our backups secure' -Original Message- From: Jim j...@lowcarbfriends.com Sent: Friday, March 19, 2010 4:22 PM To: John Daisley daisleyj...@googlemail.com; mysql@lists.mysql.com Subject: Re: MySQL Encryption Thanks for the reply, John. What you are describing seems to be the approach I've seen on the few places I've seen this topic discussed. I've been considering something along those lines, essentially a two part key. Part one of the key is made from some data that is in the record I want to protect and it is different for each record, very much like you suggest. Part two of the key is some constant key value I store somewhere. The full key is created based on some defined manipulation of the two parts, much like you suggest I believe. But, then the issue comes of where to store part two of the key. In your case, you are storing it in a stored procedure and I assume that stored procedure resides on the same mysql server that holds the data you want to protect. That's where I start questioning the security of that approach. The assumption being if someone got full control of that mysql box then essentially all your eggs are in one basket. I was thinking in terms of a most secure solution, you could have a separate server (perhaps a mysql server) that for the purpose of this example only serves part two of the key. That server is well protected and non-public as is the mysql server that stores the data. This way, two servers have to be compromised in order to gain all the parts of the key and data. But, of course, that's kind of a waste of a server and can you afford that and the extra resources that go along with maintaining another server. So, I was thinking, is it really so bad to store only one part of the key in source code. That source code resides on a separate server from the mysql server. Yes, the server that stores the source code is a public server, but at least it's two servers that have to be compromised to give up all the components needed to gain access to the encrypted data. I suppose maybe if I ask you to expand on what you mean by the following that would be helpful to further understand your approach: I then store the logic in a database stored procedure and use database security to prevent unauthorised access. Thanks, Jim On 3/19/2010 6:39 AM, John Daisley wrote: Jim, I tend to derive a key based on a separate character string and the contents of the data in the same or a related table. This means each row has a unique encryption key and you never have to have the whole key stored somewhere (you don't even know it :p ). Biggest advantage to this is should someone get hold of your data they have to work out your character string and the logic for deriving the key or attempt to hack each and every individual row of the table because no two rows will ever have the same key. For example, in a table with the columns `username`, `email_address`, `password`, `jointime` (where password is encrypted with AES_ENCRYPT) I may Use a charcter string of awfully_complex_char_string- and derive the key like so CONCAT(awfully_complex_char_string-,SUBSTRING(`email_address`,1,LOCATE(@,`email_address`)-1),CAST(`jointime` AS CHAR)) I then store the logic in a database stored procedure and use database security to prevent unauthorised access. At no point do I have this logic outside the database in any external application or script! That would be silly :) Regards John Daisley On Thu, Mar 18, 2010 at 7:26 PM, Jim j...@lowcarbfriends.com mailto:j...@lowcarbfriends.com wrote: In terms of encryption functions AES_DECRYPT and AES_ENCRYPT, can anyone point to any good links or offer any suggestions in terms of best practices on storage of the associated symmetric key? I've found very little information on this when searching. Does MySQL offer any asymmetric encryption capabilities? What are people using in terms of a good solution for encrypting specific columns of table data while providing protection of the key? Thanks, Jim -- MySQL General
RE: MySQL Encryption
At 05:54 PM 3/20/2010, John Daisley wrote: Jim In the case of our encrypted data no user, application or script is given access to the tables in question. Access is only granted via a couple of stored procedures and to be honest if you didn't know which ones you would have a hard job finding them as we have hundreds. Problem with keeping any part of the key in a place other than the mysql server is you add complexity and give yourself a whole bunch of new security concerns as you then have to transmit the 'key part' to the mysql server over a network. For someone to take complete control of our mysql server and compromise our data they would need to guess a username and password for the box in under 3 attempts, then guess the root password and then guess a valid mysql username and password. The biggest headache for us, and one which is often overlooked is 'How do we keep our backups secure' Or they can find your drive in the local swap shop after the ISP retires your drive for a new one. Or if you are doing your own hosting, the local bne artist will do a smash and grab if they find out you have computers in your office. It happens all the time over here, even government offices aren't immune. Of course if your competitors want your information bad enough, they'll bribe one of the support staff to make an extra backup, or your competitor will hire people to recover your drive. Corporate and intra-country espionage is growing rapidly and is largely unreported by the ISP and the companies that were hit. There are many ways your drive can grow legs and walk out of there. So storing all of the passwords on the drive isn't secure enough, unless the drive itself is encrypted with a password known only to a few people in your company. Never trust the internet service provider to be your only means to protect your data or your drives. Mike -Original Message- From: Jim j...@lowcarbfriends.com Sent: Friday, March 19, 2010 4:22 PM To: John Daisley daisleyj...@googlemail.com; mysql@lists.mysql.com Subject: Re: MySQL Encryption Thanks for the reply, John. What you are describing seems to be the approach I've seen on the few places I've seen this topic discussed. I've been considering something along those lines, essentially a two part key. Part one of the key is made from some data that is in the record I want to protect and it is different for each record, very much like you suggest. Part two of the key is some constant key value I store somewhere. The full key is created based on some defined manipulation of the two parts, much like you suggest I believe. But, then the issue comes of where to store part two of the key. In your case, you are storing it in a stored procedure and I assume that stored procedure resides on the same mysql server that holds the data you want to protect. That's where I start questioning the security of that approach. The assumption being if someone got full control of that mysql box then essentially all your eggs are in one basket. I was thinking in terms of a most secure solution, you could have a separate server (perhaps a mysql server) that for the purpose of this example only serves part two of the key. That server is well protected and non-public as is the mysql server that stores the data. This way, two servers have to be compromised in order to gain all the parts of the key and data. But, of course, that's kind of a waste of a server and can you afford that and the extra resources that go along with maintaining another server. So, I was thinking, is it really so bad to store only one part of the key in source code. That source code resides on a separate server from the mysql server. Yes, the server that stores the source code is a public server, but at least it's two servers that have to be compromised to give up all the components needed to gain access to the encrypted data. I suppose maybe if I ask you to expand on what you mean by the following that would be helpful to further understand your approach: I then store the logic in a database stored procedure and use database security to prevent unauthorised access. Thanks, Jim On 3/19/2010 6:39 AM, John Daisley wrote: Jim, I tend to derive a key based on a separate character string and the contents of the data in the same or a related table. This means each row has a unique encryption key and you never have to have the whole key stored somewhere (you don't even know it :p ). Biggest advantage to this is should someone get hold of your data they have to work out your character string and the logic for deriving the key or attempt to hack each and every individual row of the table because no two rows will ever have the same key. For example, in a table with the columns `username`, `email_address`, `password`, `jointime` (where password is encrypted with AES_ENCRYPT) I may Use a charcter string of awfully_complex_char_string- and derive the key like so
