- Luhn Valid – By default a base score of 50 is assigned for all Luhn valid matches. Anything that fails Luhn validation is discarded.
- Alpha Delimiters – A letter exists somewhere inside the number sequence Ex: 411a1111111111111
- Card + CVV – Match is followed by 3 digits Ex: 4111111111111111 123
- Phone Number – The match looks like it could be part of a phone number Ex: 1-4111111111111111
- Keywords – The text visa, card, etc. exists in the cell containing the match Ex: visa 4111111111111111
- Negative Keywords – Triple A membership numbers “aaa” are 16 digits and Luhn valid.
- Delimiters – The number of delimiters and the types. Ex Count:4 Types:2: 411-111-111-111/1111
- IIN Check – Does match contain a known IIN
Known Issues
- The script is single threaded. Once you start a scan the GUI will become unresponsive until it completes it’s run.
- The only way to stop a scan early is to kill the application.
- dataLoc was tested exclusively on Windows 10. There may be issues with the GUI on anything older.
References
https://blog.netspi.com/identifying-payment-cards-at-rest-going-beyond-the-key-word-search/ https://github.com/NetSPI/DataLoc [post_title] => dataLoc: A POC Tool for Finding Payment Cards Stored in MSSQL [post_excerpt] => [post_status] => publish [comment_status] => closed [ping_status] => closed [post_password] => [post_name] => dataloc-poc-tool-finding-payment-cards-stored-mssql [to_ping] => [pinged] => [post_modified] => 2021-06-08 21:52:35 [post_modified_gmt] => 2021-06-08 21:52:35 [post_content_filtered] => [post_parent] => 0 [guid] => https://netspiblogdev.wpengine.com/?p=7937 [menu_order] => 623 [post_type] => post [post_mime_type] => [comment_count] => 0 [filter] => raw ) [1] => WP_Post Object ( [ID] => 7466 [post_author] => 35 [post_date] => 2017-08-02 07:00:57 [post_date_gmt] => 2017-08-02 07:00:57 [post_content] =>In this blog, I’ll be discussing an approach for locating payment card numbers stored in MSSQL databases without relying on key words for data discovery.
To overcome the impracticality of pulling an entire database over the wire for advanced analysis, we’ll focus on using MSSQL’s native capability to filter out items that can’t contain cardholder data. This will greatly reduce the need for local checks.
The Pattern in the Number
Before we can begin, we need to understand what we’re looking for. For this exercise the focus will be on four card types; Visa, MasterCard, American Express, and Discover. These cards all have known lengths and starting sequences.
| Card Type | Length (digits) | Starting Sequence |
|---|---|---|
| Visa | 16 | 4 |
| MasterCard | 16 | 50-55, 222100-272099 |
| American Express | 15 | 34 or 37 |
| Discover | 16 | 6011 or 65 |
*Visa issued 13 digit cards in the past, but those cards are no longer valid.
*MasterCard started issuing 2-series BINs (222100-272099) January 2017. The code examples below have not been updated to support these numbers.
https://www.mastercard.us/en-us/issuers/get-support/2-series-bin-expansion.html
The first 6 digits of each of these cards make up the IIN (Issuer Identification Number) also known as the BIN (Bank Identification Number). Card issuers don’t generally provide official lists of IINs, but several community driven efforts to catalog this information exist. A good example can be found here: https://www.stevemorse.org/ssn/List_of_Bank_Identification_Numbers.html
The next 1-5 digits are known as the account range. The account range is followed by the customer identification number (CIN) and the check digit. Although the account range and CIN are going to be unknowns, the check digit is generated using a mathematical formula, and thus can be validated.
| IIN | Account Range & Customer Identification Number | Check Digit |
|---|---|---|
| 4111 11 | 11 1111 111 | 1 |
For more ideas about how to leverage the check digit, I recommend reading this post made by Karl Fosaaen. https://blog.netspi.com/cracking-credit-card-hashes-with-powershell/
MSSQL Filtering
We can’t positively identify payment cards with MSSQL’s native pattern matching capability, but we can prove several negatives that will allow us to eliminate tables, columns, and even individual cells that don’t contain payment cards.
The first thing we need to do is query the MSSQL server for a list of available databases. Filtering out default system databases is the first step in cutting down the amount of content we’ll look at locally.
SQL – List available databases
USE master; SELECT NAME FROM sysdatabases WHERE (NAME NOT LIKE 'distribution') AND (NAME NOT LIKE 'master') AND (NAME NOT LIKE 'model') AND (NAME NOT LIKE 'msdb') AND (NAME NOT LIKE 'publication') AND (NAME NOT LIKE 'reportserver') AND (NAME NOT LIKE 'reportservertempdb') AND (NAME NOT LIKE 'resource') AND (NAME NOT LIKE 'tempdb') ORDER BY NAME;
Excluded databases
(distribution, master, model, msdb, publication, reportserver, reportservertempdb, resource, tempdb)
The next step is to list the tables that may contain payment card data for the remaining databases; again, eliminating system defaults.
The SQL examples provided were created for the AdventureWorks2014 database, made freely available by Microsoft. https://msftdbprodsamples.codeplex.com/releases/view/125550
SQL - List tables
USE AdventureWorks2014; SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, i.rows FROM sys.tables AS t INNER JOIN sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2 WHERE (ROWS > 0) AND (t.name NOT LIKE 'syscolumns') AND (t.name NOT LIKE 'syscomments') AND (t.name NOT LIKE 'sysconstraints') AND (t.name NOT LIKE 'sysdepends') AND (t.name NOT LIKE 'sysfilegroups') AND (t.name NOT LIKE 'sysfiles') AND (t.name NOT LIKE 'sysforeignkeys') AND (t.name NOT LIKE 'sysfulltextcatalogs') AND (t.name NOT LIKE 'sysindexes') AND (t.name NOT LIKE 'sysindexkeys') AND (t.name NOT LIKE 'sysmembers') AND (t.name NOT LIKE 'sysobjects') AND (t.name NOT LIKE 'syspermissions') AND (t.name NOT LIKE 'sysprotects') AND (t.name NOT LIKE 'sysreferences') AND (t.name NOT LIKE 'systypes') AND (t.name NOT LIKE 'sysusers') ORDER BY TABLE_NAME;
Excluded Tables:
(syscolumns, syscomments, sysconstraints, sysdepends, sysfilegroups, sysfiles, sysforeignkeys, sysfulltextcatalogs, sysindexes, sysindexkeys, sysmembers, sysobjects, syspermissions, sysprotects, sysreferences, systypes, sysusers)
Now we’ll list columns for each table, this time filtering on column length and data type. For this example, we’ll focus on the “CreditCard” table.
SQL - List columns
USE AdventureWorks2014;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE
CHARACTER_MAXIMUM_LENGTH > 14 AND
DATA_TYPE NOT IN ('bigint','binary','bit','cursor','date','datetime','datetime2',
'datetimeoffset','float','geography','hierarchyid','image','int','money','real',
'smalldatetime','smallint','smallmoney','sql_variant','table','time','timestamp',
'tinyint','uniqueidentifier','varbinary','xml') AND
TABLE_NAME='CreditCard' OR
CHARACTER_MAXIMUM_LENGTH < 1 AND
DATA_TYPE NOT IN ('bigint','binary','bit','cursor','date','datetime','datetime2',
'datetimeoffset','float','geography','hierarchyid','image','int','money','real',
'smalldatetime','smallint','smallmoney','sql_variant','table','time','timestamp',
'tinyint','uniqueidentifier','varbinary','xml') AND
TABLE_NAME='CreditCard' ORDER BY COLUMN_NAME;
Excluded Data Types:
(bigint, binary, bit, cursor, date, datetime, datetime2, datetimeoffset, float, geography, hierarchyid, image, int, money, real, smalldatetime, smallint, smallmoney, sql_variant, table, time, timestamp, tinyint, uniqueidentifier, varbinary, xml)
The last set of server side filters we’ll apply take advantage of the weak pattern matching available in MSSQL to eliminate cells that don’t match know card formats.
SQL- Apply MSSQL pattern matching
/* create temp table with appropriate columns and data types */
CREATE TABLE #dataloc (RowNumber INT IDENTITY(1,1), "CardNumber" nvarchar(25));
/* populate temp table with data that matches payment card formats */
INSERT INTO #dataloc
Select "CardNumber" FROM [Sales].[CreditCard] WHERE "CardNumber" LIKE
('%4%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%')
UNION Select "CardNumber" FROM [Sales].[CreditCard] WHERE "CardNumber" LIKE
('%5%[1-5]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%')
UNION Select "CardNumber" FROM [Sales].[CreditCard] WHERE "CardNumber" LIKE
('%3%[47]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%')
UNION Select "CardNumber" FROM [Sales].[CreditCard] WHERE "CardNumber" LIKE
('%6%[05]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%')
This SQL copies matching rows to a temp table and adds row numbers for later use.
Local Validation Testing
We now have a temp table filled exclusively with rows of data containing 15-16 digits that loosely match known payment card patterns.
It’s time to start pulling content over the wire for local processing. The row numbers assigned earlier will be used to break the dataset into chunks.
SQL – Querying potential card numbers
SELECT * FROM #dataloc WHERE (RowNumber >=1 AND RowNumber <=4000) ORDER BY RowNumber;
Now that we have full cell data, it’s time to use regex to extract all potential payment card numbers. It’s entirely possible that some cells will contain multiple payment cards.
| American Express | (?<![0-9])3\D{0,4}(4|7)(\D{0,4}\d){13}[^0-9] |
| Discover | (?<![0-9])6\D{0,4}(5(\D{0,4}\d){14}(\D?|$)|0\D{0,4}1\D{0,4}1(\D{0,4}\d){12})[^0-9] |
| MasterCard | (?<![0-9])5\D{0,4}(0-5)(\D{0,4}\d){14}[^0-9] |
| Visa | (?<![0-9])4(\D{0,4}\d){15}[^0-9] |
If you’re familiar with regex, you may have noticed that these patterns exclude matches that are immediately prepended or followed with a digit. Although this will ultimately result in some false negatives, extensive real world testing has shown that these matches are almost always false positives.
The matches extracted using the above regex are then subjected to Luhn validation checks. Any regex match that fails the Luhn check is immediately thrown out.
Luhn Validation, invented in 1954 by Hans Peter Luhn - https://en.wikipedia.org/wiki/Luhn_algorithm
Conclusion
Testing has shown that this approach has the potential to provide higher accuracy when compared to a keyword search. It also enables us to locate payment card numbers in unexpected places such as free form notes fields, or repurposed legacy columns, but this accuracy comes with a price. Database performance loads and scan times are significantly greater than those generated by the keyword search offered by PowerUpSQL making the best approach dependent on your specific use case.
In the next few weeks I’ll be releasing a tool that serves as a proof of concept for the search method discussed in this post. In the meantime, if you’d like to read more about locating sensitive data with Scott Sutherland’s PowerUpSQL, you can do that here: https://blog.netspi.com/finding-sensitive-data-domain-sql-servers-using-powerupsql/
[post_title] => Identifying Payment Cards at Rest – Going Beyond the Key Word Search [post_excerpt] => [post_status] => publish [comment_status] => closed [ping_status] => closed [post_password] => [post_name] => identifying-payment-cards-at-rest-going-beyond-the-key-word-search [to_ping] => [pinged] => [post_modified] => 2021-04-13 00:06:07 [post_modified_gmt] => 2021-04-13 00:06:07 [post_content_filtered] => [post_parent] => 0 [guid] => https://netspiblogdev.wpengine.com/?p=7466 [menu_order] => 624 [post_type] => post [post_mime_type] => [comment_count] => 0 [filter] => raw ) ) [post_count] => 2 [current_post] => -1 [before_loop] => 1 [in_the_loop] => [post] => WP_Post Object ( [ID] => 7937 [post_author] => 35 [post_date] => 2017-08-15 07:00:09 [post_date_gmt] => 2017-08-15 07:00:09 [post_content] => In this blog I’ll be introducing dataLoc, a tool for locating payment cards in MSSQL databases without requiring the presence of keywords. dataLoc would be useful for anyone that would like to check their database for payment card numbers in unexpected places. This could include; DBAs, pen-testers, auditors, and others.dataLoc Overview
At its core, dataLoc functions by using the filtering methods discussed here: https://blog.netspi.com/identifying-payment-cards-at-rest-going-beyond-the-key-word-search/ dataLoc is not an injection or attack tool. It requires a direct connection to a database along with valid user credentials. The user account requires full read access, as well as the ability to create and drop temp tables. For those of you that are in a hurry to get started, the dataLoc source and binaries are available on GitHub: https://github.com/NetSPI/DataLocDependencies
dataLoc is a portable stand alone executable. Most systems will already have the native SQL driver the tool relies on, but if you find that your system doesn’t, it's included with the SQL Server Native Client. https://docs.microsoft.com/en-us/sql/relational-databases/native-client/sql-server-native-clientConfiguration
The tool is intended to be easy to use. All you need to do to scan for payment card numbers is provide a remote host, enter a set of credentials, or enable windows auth, and click “connect”, and then “scan”.
General
If you decide to customize some of the more advances settings, you may want to enable the use of an INI file so your changes persist. In order to keep scan times reasonable, you may want to enable the per column timeout and set a reasonable cap of 1 to 10 minutes. Most columns are processed within a few seconds.
Scoring
The scoring system is used to generate a confidence rating for each potential finding. The lower the number, the more likely the item is to be a false positive. This tool is a simple proof of concept, so it's highly likely you would benefit from tuning the scoring system to your environment.
- Luhn Valid – By default a base score of 50 is assigned for all Luhn valid matches. Anything that fails Luhn validation is discarded.
- Alpha Delimiters – A letter exists somewhere inside the number sequence Ex: 411a1111111111111
- Card + CVV – Match is followed by 3 digits Ex: 4111111111111111 123
- Phone Number – The match looks like it could be part of a phone number Ex: 1-4111111111111111
- Keywords – The text visa, card, etc. exists in the cell containing the match Ex: visa 4111111111111111
- Negative Keywords – Triple A membership numbers “aaa” are 16 digits and Luhn valid.
- Delimiters – The number of delimiters and the types. Ex Count:4 Types:2: 411-111-111-111/1111
- IIN Check – Does match contain a known IIN
Known Issues
- The script is single threaded. Once you start a scan the GUI will become unresponsive until it completes it’s run.
- The only way to stop a scan early is to kill the application.
- dataLoc was tested exclusively on Windows 10. There may be issues with the GUI on anything older.
