后渗透之数据挖掘

发表于 分类于 阅读次数：

前言

一般拿到数据库权限后会翻库，找一些敏感数据，这里汇总了几个语句，能在数据库中快速进行数据挖掘。

快速寻找Oracle中user相关数据

1
2
3
SELECT owner, table_name, column_name
FROM dba_tab_columns
WHERE LOWER(column_name) LIKE '%user%';

MYSQL

1
2
3
SELECT table_schema, table_name, column_name 
FROM information_schema.columns 
WHERE column_name COLLATE utf8_general_ci LIKE '%phone%';

写在单行中

1
SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE column_name COLLATE utf8_general_ci LIKE '%phone%';

Postgres

1
2
3
SELECT table_schema, table_name, column_name 
FROM information_schema.columns 
WHERE column_name ILIKE '%phone%';

SQL Server T-SQL 搜索值所在的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
DECLARE @SearchValue INT
SET @SearchValue = 99999999  -- 设置要搜索的值

-- 创建一个临时表来存储包含指定值的表名和列名
CREATE TABLE #Results
(
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128)
)

-- 使用动态SQL查询所有表
DECLARE @TableName NVARCHAR(128)
DECLARE @ColumnName NVARCHAR(128)

DECLARE @Sql NVARCHAR(MAX)

DECLARE table_cursor CURSOR FOR
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.system_type_id IN (56, 127)  -- 56 表示整数数据类型，127 表示大整数数据类型

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = N'INSERT INTO #Results (TableName, ColumnName)
                SELECT N''' + @TableName + ''', N''' + @ColumnName + '''
                FROM ' + QUOTENAME(@TableName) + '
                WHERE ' + QUOTENAME(@ColumnName) + ' = ' + CAST(@SearchValue AS NVARCHAR)

    EXEC sp_executesql @Sql

    FETCH NEXT FROM table_cursor INTO @TableName, @ColumnName
END

CLOSE table_cursor
DEALLOCATE table_cursor

-- 查询结果
SELECT * FROM #Results

-- 删除临时表
DROP TABLE #Results

SQL Server T-SQL 搜索字符串所在的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
DECLARE @SearchValue NVARCHAR(255)
SET @SearchValue = '192.168.31.132'  -- 设置要搜索的字符串

-- 创建一个临时表来存储包含指定值的表名和列名
CREATE TABLE #Results
(
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128)
)

-- 使用动态SQL查询所有表
DECLARE @TableName NVARCHAR(128)
DECLARE @ColumnName NVARCHAR(128)

DECLARE @Sql NVARCHAR(MAX)

DECLARE table_cursor CURSOR FOR
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.system_type_id IN (167, 175, 231)  -- 字符串数据类型的类型码

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = N'INSERT INTO #Results (TableName, ColumnName)
                SELECT N''' + @TableName + ''', N''' + @ColumnName + '''
                FROM ' + QUOTENAME(@TableName) + '
                WHERE ' + QUOTENAME(@ColumnName) + ' LIKE ''%' + @SearchValue + '%'''

    EXEC sp_executesql @Sql

    FETCH NEXT FROM table_cursor INTO @TableName, @ColumnName
END

CLOSE table_cursor
DEALLOCATE table_cursor

-- 查询结果
SELECT * FROM #Results

-- 删除临时表
DROP TABLE #Results