sqlserver.html

<h3 id="data-targeting">Data Targeting</h3>

<p class="pageDescription">{{site.data.injectionDescriptions.dataTargeting}}</p>

<h3 id="data-targeting-queries">Data Targeting queries</h3>

<table class="table table-striped table-hover">
  <thead>
    <tr>
      <th>Description</th>
      <th align="left">Query</th>
    </tr>
  </thead>
  <tr>
    <td>List non-default databases</td>
    <td>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;</td>
  </tr>
  <tr>
    <td>List non-default tables</td>
    <td>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;</td>
  </tr>
  <tr>
    <td>Column name search</td>
    <td>SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like '%password%'</td>
  </tr>
  <tr>
    <td>List non-default columns</td>
    <td>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;</td>
  </tr>
  <tr>
    <td>Search for transparent encryption</td>
    <td>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</td>
  </tr>
  <tr>
    <td>Search by database size</td>
    <td>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</td>
  </tr>
  </thead>
</table>




<h3 id="data-targeting-regexes">Data Targeting regexes</h3>

<table class="table table-striped table-hover">
  <thead>
    <tr>
      <th>Description</th>
      <th align="left">Regex</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>All major credit card providers</td>
      <td>^(?: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})$</td>
    </tr>
    <tr>
      <td>Unmasked | Masked SSN</td>
      <td>^(\d{3}-?\d{2}-?\d{4}|XXX-XX-XXXX)$</td>
    </tr>
  </tbody>
</table>




<h3 id="data-targeting-keywords">Data Targeting keywords</h3>

<table class="table table-striped table-hover">
  <thead>
    <tr>
      <th>Keyword</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>credit</td>
    </tr>
    <tr>
      <td>card</td>
    </tr>
    <tr>
      <td>pin</td>
    </tr>
    <tr>
      <td>cvv</td>
    </tr>
    <tr>
      <td>pan</td>
    </tr>
    <tr>
      <td>password</td>
    </tr>
    <tr>
      <td>social</td>
    </tr>
    <tr>
      <td>ssn</td>
    </tr>
    <tr>
      <td>account</td>
    </tr>
    <tr>
      <td>confidential</td>
    </tr>
  </tbody>
</table>