{"id":80314,"date":"2018-08-08T18:35:44","date_gmt":"2018-08-08T18:35:44","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=80314"},"modified":"2026-05-08T10:24:26","modified_gmt":"2026-05-08T10:24:26","slug":"spoofing-data-convincingly-credit-cards","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/spoofing-data-convincingly-credit-cards\/","title":{"rendered":"Generating Fake Credit Card Numbers in SQL Server with the Luhn Algorithm (for Test Data)"},"content":{"rendered":"

For development and test databases that include payment-card data, using real card numbers is a compliance nightmare (PCI DSS) and a liability. The alternative is generating fake credit card numbers that look realistic – correct length for the brand, valid-looking prefix ranges, and passing the Luhn checksum that payment systems use as a first-pass validity check. <\/b><\/p>\n

This article shows how to generate Luhn-valid fake card numbers in SQL Server: implementing the Luhn algorithm as a T-SQL function, using brand-appropriate prefix ranges (Visa starts with 4, Mastercard with 5 or 2, Amex with 34 or 37, Discover with 6011 or 65), and a BigRandomNumberView for producing batches of cards at once. <\/b><\/p>\n

(The generated numbers are unambiguously fake (don’t match real issuing bank BINs) but will survive input validation in test scenarios that check only format and checksum.)<\/b><\/em><\/p>\n

I haven\u2019t seen a SQL Server table with real unencrypted credit card numbers for several years, and I don\u2019t know of any good reasons to have them stored that way. However, I\u2019ve needed them in the past for testing a web application that had to take credit card details. Generating credit cards in a way that conforms to a particular distribution is reasonably easy in SQL Server, though. The only difficult bit is the fact that there is a validation checksum. Otherwise it is a good example of how to solve the problem of spoofing the more specialist types of data.<\/p>\n

Imagine that we need to create fake credit cards from a fairly large table. We don\u2019t want, and can\u2019t get, the real information, but our fake credit cards have to conform to the distribution of the real data.<\/p>\n

All we need is an aggregation of the real data that tells us the distribution. Fortunately, the first six digits of a credit Card has only the details of the bank, currency and so on. We aren\u2019t going to associate the fake data with an identifiable person. We are pretty safe. We are just generating a list of credit cards. We will draw from the list at random and not associate the randomly-generated suffix with the original person.<\/p>\n

We start with the real data. OK, it isn\u2019t real data, but we\u2019ll pretend that it is. The data I used is provided with this blog so you can try it out. It conforms roughly with the proportion of providers that you’ll see in the States.<\/p>\n

<\/p>\n

\u2026 A lot of rows omitted\u00a0 here \u2026<\/p>\n

<\/p>\n

We just need to know the distribution. You must imagine that these cards are from a production system that you will never see. In reality, you would just receive from the Ops people the Generation Table #GenerationTable<\/strong> so you wouldn\u2019t need access to the actual data<\/p>\n

\/* We create a table that gives us the distribution of the bank prefix of the number, \nfor each length of card.  This is the number of times that each 6-character Bank ID \nprefix occurs with a particular length It also calculates the range so as to be able\nto pick a random number right across the range. *\/\n\nCREATE table #GenerationTable (Prefix char(6), Thelength INT, TheFrequency int, TheRange int)\nINSERT INTO #GenerationTable (Prefix, Thelength, TheFrequency, TheRange)\nSELECT f.IIN, --the prefix\n f.TheLength, --the length ofd the credit card\n f.frequency, --how often it occurs,\n Sum(f.frequency) OVER (ORDER BY f.IIN,TheLength)--the cumulative total (giving the range)\n  FROM\n    (\n    SELECT Count(*) AS frequency, Len(CardNumber) AS TheLength,\n\t       Left(CreditCards.cardNumber, 6) AS IIN\n      FROM CreditCards\n      GROUP BY Left(CreditCards.cardNumber, 6),Len(CardNumber)\n    ) AS f(frequency, TheLength, IIN);\nGO\n<\/pre>\n
Now we can use the Generation Table to provide valid credit cards with the correct distribution. Note that, by this method, you will only get credit card numbers with the prefixes that come from the generation table.<\/p>\n
CREATE TABLE SpoofedCreditCards(Card CHAR(20))\nDECLARE  @topOfRange INT = (SELECT Sum(Thefrequency) FROM #GenerationTable)\nINSERT INTO SpoofedCreditCards(Card)\nSELECT dbo.ValidCardNumber(Prefix,TheLength) FROM #GenerationTable \n cross JOIN Random\n WHERE Convert(INT,Number*@topOfRange)+1 BETWEEN (TheRange-TheFrequency)+1\n AND TheRange\n<\/pre>\n
With this table we can quickly throw together a routine that provides our friends in AdventureWorks (or your spoof customer names) with credit cards. We use the ‘zip’technique.<\/p>\n
SELECT TheName.Fullname, TheCard.card\n  FROM\n    (\n    SELECT Coalesce(Person.Title + ' ', '') + Person.FirstName\n           + Coalesce(' ' + Person.MiddleName + ' ', ' ') + Person.LastName\n           + Coalesce(' ' + Person.Suffix, ''),\n      Row_Number() OVER (ORDER BY NewId() ASC)\n      FROM AdventureWorks2016.Person.Person\n    ) AS TheName(Fullname, TheKey)\n    INNER JOIN\n      (\n      SELECT SpoofedCreditCards.Card, Row_Number() OVER (ORDER BY NewId())\n        FROM SpoofedCreditCards\n      ) AS TheCard(card, TheKEY)\n      ON TheName.TheKey = TheCard.TheKEY;\n<\/pre>\n
<\/p>\n
To do this, you would need to start with a view that gives you a twenty digit number when combined with the six-digit prefix<\/p>\n
CREATE VIEW TwentyDigitNumber \/* fourteen digit strings are returned *\/ \nAS\nSELECT Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1))\n       + Char(Ascii('0') + (Rand() * 9 + 1)) AS numbers;\nGO<\/pre>\n
Now, we need the function that produces the spoof credit card that is correct according to Luhn\u2019s algorithm<\/a><\/p>\n
CREATE OR ALTER FUNCTION dbo.ValidCardNumber \n  (@ProviderPrefix CHAR(6),--the six characters that denote the provider \n   @length INT --between 13 and 19 digits including the provider\n\/**\nSummary: >\n  This function returns a valid credit card number, including the\n  provider string or ANSI prefix supplied as a parameter. \n  It is valid only in the sense that the card will past the Luhn \n  Checksum test. It is generated by random numbers so is unlikely\n  to be a genuine credit card number!\n  The Luhn Algorithm (Modulus 10 Algorithm) is only a checksum formula\n  (ISO\/IEC 7812-1) that is an extra verification for human errors.\n  The ANSI prefix (AKA the BIN, IIN,or BAN and PAN prefix) is the \n  \u201cprimary account number\u201d\n  for credit card processing. Except for the first digit, the contents\n  varies from provider to provider. The information cannot be used to\n  identify a personal account but reveals the bank identity and country of\n  issue and could indicate the currency being used. This information \n  is company confidential. A very obscure prefix could potentially\n  identify an individual when joined with other data but this is unlikely\n  e.g.\n  540221 = ANZ National Bank ANZ Low Interest MasterCard Credit Card\n  490603 = BC VISA Credit Card issued by Industrial Bank of Korea\n  374671 = Blue American Express Card\n  630490 = Bank of Ireland Laser\/Maestro debit card\n  \n  Author: Phil Factor\nDate: 06\/08\/2018\nDatabase: PhilFactor\nExamples:\n   - Select * from stuff\n   - Select top 100 percent stuff\nReturns: >\n  nothing\n**\/\n)\nRETURNS VARCHAR(20) --between 13 and 19 digits\nAS\n  BEGIN\n    DECLARE @wideNumber CHAR(20), @checksum INT, @lastdigit INT,\n      @AlteredDigit INT;\n    IF @length > 20 OR @length < 0 SELECT @length = 16;\n    IF @ProviderPrefix LIKE '%[^0-9]%' SELECT @ProviderPrefix = numbers FROM TwentyDigitNumber;\n    SELECT @wideNumber =\n      CASE WHEN @length % 2 = 1 THEN '0' ELSE '' END + @ProviderPrefix\n      + Left(numbers, @length - Len(@ProviderPrefix)) + '00000000000000000000'\n      FROM TwentyDigitNumber;\n    SELECT @checksum =\n      CASE WHEN @wideNumber LIKE '%[^0-9]%' THEN -2\n        WHEN @wideNumber IS NULL THEN -1 ELSE\n            + 2 * cast(substring(@wideNumber, 1, 1) AS TINYINT) \/ 10\n            + 2 * cast(substring(@wideNumber, 1, 1) AS TINYINT) % 10\n            + cast(substring(@wideNumber, 2, 1) AS TINYINT)\n            + 2 * cast(substring(@wideNumber, 3, 1) AS TINYINT) \/ 10\n            + 2 * cast(substring(@wideNumber, 3, 1) AS TINYINT) % 10\n            + cast(substring(@wideNumber, 4, 1) AS TINYINT)\n            + 2 * cast(substring(@wideNumber, 5, 1) AS TINYINT) \/ 10\n            + 2 * cast(substring(@wideNumber, 5, 1) AS TINYINT) % 10\n            + cast(substring(@wideNumber, 6, 1) AS TINYINT)\n            + 2 * cast(substring(@wideNumber, 7, 1) AS TINYINT) \/ 10\n            + 2 * cast(substring(@wideNumber, 7, 1) AS TINYINT) % 10\n            + cast(substring(@wideNumber, 8, 1) AS TINYINT)\n            + 2 * cast(substring(@wideNumber, 9, 1) AS TINYINT) \/ 10\n            + 2 * cast(substring(@wideNumber, 9, 1) AS TINYINT) % 10\n            + cast(substring(@wideNumber, 10, 1) AS TINYINT)\n            + 2 * cast(substring(@wideNumber, 11, 1) AS TINYINT) \/ 10\n            + 2 * cast(substring(@wideNumber, 11, 1) AS TINYINT) % 10\n            + cast(substring(@wideNumber, 12, 1) AS TINYINT)\n            + 2 * cast(substring(@wideNumber, 13, 1) AS TINYINT) \/ 10\n            + 2 * cast(substring(@wideNumber, 13, 1) AS TINYINT) % 10\n            + cast(substring(@wideNumber, 14, 1) AS TINYINT)\n            + 2 * cast(substring(@wideNumber, 15, 1) AS TINYINT) \/ 10\n            + 2 * cast(substring(@wideNumber, 15, 1) AS TINYINT) % 10\n            + cast(substring(@wideNumber, 16, 1) AS TINYINT)\n            + 2 * cast(substring(@wideNumber, 17, 1) AS TINYINT) \/ 10\n            + 2 * cast(substring(@wideNumber, 17, 1) AS TINYINT) % 10\n            + cast(substring(@wideNumber, 18, 1) AS TINYINT)\n            + 2 * cast(substring(@wideNumber, 19, 1) AS TINYINT) \/ 10\n            + 2 * cast(substring(@wideNumber, 19, 1) AS TINYINT) % 10\n            + cast(substring(@wideNumber, 20, 1) AS TINYINT) END;\nSELECT @AlteredDigit = CASE WHEN @length % 2 = 1 THEN @length + 1 ELSE @length END;\nSELECT @lastdigit = Cast(Substring(@wideNumber, @AlteredDigit, 1) AS TINYINT);\nIF (@checksum % 10 <= @lastdigit) \n  SELECT @lastdigit = @lastdigit - (@checksum % 10);\nELSE \n  SELECT @lastdigit = @lastdigit + (10 - (@checksum % 10));\nIF @checksum % 10 <> 0\n    SELECT @wideNumber =\n      Stuff(@wideNumber, @AlteredDigit, 1, Char(Ascii('0') + @lastdigit));\nRETURN Substring(@wideNumber, 1 + (@length % 2), @length);\n  END;\n\nGO<\/pre>\n
And finally, you have a view that contains just the number of random numbers that you need. I\u2019ve bulked this up to 4000 just to test out and make sure the routine performed well. The source is with the blog.<\/p>\n
<\/p>\n
\u2026 and so on down to the end \u2026 (it avoids iteration and is fast)<\/p>\n
<\/p>\n
We can now compare the distribution of the old and new data, using the routine published here in \u2018Visual Checks on How Data is Distributed in SQL Server\u2019<\/a><\/p>\n
DECLARE @FirstVariable IndependentVariable; --a simple table of numbers\nINSERT INTO @FirstVariable\n  (number)\n  SELECT Convert(FLOAT,(Left(CreditCards.cardnumber,6)))\n  FROM CreditCards \nSELECT ColumnHistogram.line AS 'Original Modified date'\n  FROM dbo.ColumnHistogram(@FirstVariable)\n  ORDER BY ColumnHistogram.y DESC;\nGO\nDECLARE @SecondVariable IndependentVariable; --a simple table of numbers\nINSERT INTO @SecondVariable\n  (number)\n  SELECT Convert(FLOAT,Left(Card,6))\n  FROM  SpoofedCreditCards;\nSELECT ColumnHistogram.line AS 'Spoofed Modified date'\n  FROM dbo.ColumnHistogram(@SecondVariable)\n  ORDER BY ColumnHistogram.y DESC;\nGO<\/pre>\n
<\/p>\n
Can we improve on this in order to get a smoother distribution of provider strings? At the moment we can\u2019t create a six-digit provider string that isn\u2019t in the original data. To do so isn\u2019t easy, because each provider uses the string in a different way, and not every combination of digit is used even for the individual provider. It would require maintenance as well because provider strings can come and so, or change.<\/p>\n
This following routine is designed for generating lists. You can check the routine that creates the card by using an existing routine, dbo.fnIsValidCard<\/strong>, that I wrote a while back in The Luhn Algorithm in SQL <\/a> .<\/p>\n
SELECT CreditCards.provider, CreditCards.cardNumber\n  FROM creditcards\n  WHERE dbo.fnIsValidCard(\n          dbo.ValidCardNumber(CreditCards.cardNumber, Len(CreditCards.cardNumber))\n        ) = 0;\n<\/pre>\n
Downloads<\/h2>\n
BigRandomNumberView<\/a><\/p>\n
SampleCreditCards<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Generate fake credit card numbers in SQL Server that pass Luhn checksum validation – useful for populating development and test databases with realistic-looking card data without using real PANs. Includes the Luhn algorithm implementation, prefix ranges for Visa, Mastercard, Amex, and Discover, and a BigRandomNumberView for producing many cards at once.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[68855,4816,4183,4252],"coauthors":[6813],"class_list":["post-80314","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-sql-provision","tag-sql-server-tsql","tag-t-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80314","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=80314"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80314\/revisions"}],"predecessor-version":[{"id":110411,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80314\/revisions\/110411"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=80314"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=80314"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=80314"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=80314"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}