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 |
© 2023 Copyright by NetSPI. All rights reserved.