How To View Data in Hidden System Tables.

SQL Server has System base tables which are hidden cannot be directly queried.

For example:

[sql]
SELECT * FROM sys.sysrscols
[/sql]

Output:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.sysrscols’.

In order to view data from the hidden System base tables you have to connect to SQL Server using Dedicated Administrator Connection.

In order to connect using Dedicated Administrator Connection, Add ADMIN: before the name of the server\instance in SQL Server Management studio.

Dedicated Administrator Connection

Now you can query all the System tables. 🙂

[sql]

——————————————————————————————————————————————————
SELECT * FROM sys.sysrscols
GO
SELECT * FROM sys.sysrowsets
GO
SELECT * FROM sys.sysallocunits
GO
SELECT * FROM sys.sysfiles1
GO
SELECT * FROM sys.syspriorities
GO
SELECT * FROM sys.sysdbfrag
GO
SELECT * FROM sys.sysfgfrag
GO
SELECT * FROM sys.syspru
GO
SELECT * FROM sys.sysbrickfiles
GO
SELECT * FROM sys.sysphfg
GO
SELECT * FROM sys.sysprufiles
GO
SELECT * FROM sys.sysftinds
GO
SELECT * FROM sys.sysowners
GO
SELECT * FROM sys.sysdbreg
GO
SELECT * FROM sys.sysprivs
GO
SELECT * FROM sys.sysschobjs
GO
SELECT * FROM sys.syslogshippers
GO
SELECT * FROM sys.syscolpars
GO
SELECT * FROM sys.sysxlgns
GO
SELECT * FROM sys.sysxsrvs
GO
SELECT * FROM sys.sysnsobjs
GO
SELECT * FROM sys.sysusermsgs
GO
SELECT * FROM sys.syscerts
GO
SELECT * FROM sys.sysrmtlgns
GO
SELECT * FROM sys.syslnklgns
GO
SELECT * FROM sys.sysxprops
GO
SELECT * FROM sys.sysscalartypes
GO
SELECT * FROM sys.systypedsubobjs
GO
SELECT * FROM sys.sysidxstats
GO
SELECT * FROM sys.sysiscols
GO
SELECT * FROM sys.sysendpts
GO
SELECT * FROM sys.syswebmethods
GO
SELECT * FROM sys.sysbinobjs
GO
SELECT * FROM sys.sysaudacts
GO
SELECT * FROM sys.sysobjvalues
GO
SELECT * FROM sys.sysclsobjs
GO
SELECT * FROM sys.sysrowsetrefs
GO
SELECT * FROM sys.sysremsvcbinds
GO
SELECT * FROM sys.sysxmitqueue
GO
SELECT * FROM sys.sysrts
GO
SELECT * FROM sys.sysconvgroup
GO
SELECT * FROM sys.sysdesend
GO
SELECT * FROM sys.sysdercv
GO
SELECT * FROM sys.syssingleobjrefs
GO
SELECT * FROM sys.sysmultiobjrefs
GO
SELECT * FROM sys.sysguidrefs
GO
SELECT * FROM sys.syschildinsts
GO
SELECT * FROM sys.syscompfragments
GO
SELECT * FROM sys.sysftstops
GO
SELECT * FROM sys.sysqnames
GO
SELECT * FROM sys.sysxmlcomponent
GO
SELECT * FROM sys.sysxmlfacet
GO
SELECT * FROM sys.sysxmlplacement
GO
SELECT * FROM sys.sysobjkeycrypts
GO
SELECT * FROM sys.sysasymkeys
GO
SELECT * FROM sys.syssqlguides
GO
SELECT * FROM sys.sysbinsubobjs
GO
SELECT * FROM sys.syssoftobjrefs
GO
[/sql]

enable xp_cmdshell on SQL Server

How to enable xp_cmdshell on SQL Server 2005/2008?

If xp_cmdshell is disabled on SQL Server you get the following error:
Query:
[sql]
EXEC xp_cmdshell ‘dir’
[/sql]


SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

In order to enable this SQL Server has to be configured to allow access to procedure xp_cmdshell.

Following are the steps for that:

[sql]
EXEC sp_configure ‘show advanced options’, 1
reconfigure
GO
[/sql]

This enables to show advanced options by sp_configure.

Now if you execute sp_configure
[sql]
EXEC sp_configure
[/sql]

The following will be the output in the results window:

xp_cmdshell disabled
xp_cmdshell disabled

Run the following query to enable this:
[sql]
EXEC sp_configure ‘xp_cmdshell’,1
reconfigure
GO
[/sql]

Now you will be able to execute xp_cmdshell.

More about using sp_configure can be found here

SQL injection

SQL injection
SQL injection is technique that exploits a security vulnerability using sql code.
This happens when the input given by the user is not correctly checked for the vurnerable SQL code and is there by sent to the instance of SQL Server for parsing and execution. This process works by terminating the text and by appending a new command.

SQL Injection Attack is abbreviated as SQLIA

Basic example of SQL Injection attack:

Lets consider the following query:

[php]var sql = "SELECT * FROM EMPLOYEE WHERE NAME =’"+EmployeeName+"’";[/php]

EmployeeName is fetched from the users input from the web page.

Now if the user enters “Jhon”, then the query would run great and the details of “Jhon” would be displayed.

But consider user entering the following as input:
[php]’ or ‘1’=’1[/php]

This would result in the following query:

[php]var sql = "SELECT * FROM EMPLOYEE WHERE NAME =” or ‘1’=’1’";[/php]

This means the user will be able to tweek the SQL query.

Using such means users can by pass user authentication on the websites. Hence the developers must be careful in order to avoid such Injection attacks.

Now if we pass this as employee name??
[php]Jhon’; drop table EMPLOYEE–[/php]

This results in the query being built into:
[php]var sql = "SELECT * FROM EMPLOYEE WHERE NAME =’Jhon’; drop table EMPLOYEE–‘";[/php]

The SQL Query that would be executed will be:
[sql]SELECT * FROM EMPLOYEE WHERE NAME =’Jhon’; drop table EMPLOYEE–‘[/sql]

Here ; says that the first query is completed, and the — says to ignore the remaining part of the query.

So three queries are executed here:
[sql]
SELECT * FROM EMPLOYEE WHERE NAME =’Jhon’
drop table EMPLOYEE
–‘[/sql]

  • The first query gives the details of Jhon
  • Second one drops the table EMPLOYEE
  • The third one does not do any thing as it is just a SQL Comment

The following charecters must be checked in the user input and if they are present they have to be rejected:

  • ;
  • /* … */
  • xp_

Downloads:
Microsoft – Source Code Analyzer for SQL Injection

References:
wiki
MSDN – SQL Injection