Data Targeting

Being able to properly target and identify sensitive information can exponentially decrease time spent in a database. This means less time spent poking around and more time spent researching other vectors.

Data Targeting queries

Description Query
Database sizes SELECT table_schema “Database Name",sum( data_length + index_length ) / 1024 / 1024 “Database Size in MB",sum( data_free )/ 1024 / 1024 “Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema ;
Database name keyword SELECT table_schema “Database Name" FROM information_schema.TABLES WHERE table_schema LIKE “%passwords%" GROUP BY table_schema ;
Table name keyword SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT LIKE “information_schema" AND table_name LIKE “%admin%“;
Column name keyword SELECT column_name, table_name FROM information_schema.columns WHERE column_name LIKE “%password%“;
Column data regex SELECT * from credit_cards WHERE cc_number REGEXP '^4[0-9]{15}$';

Data Targeting regexes

Description Regex
All major credit card providers ^(?:4[0-9]{12}(?:[0-9]{3})?|(?:5[1-5][0-9]{2}|222[1-9]|22[3-9][0-9]|2[3-6][0-9]{2}|27[01][0-9]|2720)[0-9]{12}|3[47][0-9]{13}|3(?:0[0-5]|[68][0-9])[0-9]{11}|6(?:011|5[0-9]{2})[0-9]{12}|(?:2131|1800|35\d{3})\d{11})$
Unmasked | Masked SSN ^(\d{3}-?\d{2}-?\d{4}|XXX-XX-XXXX)$

Data Targeting keywords

Keyword
credit
card
pin
cvv
pan
password
social
ssn
account
confidential

Data Targeting

Being able to properly target and identify sensitive information can exponentially decrease time spent in a database. This means less time spent poking around and more time spent researching other vectors.

Data Targeting queries

Description Query
Finding Sensitive Data SELECT owner,table_name,column_name FROM all_tab_columns WHERE column_name LIKE '%PASS%';
Finding Privileges SELECT * FROM session_privs
SELECT * FROM USER_SYS_PRIVS
SELECT * FROM USER_TAB_PRIVS
SELECT * FROM USER_TAB_PRIVS_MADE
SELECT * FROM USER_TAB_PRIVS_RECD
SELECT * FROM ALL_TAB_PRIVS
SELECT * FROM USER_ROLE_PRIVS
Extracting stored procedure/Java sources SELECT * FROM all_source WHERE owner NOT IN ('SYS','SYSTEM')
SELECT * FROM all_source WHERE TYPE LIKE '%JAVA %'
SELECT TO_CHAR(DBMS_METADATA.get_ddl('TABLE','DEPT','CONSUELA')) FROM dual

Data Targeting regexes

Description Regex
All major credit card providers ^(?:4[0-9]{12}(?:[0-9]{3})?|(?:5[1-5][0-9]{2}|222[1-9]|22[3-9][0-9]|2[3-6][0-9]{2}|27[01][0-9]|2720)[0-9]{12}|3[47][0-9]{13}|3(?:0[0-5]|[68][0-9])[0-9]{11}|6(?:011|5[0-9]{2})[0-9]{12}|(?:2131|1800|35\d{3})\d{11})$
Unmasked | Masked SSN ^(\d{3}-?\d{2}-?\d{4}|XXX-XX-XXXX)$

Data Targeting keywords

Keyword
credit
card
pin
cvv
pan
password
social
ssn
account
confidential

Data Targeting

Being able to properly target and identify sensitive information can exponentially decrease time spent in a database. This means less time spent poking around and more time spent researching other vectors.

Data Targeting queries

Description Query
List non-default databases 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;
List non-default tables 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;
Column name search SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like '%password%'
List non-default columns 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;
Search for transparent encryption SELECT a.database_id as [dbid], a.name, HAS_DBACCESS(a.name) as [has_dbaccess], SUSER_SNAME(a.owner_sid) as [db_owner], a.is_trustworthy_on, a.is_db_chaining_on, a.is_broker_enabled, a.is_encrypted, a.is_read_only, a.create_date, a.recovery_model_desc, b.filename FROM [sys].[databases] a INNER JOIN [sys].[sysdatabases] b ON a.database_id = b.dbid ORDER BY a.database_id WHERE is_encrypted=1
Search by database size SELECT a.database_id as [dbid], a.name, HAS_DBACCESS(a.name) as [has_dbaccess], SUSER_SNAME(a.owner_sid) as [db_owner], a.is_trustworthy_on, a.is_db_chaining_on, a.is_broker_enabled, a.is_encrypted, a.is_read_only, a.create_date, a.recovery_model_desc, b.filename, (SELECT CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) from sys.master_files where name like a.name) as [DbSizeMb] FROM [sys].[databases] a INNER JOIN [sys].[sysdatabases] b ON a.database_id = b.dbid ORDER BY DbSizeMb DESC

Data Targeting regexes

Description Regex
All major credit card providers ^(?:4[0-9]{12}(?:[0-9]{3})?|(?:5[1-5][0-9]{2}|222[1-9]|22[3-9][0-9]|2[3-6][0-9]{2}|27[01][0-9]|2720)[0-9]{12}|3[47][0-9]{13}|3(?:0[0-5]|[68][0-9])[0-9]{11}|6(?:011|5[0-9]{2})[0-9]{12}|(?:2131|1800|35\d{3})\d{11})$
Unmasked | Masked SSN ^(\d{3}-?\d{2}-?\d{4}|XXX-XX-XXXX)$

Data Targeting keywords

Keyword
credit
card
pin
cvv
pan
password
social
ssn
account
confidential

© 2018 Copyright by NetSPI. All rights reserved.