Re: Retrieve most recent of multiple rows
not all the rows, only the distinct q_id, On Wed, Mar 13, 2013 at 8:28 PM, Johan De Meersman vegiv...@tuxera.bewrote: -- *From: *Ananda Kumar anan...@gmail.com *Subject: *Re: Retrieve most recent of multiple rows select qid,max(atimestamp) from kkk where qid in (select distinct qid from kkk) group by qid; What use is that where statement? It just says to use all the rows in the table. -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: file level encryption on mysql
I'm sorry for top-posting, but I think you can achieve the best practice if you'd encrypt user data with some sort of hash made from the part of the password, i.e. after user is logged in you can store personal key for the user in memory for decryption so you have to know every user password (or part of it) to get the info from these 3 tables. The password itself for the user should be stored as a hash in database (use bcrypt). All decryption / encryption should be done in your application. The only disadvantage is you won't be able to read user's data if you don't know user's password. 2013/2/5 Rick James rja...@yahoo-inc.com: AES encryption is weak because it is too easy for the hacker to get the passphrase. If you can somehow hide the passphrase behind 'root', you can at least prevent a non-sudo user from seeing the data. Your web server starts as root, then degrades itself before taking requests. If it can grab the passphrase before that, it can keep it in RAM for use, but not otherwise expose it. Bottom line: The problem (of protecting data from hacker/thief/etc) cannot be solved by just MySQL. (And perhaps MySQL is not even part of the solution.) -Original Message- From: Mike Franon [mailto:kongfra...@gmail.com] Sent: Tuesday, February 05, 2013 6:43 AM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: file level encryption on mysql Which is the best way ? I see you can do it from PHP itself http://coding.smashingmagazine.com/2012/05/20/replicating-mysql-aes- encryption-methods-with-php/ or can use mysql AES? http://security.stackexchange.com/questions/16473/how-do-i-protect- user-data-at-rest From what I understand we need two way and one way encryption. Is the best way what the first article is recommending? On Tue, Feb 5, 2013 at 9:20 AM, Reindl Harald h.rei...@thelounge.net wrote: you have to encrypt them in the application and make the key stored as safe as possible, however for a full intrution there is no way to protect data which can not be only hashed somewhere you need the information how to encrypt them Am 05.02.2013 15:18, schrieb Mike Franon: I tried all these methods and you are right this is not going to work for us. I am not a developer, does anyone have any good links or reference to the best way I can share with my developers on best way to encrypt and decrypt personal user info. We do not store credit cards, but want to store 3 tables that have email address, ip address, and personal info. On Sun, Feb 3, 2013 at 12:57 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 03.02.2013 18:52, schrieb Mike Franon: Hi, I was wondering what type of encryption for linux would you recommend to encrypt the database files on the OS level? I had a hard time starting the database after I moved it to a partiton with encryptFS I only need 3 tables encrypted and know it is better to do it from the application, but unfortunately that cannot happen for a while. Has anyone done OS file level encryption, and if so which one did they use? https://wiki.archlinux.org/index.php/Dm-crypt_with_LUKS but this all is useless in case of intrusion because the FS is unlocked and you have no gain - FS encryption only matters if your notebook or disks get stolen which is unlikely on a server -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Aw: Re: Retrieve most recent of multiple rows
not all the rows, only the distinct q_id, The subquery will give all distinct q_ids, but the select from in(subquery) will give everything, because each row must have a q_id which is returned by the subquery. The query after all says select all rows where the q_id is one of the q_ids occuring in the table and this, of course, gives everything. Also your select qid, max(atimestamp) is not doing what I think you want to do. I think you want all raws where atimestamp is somehow a maximum. Now in your query max(atimesamp) will return one single value and this will be used as a sort of constant. So if max(atimestamp) is e. g. 5, you get effictively select qid, 5 from ... Qou will get all rows with the constant becoming a row. You want to filter rows, so your condition must be in where (this is a basic concept of sql). Somehow (this is not correct sql, just a hint) it must be like select qid from kkk where atimestamp=max(atimestamp). If you want to filter the condition must be in where, a function on a column behins select will not filter. I think you need to get a basic understanding of sql first, sorry if that sounds harsh Stefan On Wed, Mar 13, 2013 at 8:28 PM, Johan De Meersman vegiv...@tuxera.bewrote: -- *From: *Ananda Kumar anan...@gmail.com *Subject: *Re: Retrieve most recent of multiple rows select qid,max(atimestamp) from kkk where qid in (select distinct qid from kkk) group by qid; What use is that where statement? It just says to use all the rows in the table. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Re: Retrieve most recent of multiple rows
Mr. stefan, Thanks for let me know about basic of sql. But if you see my earlier response, i have given the correct sql select * from tab where anwer_timestamp in (select max(anwer_timestamp) from tab where q_id in (select distinct q_id from tab) group by q_id); You missed reading my earlier email, where my basic on sql is good enough. I wrongly typed the sql and missed one where condition in my later response. Also, please understand, people on this forum are trying to help each other. Some might give the required solution with exact requirement, while some give just hints, which helps the requester in solving the issue. Please be appreciative of the people trying to provide some kind of solution. hope this does not sound too harsh on u On Thu, Mar 14, 2013 at 3:44 PM, Stefan Kuhn stef...@web.de wrote: not all the rows, only the distinct q_id, The subquery will give all distinct q_ids, but the select from in(subquery) will give everything, because each row must have a q_id which is returned by the subquery. The query after all says select all rows where the q_id is one of the q_ids occuring in the table and this, of course, gives everything. Also your select qid, max(atimestamp) is not doing what I think you want to do. I think you want all raws where atimestamp is somehow a maximum. Now in your query max(atimesamp) will return one single value and this will be used as a sort of constant. So if max(atimestamp) is e. g. 5, you get effictively select qid, 5 from ... Qou will get all rows with the constant becoming a row. You want to filter rows, so your condition must be in where (this is a basic concept of sql). Somehow (this is not correct sql, just a hint) it must be like select qid from kkk where atimestamp=max(atimestamp). If you want to filter the condition must be in where, a function on a column behins select will not filter. I think you need to get a basic understanding of sql first, sorry if that sounds harsh Stefan On Wed, Mar 13, 2013 at 8:28 PM, Johan De Meersman vegiv...@tuxera.be wrote: -- *From: *Ananda Kumar anan...@gmail.com *Subject: *Re: Retrieve most recent of multiple rows select qid,max(atimestamp) from kkk where qid in (select distinct qid from kkk) group by qid; What use is that where statement? It just says to use all the rows in the table. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Retrieve most recent of multiple rows
- Original Message - From: Ananda Kumar anan...@gmail.com Subject: Re: Re: Retrieve most recent of multiple rows select * from tab where anwer_timestamp in (select max(anwer_timestamp) from tab where q_id in (select distinct q_id from tab) group by q_id); This is entirely equivalent to select * from tab where anwer_timestamp in (select max(anwer_timestamp) from tab group by q_id); Additionally, there are no double timestamps in the dataset you used, whereas there are in OP's given dataset. It's also fairly hard to provide an accurate solution as long as OP hasn't clarified what exactly they want, really. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
