February 18, 2019 at 10:22 am
patrickmcginnis59 10839 - Monday, February 18, 2019 10:04 AMScottPletcher - Monday, February 18, 2019 8:18 AMpatrickmcginnis59 10839 - Monday, February 18, 2019 7:16 AMI'd just like to thank Joe for fighting the good fight. I'm seeing HORRIFIC assertions being posted by admittedly experienced folks.Heck anybody trying to tell me they can't use computers to validate a credit card number (I mean EVERY VALIDATION THAT IS AVAILABLE ACCORDING TO THE SPEC), well I'd have to show them the door. Sorry, but it needs to be said. There is no excuse for this. I just hope I'm mistaken in how I'm reading such posts.
No one's ever said that. Like Joe, you're fighting a straw man. Of course you do any/all verifications needed for any cc (or other) value going into the db. But that doesn't affect the physical method I use internally to store that value. I'm surprised to see so many claims that it somehow should. Indeed, as with a date and a lot of other data types, the type itself helps verify the value.
If I store a cc as numeric, I don't have to valid that every char entered is numeric. I can CAST that numeric value to char or binary or any other format needed for validation. Why on earth should the validation process prevent me from saving bytes and processing overhead by storing the value as numeric rather than char??
Think back to what we do with cc's as an example.
We validate the number, then we authorize the account, then we authorize the transaction.
Why would we validate that the credit card number is all numeric without running the checksums? At the very time we're validating each digit, we could also be calculating the checksums. Why would we spread the validation across multiple systems. Its certainly possible that we could make this work, but we're talking about the very subjective matter about how we put together systems. I get it, this is not database administrator concerns, but its certainly on our todo list as computer professionals.
Its fine if you want to subsequently "compress" the data you are storing, but the data model isn't supposed to be concerned with that.
I get it, I could program everything with fortran and it will work fine, probably beat the snot out of SQL Server once I programmed the zillion lines needed to produce my own transactional storage that will use today's multiple cpu architectures. But that possibility is not what the relational model is about or even sensible application design for that matter.
Using a credit card number as a primary key would be a bad idea. For one thing there are PCI security implications of storing credit card numbers. One solution is to generate a GUID for each credit card and have only one table that stores the credit card number (encrypted) along with the GUID for lookup. So the primary key for this table would be the generated GUID which is not much different from an identity column.
February 18, 2019 at 10:31 am
Jonathan AC Roberts - Monday, February 18, 2019 10:22 AMpatrickmcginnis59 10839 - Monday, February 18, 2019 10:04 AMScottPletcher - Monday, February 18, 2019 8:18 AMpatrickmcginnis59 10839 - Monday, February 18, 2019 7:16 AMI'd just like to thank Joe for fighting the good fight. I'm seeing HORRIFIC assertions being posted by admittedly experienced folks.Heck anybody trying to tell me they can't use computers to validate a credit card number (I mean EVERY VALIDATION THAT IS AVAILABLE ACCORDING TO THE SPEC), well I'd have to show them the door. Sorry, but it needs to be said. There is no excuse for this. I just hope I'm mistaken in how I'm reading such posts.
No one's ever said that. Like Joe, you're fighting a straw man. Of course you do any/all verifications needed for any cc (or other) value going into the db. But that doesn't affect the physical method I use internally to store that value. I'm surprised to see so many claims that it somehow should. Indeed, as with a date and a lot of other data types, the type itself helps verify the value.
If I store a cc as numeric, I don't have to valid that every char entered is numeric. I can CAST that numeric value to char or binary or any other format needed for validation. Why on earth should the validation process prevent me from saving bytes and processing overhead by storing the value as numeric rather than char??
Think back to what we do with cc's as an example.
We validate the number, then we authorize the account, then we authorize the transaction.
Why would we validate that the credit card number is all numeric without running the checksums? At the very time we're validating each digit, we could also be calculating the checksums. Why would we spread the validation across multiple systems. Its certainly possible that we could make this work, but we're talking about the very subjective matter about how we put together systems. I get it, this is not database administrator concerns, but its certainly on our todo list as computer professionals.
Its fine if you want to subsequently "compress" the data you are storing, but the data model isn't supposed to be concerned with that.
I get it, I could program everything with fortran and it will work fine, probably beat the snot out of SQL Server once I programmed the zillion lines needed to produce my own transactional storage that will use today's multiple cpu architectures. But that possibility is not what the relational model is about or even sensible application design for that matter.
Using a credit card number as a primary key would be a bad idea. For one thing there are PCI security implications of storing credit card numbers. One solution is to generate a GUID for each credit card and have only one table that stores the credit card number (encrypted) along with the GUID for lookup. So the primary key for this table would be the generated GUID which is not much different from an identity column.
Cc nums are not typical anyway, they'd almost certainly end up stored as varbinary because of the compression method(s) used on them.
Again, how you do the checking is irrelevant to the underlying storage format. That was my main point.
As to keys, agreed, you should certainly never use cc as a key, least of all because they also expire and thus change. But a bigint would be easier to deal with than a guid. Add a tinyint site origination code/value if you need to, to make the values guaranteed unique (or use the very high portions of the numeric values as a unique origination code).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 18, 2019 at 11:47 am
Jonathan AC Roberts - Monday, February 18, 2019 10:22 AMpatrickmcginnis59 10839 - Monday, February 18, 2019 10:04 AMScottPletcher - Monday, February 18, 2019 8:18 AMpatrickmcginnis59 10839 - Monday, February 18, 2019 7:16 AMI'd just like to thank Joe for fighting the good fight. I'm seeing HORRIFIC assertions being posted by admittedly experienced folks.Heck anybody trying to tell me they can't use computers to validate a credit card number (I mean EVERY VALIDATION THAT IS AVAILABLE ACCORDING TO THE SPEC), well I'd have to show them the door. Sorry, but it needs to be said. There is no excuse for this. I just hope I'm mistaken in how I'm reading such posts.
No one's ever said that. Like Joe, you're fighting a straw man. Of course you do any/all verifications needed for any cc (or other) value going into the db. But that doesn't affect the physical method I use internally to store that value. I'm surprised to see so many claims that it somehow should. Indeed, as with a date and a lot of other data types, the type itself helps verify the value.
If I store a cc as numeric, I don't have to valid that every char entered is numeric. I can CAST that numeric value to char or binary or any other format needed for validation. Why on earth should the validation process prevent me from saving bytes and processing overhead by storing the value as numeric rather than char??
Think back to what we do with cc's as an example.
We validate the number, then we authorize the account, then we authorize the transaction.
Why would we validate that the credit card number is all numeric without running the checksums? At the very time we're validating each digit, we could also be calculating the checksums. Why would we spread the validation across multiple systems. Its certainly possible that we could make this work, but we're talking about the very subjective matter about how we put together systems. I get it, this is not database administrator concerns, but its certainly on our todo list as computer professionals.
Its fine if you want to subsequently "compress" the data you are storing, but the data model isn't supposed to be concerned with that.
I get it, I could program everything with fortran and it will work fine, probably beat the snot out of SQL Server once I programmed the zillion lines needed to produce my own transactional storage that will use today's multiple cpu architectures. But that possibility is not what the relational model is about or even sensible application design for that matter.
Using a credit card number as a primary key would be a bad idea. For one thing there are PCI security implications of storing credit card numbers. One solution is to generate a GUID for each credit card and have only one table that stores the credit card number (encrypted) along with the GUID for lookup. So the primary key for this table would be the generated GUID which is not much different from an identity column.
No generally accepted theory of programming advocates disclosing sensitive data to unauthorized entities 🙂
February 18, 2019 at 11:54 am
ScottPletcher - Monday, February 18, 2019 10:31 AMJonathan AC Roberts - Monday, February 18, 2019 10:22 AMpatrickmcginnis59 10839 - Monday, February 18, 2019 10:04 AMScottPletcher - Monday, February 18, 2019 8:18 AMpatrickmcginnis59 10839 - Monday, February 18, 2019 7:16 AMI'd just like to thank Joe for fighting the good fight. I'm seeing HORRIFIC assertions being posted by admittedly experienced folks.Heck anybody trying to tell me they can't use computers to validate a credit card number (I mean EVERY VALIDATION THAT IS AVAILABLE ACCORDING TO THE SPEC), well I'd have to show them the door. Sorry, but it needs to be said. There is no excuse for this. I just hope I'm mistaken in how I'm reading such posts.
No one's ever said that. Like Joe, you're fighting a straw man. Of course you do any/all verifications needed for any cc (or other) value going into the db. But that doesn't affect the physical method I use internally to store that value. I'm surprised to see so many claims that it somehow should. Indeed, as with a date and a lot of other data types, the type itself helps verify the value.
If I store a cc as numeric, I don't have to valid that every char entered is numeric. I can CAST that numeric value to char or binary or any other format needed for validation. Why on earth should the validation process prevent me from saving bytes and processing overhead by storing the value as numeric rather than char??
Think back to what we do with cc's as an example.
We validate the number, then we authorize the account, then we authorize the transaction.
Why would we validate that the credit card number is all numeric without running the checksums? At the very time we're validating each digit, we could also be calculating the checksums. Why would we spread the validation across multiple systems. Its certainly possible that we could make this work, but we're talking about the very subjective matter about how we put together systems. I get it, this is not database administrator concerns, but its certainly on our todo list as computer professionals.
Its fine if you want to subsequently "compress" the data you are storing, but the data model isn't supposed to be concerned with that.
I get it, I could program everything with fortran and it will work fine, probably beat the snot out of SQL Server once I programmed the zillion lines needed to produce my own transactional storage that will use today's multiple cpu architectures. But that possibility is not what the relational model is about or even sensible application design for that matter.
Using a credit card number as a primary key would be a bad idea. For one thing there are PCI security implications of storing credit card numbers. One solution is to generate a GUID for each credit card and have only one table that stores the credit card number (encrypted) along with the GUID for lookup. So the primary key for this table would be the generated GUID which is not much different from an identity column.
Cc nums are not typical anyway, they'd almost certainly end up stored as varbinary because of the compression method(s) used on them.
Again, how you do the checking is irrelevant to the underlying storage format. That was my main point.
As to keys, agreed, you should certainly never use cc as a key, least of all because they also expire and thus change. But a bigint would be easier to deal with than a guid. Add a tinyint site origination code/value if you need to, to make the values guaranteed unique (or use the very high portions of the numeric values as a unique origination code).
Ok say I have a customer who uses a credit card to purchase, what do I send to the processor if not the card number? Now since I'm not personally responsible for any credit cards, tell me what key does the processor use to update the transaction that was generated and associated with the credit card? I don't personally do credit card processing or work at a bank, so for the bankers who post here, what do we actually do when processing these transactions?
edit: also, when I talk about Abraham Lincoln, the 16th president of the United States, that's enough to identify him. This didn't change once he "expired" so to speak LOL
February 18, 2019 at 1:10 pm
I think we need to back up and get some terminology corrected here. We say that we have a concatenation, it's a string with an ordering and there doesn't have to be any relationship among the components. In theory, I can put my political affiliation and my shoe size and a concatenated string in one column (Libertarian, 8.5B).
If we have a compound, a composite, vector, or whatever encoding, then things are different. For example (latitude, longitude) is an ordered pair. One component without the other has no meaning and will not locate a point on the globe. In the case of the VIN, its components have dependencies. There is no such thing as a "Lamborghini" and "Civic" manufacturer and model components of the composite; we simply assume that the manufacturer should have been "Honda" and that somebody really screwed up. A concatenation can be decomposed meaningfully; structured encodings cannot be decomposed. However, you can filter them. One of my filters might be "Lamborghini" at the manufacturer level. It is subordinated in the hierarchy and inherit the manufacturer in the filter.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 18, 2019 at 1:15 pm
>> Cc nums are not typical anyway, they'd almost certainly end up stored as varbinary because of the compression method(s) used on them. <<
I thought the credit cards are supposed to be encrypted, using industry standards ...
Please post DDL and follow ANSI/ISO standards when asking for help.
February 18, 2019 at 1:32 pm
jcelko212 32090 - Monday, February 18, 2019 1:15 PM>> Cc nums are not typical anyway, they'd almost certainly end up stored as varbinary because of the compression method(s) used on them. <<I thought the credit cards are supposed to be encrypted, using industry standards ...
I would think banks or other credit card issuers have to use this as a key of some sort, but I'm guessing that since the credit card companies are a world unto themselves, its not exactly easy to find out how they do it. With the folks who might need to save credit info for recurring purposes, apparently a good method is for them to process the card ONCE with a processor (and possibly keeping the last 4 for familiarity purposes with the customer), and then receive and store a token from the processor for future recurring purchases, this token not only represents the card but in fact a unique card+merchant key so that the token is only valid coming from the merchant who received the token. Pretty interesting!
February 18, 2019 at 1:46 pm
>> Btw, the US fed govt already announced, some time back, that it intends to move away from DUNS numbers to a more open system. Best of luck to all DUNS-modeled folks who have to go change this key in every table in which it appears! <<
I subscribe to a hell of a lot of industry-standard websites, but I never saw that. Damn! Can you give me some links? Last time DUNS mattered to me that was a few years back here in Austin for a contract at Dell computers, and at a company manufactures aircraft parts in Ohio. Last identifier I really gotta charge out of the is not a job related, was the ISAN because I had worked at the Belgian national television and radio network a gazillion years ago and we were desperate to find some way to identify individual programs.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 18, 2019 at 1:52 pm
patrickmcginnis59 10839 - Monday, February 18, 2019 1:32 PMjcelko212 32090 - Monday, February 18, 2019 1:15 PM>> Cc nums are not typical anyway, they'd almost certainly end up stored as varbinary because of the compression method(s) used on them. <<I thought the credit cards are supposed to be encrypted, using industry standards ...
I would think banks or other credit card issuers have to use this as a key of some sort, but I'm guessing that since the credit card companies are a world unto themselves, its not exactly easy to find out how they do it. With the folks who might need to save credit info for recurring purposes, apparently a good method is for them to process the card ONCE with a processor (and possibly keeping the last 4 for familiarity purposes with the customer), and then receive and store a token from the processor for future recurring purchases, this token not only represents the card but in fact a unique card+merchant key so that the token is only valid coming from the merchant who received the token. Pretty interesting!
That sounds like a reasonable method to reduce overhead. As to keys, the full cc number is not actually used by the vast majority of employees to do lookups. It's too insecure. Instead, as you noted for vendors, you give the cc company the last 4 digits and other specific identifying value(s), but not the full card#. They don't want the employees to see full card#s, for obvious security reasons.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 18, 2019 at 1:56 pm
ScottPletcher - Monday, February 18, 2019 1:52 PMpatrickmcginnis59 10839 - Monday, February 18, 2019 1:32 PMjcelko212 32090 - Monday, February 18, 2019 1:15 PM>> Cc nums are not typical anyway, they'd almost certainly end up stored as varbinary because of the compression method(s) used on them. <<I thought the credit cards are supposed to be encrypted, using industry standards ...
I would think banks or other credit card issuers have to use this as a key of some sort, but I'm guessing that since the credit card companies are a world unto themselves, its not exactly easy to find out how they do it. With the folks who might need to save credit info for recurring purposes, apparently a good method is for them to process the card ONCE with a processor (and possibly keeping the last 4 for familiarity purposes with the customer), and then receive and store a token from the processor for future recurring purchases, this token not only represents the card but in fact a unique card+merchant key so that the token is only valid coming from the merchant who received the token. Pretty interesting!
That sounds like a reasonable method to reduce overhead. As to keys, the full cc number is not actually used by the vast majority of employees to do lookups. It's too insecure. Instead, as you noted for vendors, you give the cc company the last 4 digits and other specific identifying value(s), but not the full card#. They don't want the employees to see full card#s, for obvious security reasons.
I think that makes sense. I can't for the life of me find out how the credit card issuers do things, they're the ones who would seem to need to lookup with the card number, but like I said, credit card issuers are probably much fewer in number so I suspect they have industry standards they follow, that I'll never learn unless I have a bunch of money that I can loan out 🙂
Viewing 10 posts - 61 through 69 (of 69 total)
You must be logged in to reply to this topic. Login to reply