How to protect primary key value on a web page?
I want to bounce some ideas off of MySQL developers that use it for web development. Maybe I'm a little paranoid, but when dealing with the Internet, I want to make my web app as secure as possible. I'm hoping some of you can offer me some ideas in this respect. I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? I thought of using UUID_Short() for the primary key instead of an auto-inc, and this isn't really random. It generates near sequential numbers based on time. So I need a way of encrypting the cust_id before sending it to the web page. The user can bookmark this page in his browser so I need to be able to decrypt it back to the real cust_id to retrieve the data. Doing the encryption and decryption is easy enough for me to do on the web server. I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except the string is very long at 64 characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string. Another alternative is to store an MD5 hash value of Cust_Id in the table under a different column Cust_Id_Hash and display that on the web page. So the table joins would still use Cust_Id and Cust_Id_Hash would be used only as a lookup when communicate with the web page. But Innodb's ability to store large random strings will slow down inserts and will consume more disk space. What is the best way to solve the problem? I don't want to re-invent the wheel because I'm sure this problem has been solved by other web developers. Maybe an efficient solution is staring me in the face, so I'm open to some suggestions. :-) TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to protect primary key value on a web page?
Hi there, Yes I think its actually a pattern a few hundreds million sites solved already :) And any way to encrypt (scramble)the http get string would do. But my question is , are you afraid of sql injection? How do fear your db would be violated? On Mar 10, 2011 6:13 PM, mos mo...@fastmail.fm wrote: I want to bounce some ideas off of MySQL developers that use it for web development. Maybe I'm a little paranoid, but when dealing with the Internet, I want to make my web app as secure as possible. I'm hoping some of you can offer me some ideas in this respect. I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? I thought of using UUID_Short() for the primary key instead of an auto-inc, and this isn't really random. It generates near sequential numbers based on time. So I need a way of encrypting the cust_id before sending it to the web page. The user can bookmark this page in his browser so I need to be able to decrypt it back to the real cust_id to retrieve the data. Doing the encryption and decryption is easy enough for me to do on the web server. I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except the string is very long at 64 characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string. Another alternative is to store an MD5 hash value of Cust_Id in the table under a different column Cust_Id_Hash and display that on the web page. So the table joins would still use Cust_Id and Cust_Id_Hash would be used only as a lookup when communicate with the web page. But Innodb's ability to store large random strings will slow down inserts and will consume more disk space. What is the best way to solve the problem? I don't want to re-invent the wheel because I'm sure this problem has been solved by other web developers. Maybe an efficient solution is staring me in the face, so I'm open to some suggestions. :-) TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: How to protect primary key value on a web page?
Am 10.03.2011 18:10, schrieb mos: I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? the most sites will handle this by checking permissions security by obscurity is simple crap if i have access to record 738 and get z39 by changing the url your application is simply broken signature.asc Description: OpenPGP digital signature
Re: How to protect primary key value on a web page?
On Thursday 10 March 2011 11:45:27 am Reindl Harald wrote: Am 10.03.2011 18:10, schrieb mos: I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? the most sites will handle this by checking permissions security by obscurity is simple crap if i have access to record 738 and get z39 by changing the url your application is simply broken I think the original poster knows/suspects his application is broken and thats why he's asking. I think he has a case where he allows a user to edit their own records and doesn't have the ability to require a username/password from them, I have a similar situation. What I do is store a random number in their record, which I also include in the url. Access to the record is gained by the combination of id, and tag. Just a thought. -- Take care and have fun, Mike Diehl. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to protect primary key value on a web page?
At 12:37 PM 3/10/2011, Claudio Nanni wrote: Hi there, Yes I think its actually a pattern a few hundreds million sites solved already :) Great. How did they do it? :) And any way to encrypt (scramble)the http get string would do. But my question is , are you afraid of sql injection? I'm using parameterized queries and validating user input so SQL injection shouldn't be a problem. I just don't want to give the hacker any more useful information than necessary. Let's say I have a Document_Id column and the url is www.mydocuments.com/public?docid=4 to retrieve document_id=4, I don't want someone to write a program to retrieve all of my public documents and download them. I want them to go through the user interface. The private documents of course need a user name and password to access them, but public documents do not require passwords. So hashing or encrypting the id column will make the id's non-contiguous and impossible to guess. Mike How do fear your db would be violated? On Mar 10, 2011 6:13 PM, mos mailto:mo...@fastmail.fmmo...@fastmail.fm wrote: I want to bounce some ideas off of MySQL developers that use it for web development. Maybe I'm a little paranoid, but when dealing with the Internet, I want to make my web app as secure as possible. I'm hoping some of you can offer me some ideas in this respect. I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? I thought of using UUID_Short() for the primary key instead of an auto-inc, and this isn't really random. It generates near sequential numbers based on time. So I need a way of encrypting the cust_id before sending it to the web page. The user can bookmark this page in his browser so I need to be able to decrypt it back to the real cust_id to retrieve the data. Doing the encryption and decryption is easy enough for me to do on the web server. I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except the string is very long at 64 characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string. Another alternative is to store an MD5 hash value of Cust_Id in the table under a different column Cust_Id_Hash and display that on the web page. So the table joins would still use Cust_Id and Cust_Id_Hash would be used only as a lookup when communicate with the web page. But Innodb's ability to store large random strings will slow down inserts and will consume more disk space. What is the best way to solve the problem? I don't want to re-invent the wheel because I'm sure this problem has been solved by other web developers. Maybe an efficient solution is staring me in the face, so I'm open to some suggestions. :-) TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysqlhttp://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.comhttp://lists.mysql.com/mysql?unsub=claudio.na...@gmail.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: How to protect primary key value on a web page?
Am 10.03.2011 21:09, schrieb mos: At 12:37 PM 3/10/2011, Claudio Nanni wrote: Hi there, Yes I think its actually a pattern a few hundreds million sites solved already :) Great. How did they do it? :) And any way to encrypt (scramble)the http get string would do. But my question is , are you afraid of sql injection? I'm using parameterized queries and validating user input so SQL injection shouldn't be a problem. I just don't want to give the hacker any more useful information than necessary. Let's say I have a Document_Id column and the url is www.mydocuments.com/public?docid=4 to retrieve document_id=4, I don't want someone to write a program to retrieve all of my public documents and download them. I want them to go through the user interface. The private documents of course need a user name and password to access them, but public documents do not require passwords. So hashing or encrypting the id column will make the id's non-contiguous and impossible to guess. sorry but this is foolish leave the id in peace and add a colum with some checksum signature.asc Description: OpenPGP digital signature
Re: How to protect primary key value on a web page?
On Mar 10, 2011 9:13 PM, mos mo...@fastmail.fm wrote: At 12:37 PM 3/10/2011, Claudio Nanni wrote: Hi there, Yes I think its actually a pattern a few hundreds million sites solved already :) Great. How did they do it? :) Please, google for me I am cooking right now :) And any way to encrypt (scramble)the http get string would do. But my question is , are you afraid of sql injection? I'm using parameterized queries and validating user input so SQL injection shouldn't be a problem. I just don't want to give the hacker any more useful information than necessary. Let's say I have a Document_Id column and the url is www.mydocuments.com/public?docid=4 to retrieve document_id=4, I don't want someone to write a program to retrieve all of my public documents and download them. I want them to go through the user interface. The private documents of course need a user name and password to access them, but public documents do not require passwords. So hashing or encrypting the id column will make the id's non-contiguous and impossible to guess. then you have the solution! I actually I am not a GET lover for your same reasons, and I would just store an handle in the cookie and keep all the state in a session on the server. Mike How do fear your db would be violated? On Mar 10, 2011 6:13 PM, mos mailto:mo...@fastmail.fm mo...@fastmail.fm wrote: I want to bounce some ideas off of MySQL developers that use it for web development. Maybe I'm a little paranoid, but when dealing with the Internet, I want to make my web app as secure as possible. I'm hoping some of you can offer me some ideas in this respect. I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? I thought of using UUID_Short() for the primary key instead of an auto-inc, and this isn't really random. It generates near sequential numbers based on time. So I need a way of encrypting the cust_id before sending it to the web page. The user can bookmark this page in his browser so I need to be able to decrypt it back to the real cust_id to retrieve the data. Doing the encryption and decryption is easy enough for me to do on the web server. I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except the string is very long at 64 characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string. Another alternative is to store an MD5 hash value of Cust_Id in the table under a different column Cust_Id_Hash and display that on the web page. So the table joins would still use Cust_Id and Cust_Id_Hash would be used only as a lookup when communicate with the web page. But Innodb's ability to store large random strings will slow down inserts and will consume more disk space. What is the best way to solve the problem? I don't want to re-invent the wheel because I'm sure this problem has been solved by other web developers. Maybe an efficient solution is staring me in the face, so I'm open to some suggestions. :-) TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: How to protect primary key value on a web page?
On Mar 10, 2011 9:23 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 10.03.2011 21:09, schrieb mos: At 12:37 PM 3/10/2011, Claudio Nanni wrote: Hi there, Yes I think its actually a pattern a few hundreds million sites solved already :) Great. How did they do it? :) And any way to encrypt (scramble)the http get string would do. But my question is , are you afraid of sql injection? I'm using parameterized queries and validating user input so SQL injection shouldn't be a problem. I just don't want to give the hacker any more useful information than necessary. Let's say I have a Document_Id column and the url is www.mydocuments.com/public?docid=4 to retrieve document_id=4, I don't want someone to write a program to retrieve all of my public documents and download them. I want them to go through the user interface. The private documents of course need a user name and password to access them, but public documents do not require passwords. So hashing or encrypting the id column will make the id's non-contiguous and impossible to guess. sorry but this is foolish leave the id in peace and add a colum with some checksum Wordpress guys are also foolish? They do not even encrypt. And what's the difference between passing in a GET an encrypted Id or passing another column with a checksum deriving from the Id?
Re: How to protect primary key value on a web page?
Am 10.03.2011 21:56, schrieb Claudio Nanni: On Mar 10, 2011 9:23 PM, Reindl Harald h.rei...@thelounge.net wrote: So hashing or encrypting the id column will make the id's non-contiguous and impossible to guess. sorry but this is foolish leave the id in peace and add a colum with some checksum Wordpress guys are also foolish? of course they are look at their awful code you will not really tell me that quality looks like wordpress? And what's the difference between passing in a GET an encrypted Id or passing another column with a checksum deriving from the Id? what exactly do you not understand? fecth the record by its primary key is pretty fast decide the data-output by a checksum which is independent to the key how will you do this any other way? you can not use hash functions because you can not revert them for fetch the record, so you have to use obfusction you can revert to the key and if you can do this anybody will sooner or later signature.asc Description: OpenPGP digital signature
Re: How to protect primary key value on a web page?
On 3/10/2011 12:10, mos wrote: I want to bounce some ideas off of MySQL developers that use it for web development. Maybe I'm a little paranoid, but when dealing with the Internet, I want to make my web app as secure as possible. I'm hoping some of you can offer me some ideas in this respect. I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. ... You could follow some of the basic security designs already in use. 1) use https:// 2) Don't worry about the URLs, worry about authenticating the requesting user to the session to the data. Only allow the users access to what they are supposed to have access to in the quantities they are allowed to view it. 3) You could include the session identifier as part of the URL. Once the session expires, that URL is now dead. One of your worries was a BOT coming along and scraping off all of your public files. That's pretty easy to catch if you actively monitor usage patterns. Another way of doing that is to have two unique identifiers for each data object, one is the sequential private number, the other is the non-incremental (random or hash) value that you can expose via URL. It's not really securing anything but it is making it harder for random successes. If they fail to randomly find a valid value enough times, you lock out that IP address. However this really isn't a great topic for a database list as most of solution to your problems reside in how you design your application. Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to protect primary key value on a web page?
Hi. On Thursday 10 Mar 2011 at 20:09 mos wrote: [snip] Let's say I have a Document_Id column and the url is www.mydocuments.com/public?docid=4 to retrieve document_id=4, I don't want someone to write a program to retrieve all of my public documents and download them. I want them to go through the user interface. Leaving aside the silliness of making a document public then trying to stop people downloading it, there is at least one common solution available to you - Apache's mod_rewrite. http://httpd.apache.org/docs/2.0/mod/mod_rewrite.html Obviously this is dependant on you running Apache, but it is a simple and common approach that will give you what you want. You could also consider rate-limiting your application so that users who request too many pages for your tastes (indicating a possible program) are deliberately slowed down. Beware that this solution will likely have a detrimental effect on search engine spiders, and therefore your site rankings. However, neither of these solutions are appropriate for discussion on a MySQL mailing list, and I agree with many of the other responses you have had - your plan to do this by changing your database is pointless and misdirected. Cheers, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
