Re: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
I have an idea. First create a master key that the ssn ar encrypted with. Store the master key encrypted with a key accessible by users (mksk). Every user stores the key (mksk) to decrypt the (master key) and the mksk should be ecrypted with there password as key. And when a new user is created the system uses the mksk that the the admin who create the user uses. This has the advantage that you can re-encrypt the ssn table and the only key you have to change is the master key. ** Hans Åhlin Tel: +46761488019 icq: 275232967 http://www.kronan-net.com/ irc://irc.freenode.net:6667 - TheCoin ** 2010/8/12 tedd > At 5:30 PM -0700 8/11/10, Daevid Vincent wrote: > >> > -Original Message- >> >>> 2. Were told it was a social security number >>>(i.e., in the form of 123-45-6789). >>> >> >> Stop. >> >> Why are you even contemplating storing SS# ?? >> > > Daevid et al: > > Why? Because my client wants to store SS numbers on their online system to > aid them in their collection business. > > You see, the client in this case is not asking people for their SS numbers, > but rather trying to collect unpaid debts. Their clients (i.e., creditors) > have provided them debtor data, which may/may not include SS numbers. > > My current thoughts are that the entire process will be behind a password > protected section of a web site where only the people working for the firm > will have access. The point of the system will be to aid collectors in their > collection efforts and to allow them to conduct business anywhere they can > find Internet access. > > Of course, this will not stop employees from abusing the data, but that > possibility also exist in the hard-copy only office as well -- that's a > criminal act and will be handled accordingly. The difference here is that > the data can be accessed online via password authorization. Is that too > easy? > > My effort here with my "Encryption/Decryption Question" is to focus on the > event that the web site may hacked and access to the database is provided to > an intruder. In such case, then the SS numbers residing there should be > encrypted and that was my current quest to resolve. > > Now, if federal law prohibits storing SS numbers in an online database > that's accessible via password authorization then that's "end-of-story". > I'll simply tell the client that federal law prohibits such practice and > that will be the end of it -- it makes no difference to me. > > However, if the practice of storing SS number online is not prohibited by > law, then what are the appropriate "due diligence" steps necessary to > protect such data? > > Cheers, > > tedd > > > -- > --- > http://sperling.com/ > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
RE: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
> -Original Message- > From: tedd [mailto:t...@sperling.com] > Sent: Thursday, August 12, 2010 8:32 AM > To: Bastien Koert > Cc: php-general@lists.php.net > Subject: Re: [PHP] Storing Social Security Number WAS: > Encryption/Decryption Question > > For searching standard fields, it's a piece of cake to use %LIKE%. > For example, let's say the investigator has a piece of paper that has > the number "393" on it and want's to search the database for all > phone numbers that contain "393" -- he could use %LIKE% and that > would produce 517-393-, 393-123-4567, 818-122-4393 and so on. > That's neat! > > However, if the field is encrypted, then how do you preform a partial > search on that? You can't encrypt the search string and use that > because you need the entire string. So, how do you solve that problem? > > If you hash the number of store the hash, then you can create a > hashed search string and use that. But again it doesn't work for > partial %LIKE% searches. For example, I couldn't search for "393" in > a SS# -- I would have to search for the complete SS#. > > So, how do you solve the %LIKE% problem with encryption and hashes? Well, if you can get all the encryption/decryption to take place in SQL, you can use something like this pseudocode: SELECT name, dob, DECRYPT(ssn) as rawssn FROM deadbeats HAVING rawssn LIKE '%393%'; You can assign an alias and use HAVING instead of WHERE. http://dev.mysql.com/doc/refman/5.0/en/select.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
> -Original Message- > From: tedd [mailto:t...@sperling.com] > Sent: Thursday, August 12, 2010 7:30 AM > To: a...@ashleysheridan.co.uk > Cc: php-general@lists.php.net > Subject: RE: [PHP] Storing Social Security Number WAS: > Encryption/Decryption Question > > At 2:51 PM +0100 8/12/10, Ashley Sheridan wrote: > >If you are storing the data in a DB, then I'd consider using different > >levels of access to that via different DB users, which should offer an > >extra layer of security in protecting the data. > > Of course, the routines I'm writing provide several levels of access for > different functions/job-duties. However, at some point there will be people > who will have access to SS# data. > > The real questions here are: > > 1. Is it lawful in the USA to store US SS# in an online database? > > 2. If it is lawful, then what security provisions are required? > > Cheers, > > tedd > > -- > --- > http://sperling.com/ > Tedd, I don't think it's unlawful according to the links below: http://www.ssa.gov/kc/id_practices_best.htm http://www.wireless.att.com/learn/basics/shopping-faqs.jsp#05 If your client accepts credit card as a form of payment for collection and if you comply with PCI DSS, then you're pretty much safe. (IIRC, it's not cheap to be certified by PCI DSS but you can still put their requirements in practice without having to qualify unless the client's business requires it). Regards, Tommy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
On Thu, Aug 12, 2010 at 11:32 AM, tedd wrote: > At 10:56 AM -0400 8/12/10, Bastien Koert wrote: >> >> However, the data must be stored in an encrypted format and it must be >> transmitted via SSL. We do it that way (taking both a hash for >> searching for the ssn and the encrypted form) and haven't had any >> issues as yet. > > The data will be encrypted and only accessible behind an SSL via an > authorization process -- that's given. > > I have given some thought about searching the database for encrypted SS#'s > and have been perplexed as how to do that. > > For searching standard fields, it's a piece of cake to use %LIKE%. For > example, let's say the investigator has a piece of paper that has the number > "393" on it and want's to search the database for all phone numbers that > contain "393" -- he could use %LIKE% and that would produce 517-393-, > 393-123-4567, 818-122-4393 and so on. That's neat! > > However, if the field is encrypted, then how do you preform a partial search > on that? You can't encrypt the search string and use that because you need > the entire string. So, how do you solve that problem? > > If you hash the number of store the hash, then you can create a hashed > search string and use that. But again it doesn't work for partial %LIKE% > searches. For example, I couldn't search for "393" in a SS# -- I would have > to search for the complete SS#. > > So, how do you solve the %LIKE% problem with encryption and hashes? We didn't...in our case the user either has the ssn or doesn't. No direct searching on ssn without the full ssn is supported. We do allow searching on names so its a work around that way. > >> The other thing to consider is that more and more states are looking to >> encrypt PII data >> (name, dob, ssn etc) for more security. > > I'm considering that as well, but that also raises more searching problems > as described above. > >> You could consider storing just the encrypted ssn and link data in a >> separate database, that would require a different logon to access when >> the data is required. > > Interesting -- I might also hash the foreign link. But I have to think about > that. > > Cheers, > > tedd > -- > --- > http://sperling.com/ > -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
On Aug 12, 2010, at 11:55 AM, tedd wrote: > At 11:39 AM -0400 8/12/10, Joshua Kehn wrote: >> Would one option be to have a table of unencrypted SSN's with an encrypted >> id for the user. So you could search the SSN's and then connect them, >> however if the table was dumped you wouldn't be able to link it directly to >> the person (as it would just have a SSN and an id). >> >> Regards, >> >> -Josh > > No, the problem here is to keep the database from containing any raw SS#. It > is absolutely necessary to encrypt the data. > > The question is: > > 1. Is it legal? > > 2. How to do it? > > Cheers, > > tedd > -- > --- > http://sperling.com/ Tedd- In my mind if you have a table of raw SSN's it's fine as long as they can't be readily linked to a person without decrypting the id. Again, I don't know the legalities of it so I defer. If you have to encrypt them then I'm not sure how you would run any %LIKE% query unless you went through and decrypted them OTF when running the query. This (I don't believe) can be done in MySQL (or any other RDB) so it would have to be done in memory. Depending on the table size that gets annoyingly slow. If the SSN's must be encrypted, and you aren't encrypting the parts separately ( enc - enc - enc) I don't think you will be able to run a %LIKE% query simply. Regards, -Josh Joshua Kehn | josh.k...@gmail.com http://joshuakehn.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
At 11:39 AM -0400 8/12/10, Joshua Kehn wrote: Would one option be to have a table of unencrypted SSN's with an encrypted id for the user. So you could search the SSN's and then connect them, however if the table was dumped you wouldn't be able to link it directly to the person (as it would just have a SSN and an id). Regards, -Josh No, the problem here is to keep the database from containing any raw SS#. It is absolutely necessary to encrypt the data. The question is: 1. Is it legal? 2. How to do it? Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
On Aug 12, 2010, at 11:32 AM, tedd wrote: > At 10:56 AM -0400 8/12/10, Bastien Koert wrote: >> However, the data must be stored in an encrypted format and it must be >> transmitted via SSL. We do it that way (taking both a hash for >> searching for the ssn and the encrypted form) and haven't had any >> issues as yet. > > The data will be encrypted and only accessible behind an SSL via an > authorization process -- that's given. > > I have given some thought about searching the database for encrypted SS#'s > and have been perplexed as how to do that. > > For searching standard fields, it's a piece of cake to use %LIKE%. For > example, let's say the investigator has a piece of paper that has the number > "393" on it and want's to search the database for all phone numbers that > contain "393" -- he could use %LIKE% and that would produce 517-393-, > 393-123-4567, 818-122-4393 and so on. That's neat! > > However, if the field is encrypted, then how do you preform a partial search > on that? You can't encrypt the search string and use that because you need > the entire string. So, how do you solve that problem? > > If you hash the number of store the hash, then you can create a hashed search > string and use that. But again it doesn't work for partial %LIKE% searches. > For example, I couldn't search for "393" in a SS# -- I would have to search > for the complete SS#. > > So, how do you solve the %LIKE% problem with encryption and hashes? > >> The other thing to consider is that more and more states are looking to >> encrypt PII data >> (name, dob, ssn etc) for more security. > > I'm considering that as well, but that also raises more searching problems as > described above. > >> You could consider storing just the encrypted ssn and link data in a >> separate database, that would require a different logon to access when >> the data is required. > > Interesting -- I might also hash the foreign link. But I have to think about > that. > > Cheers, > > tedd > -- > --- > http://sperling.com/ > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > Would one option be to have a table of unencrypted SSN's with an encrypted id for the user. So you could search the SSN's and then connect them, however if the table was dumped you wouldn't be able to link it directly to the person (as it would just have a SSN and an id). Regards, -Josh Joshua Kehn | josh.k...@gmail.com http://joshuakehn.com
Re: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
At 10:56 AM -0400 8/12/10, Bastien Koert wrote: However, the data must be stored in an encrypted format and it must be transmitted via SSL. We do it that way (taking both a hash for searching for the ssn and the encrypted form) and haven't had any issues as yet. The data will be encrypted and only accessible behind an SSL via an authorization process -- that's given. I have given some thought about searching the database for encrypted SS#'s and have been perplexed as how to do that. For searching standard fields, it's a piece of cake to use %LIKE%. For example, let's say the investigator has a piece of paper that has the number "393" on it and want's to search the database for all phone numbers that contain "393" -- he could use %LIKE% and that would produce 517-393-, 393-123-4567, 818-122-4393 and so on. That's neat! However, if the field is encrypted, then how do you preform a partial search on that? You can't encrypt the search string and use that because you need the entire string. So, how do you solve that problem? If you hash the number of store the hash, then you can create a hashed search string and use that. But again it doesn't work for partial %LIKE% searches. For example, I couldn't search for "393" in a SS# -- I would have to search for the complete SS#. So, how do you solve the %LIKE% problem with encryption and hashes? The other thing to consider is that more and more states are looking to encrypt PII data (name, dob, ssn etc) for more security. I'm considering that as well, but that also raises more searching problems as described above. You could consider storing just the encrypted ssn and link data in a separate database, that would require a different logon to access when the data is required. Interesting -- I might also hash the foreign link. But I have to think about that. Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
On Thu, Aug 12, 2010 at 10:30 AM, tedd wrote: > At 2:51 PM +0100 8/12/10, Ashley Sheridan wrote: >> >> If you are storing the data in a DB, then I'd consider using different >> levels of access to that via different DB users, which should offer an extra >> layer of security in protecting the data. > > Of course, the routines I'm writing provide several levels of access for > different functions/job-duties. However, at some point there will be people > who will have access to SS# data. > > The real questions here are: > > 1. Is it lawful in the USA to store US SS# in an online database? > > 2. If it is lawful, then what security provisions are required? > > Cheers, > > tedd > > -- > --- > http://sperling.com/ > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > The worst part of that is that is varies by state (check the MA and NY laws as the most restrictive), there are no federal guidelines as yet. However, the data must be stored in an encrypted format and it must be transmitted via SSL. We do it that way (taking both a hash for searching for the ssn and the encrypted form) and haven't had any issues as yet. Some clients are simply refusing to store SSNs for any person in the system where the address is in MA. The other thing to consider is that more and more states are looking to encrypt PII data (name, dob, ssn etc) for more security. You could consider storing just the encrypted ssn and link data in a separate database, that would require a different logon to access when the data is required. -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
At 2:51 PM +0100 8/12/10, Ashley Sheridan wrote: If you are storing the data in a DB, then I'd consider using different levels of access to that via different DB users, which should offer an extra layer of security in protecting the data. Of course, the routines I'm writing provide several levels of access for different functions/job-duties. However, at some point there will be people who will have access to SS# data. The real questions here are: 1. Is it lawful in the USA to store US SS# in an online database? 2. If it is lawful, then what security provisions are required? Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
At 2:59 PM +0100 8/12/10, Richard Quadling wrote: On 12 August 2010 14:45, tedd wrote: At 5:30 PM -0700 8/11/10, Daevid Vincent wrote: > -Original Message- 2. Were told it was a social security number (i.e., in the form of 123-45-6789). Stop. Why are you even contemplating storing SS# ?? Why hold the SSN (encrypted or otherwise). If you hold it encrypted, then the keys have to exist somewhere and that will cost you something. Why not hold a non reversible hash? That way you can't determine the SSN, but someone posing as the SSN holder presents their SSN, you run it through the same hash routine and compare the hashes. If they match, then the SSN is valid. If not, then not. Don't store the SSN would be my way. The same way you don't store passwords using a reversible technology. Richard Quadling. Richard: That would work (and does work) for storing things like passwords. I have routines where users logon and enters their password where I md5() it and store the hash in a database. The next time the user logs on, the script again runs the password through the md5() and compares results with the database. If there is a match, then access is provided. If not, then it fails. All of this without me (the database) knowing what the password actually was. However in this case, the authorized users of database must be able to see the actual SS# for their processing. Seeing a md(), or any other hash, would not serve the purpose of having the numbers in the database. Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
On 12 August 2010 14:45, tedd wrote: > At 5:30 PM -0700 8/11/10, Daevid Vincent wrote: >> >> > -Original Message- >>> >>> 2. Were told it was a social security number >>> (i.e., in the form of 123-45-6789). >> >> Stop. >> >> Why are you even contemplating storing SS# ?? Why hold the SSN (encrypted or otherwise). If you hold it encrypted, then the keys have to exist somewhere and that will cost you something. Why not hold a non reversible hash? That way you can't determine the SSN, but someone posing as the SSN holder presents their SSN, you run it through the same hash routine and compare the hashes. If they match, then the SSN is valid. If not, then not. Don't store the SSN would be my way. The same way you don't store passwords using a reversible technology. -- Richard Quadling. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
On Thu, 2010-08-12 at 09:45 -0400, tedd wrote: > At 5:30 PM -0700 8/11/10, Daevid Vincent wrote: > > > -Original Message- > >> 2. Were told it was a social security number > >> (i.e., in the form of 123-45-6789). > > > >Stop. > > > >Why are you even contemplating storing SS# ?? > > Daevid et al: > > Why? Because my client wants to store SS numbers on their online > system to aid them in their collection business. > > You see, the client in this case is not asking people for their SS > numbers, but rather trying to collect unpaid debts. Their clients > (i.e., creditors) have provided them debtor data, which may/may not > include SS numbers. > > My current thoughts are that the entire process will be behind a > password protected section of a web site where only the people > working for the firm will have access. The point of the system will > be to aid collectors in their collection efforts and to allow them to > conduct business anywhere they can find Internet access. > > Of course, this will not stop employees from abusing the data, but > that possibility also exist in the hard-copy only office as well -- > that's a criminal act and will be handled accordingly. The difference > here is that the data can be accessed online via password > authorization. Is that too easy? > > My effort here with my "Encryption/Decryption Question" is to focus > on the event that the web site may hacked and access to the database > is provided to an intruder. In such case, then the SS numbers > residing there should be encrypted and that was my current quest to > resolve. > > Now, if federal law prohibits storing SS numbers in an online > database that's accessible via password authorization then that's > "end-of-story". I'll simply tell the client that federal law > prohibits such practice and that will be the end of it -- it makes no > difference to me. > > However, if the practice of storing SS number online is not > prohibited by law, then what are the appropriate "due diligence" > steps necessary to protect such data? > > Cheers, > > tedd > > -- > --- > http://sperling.com/ > If you are storing the data in a DB, then I'd consider using different levels of access to that via different DB users, which should offer an extra layer of security in protecting the data. In the UK, I believe you are allowed to store details such as these in an online system, but the whole server itself has to pass a PCI check, which ensures that various server modules are up-to-date, etc, which should hopefully block another hole or two. Thanks, Ash http://www.ashleysheridan.co.uk
RE: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question
At 5:30 PM -0700 8/11/10, Daevid Vincent wrote: > -Original Message- 2. Were told it was a social security number (i.e., in the form of 123-45-6789). Stop. Why are you even contemplating storing SS# ?? Daevid et al: Why? Because my client wants to store SS numbers on their online system to aid them in their collection business. You see, the client in this case is not asking people for their SS numbers, but rather trying to collect unpaid debts. Their clients (i.e., creditors) have provided them debtor data, which may/may not include SS numbers. My current thoughts are that the entire process will be behind a password protected section of a web site where only the people working for the firm will have access. The point of the system will be to aid collectors in their collection efforts and to allow them to conduct business anywhere they can find Internet access. Of course, this will not stop employees from abusing the data, but that possibility also exist in the hard-copy only office as well -- that's a criminal act and will be handled accordingly. The difference here is that the data can be accessed online via password authorization. Is that too easy? My effort here with my "Encryption/Decryption Question" is to focus on the event that the web site may hacked and access to the database is provided to an intruder. In such case, then the SS numbers residing there should be encrypted and that was my current quest to resolve. Now, if federal law prohibits storing SS numbers in an online database that's accessible via password authorization then that's "end-of-story". I'll simply tell the client that federal law prohibits such practice and that will be the end of it -- it makes no difference to me. However, if the practice of storing SS number online is not prohibited by law, then what are the appropriate "due diligence" steps necessary to protect such data? Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
