Getting file details

If you need to find out the attributes of a particular file, then try out spFileDetails:

/****** Object:  StoredProcedure [dbo].[spFileDetails]    Script Date: 03/28/2007 15:28:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spFileDetails]
@Filename VARCHAR(100)

/*
spFileDetails 'c:\autoexec.bat'
*/
AS
DECLARE @hr INT,         --the HRESULT returned from 
       @objFileSystem INT,              --the FileSystem object
       @objFile INT,            --the File object
       @ErrorObject INT,        --the error object
       @ErrorMessage VARCHAR(255),--the potential error message
       @Path VARCHAR(100),--
       @ShortPath VARCHAR(100),
       @Type VARCHAR(100),
       @DateCreated datetime,
       @DateLastAccessed datetime,
       @DateLastModified datetime,
       @Attributes INT,
       @size INT



SET nocount ON

SELECT @hr=0,@errorMessage='opening the file system object '
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',
                                       @objFileSystem OUT
IF @hr=0 SELECT @errorMessage='accessing the file '''
                                       +@Filename+'''',
       @ErrorObject=@objFileSystem
IF @hr=0 EXEC @hr = sp_OAMethod @objFileSystem,
         'GetFile',  @objFile out,@Filename
IF @hr=0 
       SELECT @errorMessage='getting the attributes of '''
                                       +@Filename+'''',
       @ErrorObject=@objFile
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'Path', @path OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'ShortPath', @ShortPath OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'Type', @Type OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'DateCreated', @DateCreated OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'DateLastAccessed', @DateLastAccessed OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'DateLastModified', @DateLastModified OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'Attributes', @Attributes OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'size', @size OUT


IF @hr<>0
       BEGIN
       DECLARE 
               @Source VARCHAR(255),
               @Description VARCHAR(255),
               @Helpfile VARCHAR(255),
               @HelpID INT
       
       EXECUTE sp_OAGetErrorInfo  @errorObject, 
               @source output,@Description output,
                               @Helpfile output,@HelpID output

       SELECT @ErrorMessage='Error whilst '
                               +@Errormessage+', '
                               +@Description
       RAISERROR (@ErrorMessage,16,1)
       END
EXEC sp_OADestroy @objFileSystem
EXEC sp_OADestroy @objFile
SELECT [Path]=  @Path,
       [ShortPath]=    @ShortPath,
       [Type]= @Type,
       [DateCreated]=  @DateCreated ,
       [DateLastAccessed]=     @DateLastAccessed,
       [DateLastModified]=     @DateLastModified,
       [Attributes]=   @Attributes,
       [size]= @size
RETURN @hr

Execute spFileDetails 'c:\autoexec.bat'

Writing out a file

No problem – just create spWriteStringToFile and try:

alter PROCEDURE spWriteStringToFile
 (
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)

--
)
AS
DECLARE  @objFileSystem int
        ,@objTextStream int,
  @objErrorObject int,
  @strErrorMessage Varchar(1000),
     @Command varchar(1000),
     @hr int,
  @fileAndPath varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod   @objFileSystem   , 'CreateTextFile'
 , @objTextStream OUT, @FileAndPath,2,True

if @HR=0 Select @objErrorObject=@objTextStream, 
 @strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'

if @hr<>0
 begin
 Declare 
  @Source varchar(255),
  @Description Varchar(255),
  @Helpfile Varchar(255),
  @HelpID int
 
 EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
  @source output,@Description output,@Helpfile output,@HelpID output
 Select @strErrorMessage='Error whilst '
   +coalesce(@strErrorMessage,'doing something')
   +', '+coalesce(@Description,'')
 raiserror (@strErrorMessage,16,1)
 end
EXECUTE  sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream 

execute spWriteStringToFile 'This article describes how to fully access the
local filesystem from SQL Server. It shows a
way of reading and writing data to file, and
accessing the details of the server's
filesystem using OLE Automation to access
the filesystem object'
, 'MyPath','MyFileName'

Reading a file into a SQL Server data type

This is all very well, but how about something that reads a file in one gulp into a varchar or XML datatype? Perhaps you need to extract data from HTML, XHTML or some other format. Create the ufsReadfileAsString procedure and try something like…

ALTER FUNCTION [dbo].[ufsReadfileAsString]
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS 
 Varchar(max)
AS
BEGIN

DECLARE  @objFileSystem int
        ,@objTextStream int,
  @objErrorObject int,
  @strErrorMessage Varchar(1000),
     @Command varchar(1000),
  @Chunk Varchar(8000),
  @String varchar(max),
     @hr int,
  @YesOrNo int

Select @String=''
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT


if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename

if @HR=0 execute @hr = sp_OAMethod   @objFileSystem  , 'OpenTextFile'
 , @objTextStream OUT, @command,1,false,0--for reading, FormatASCII

WHILE @hr=0
 BEGIN
 if @HR=0 Select @objErrorObject=@objTextStream, 
  @strErrorMessage='finding out if there is more to read in "'+@filename+'"'
 if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT

 IF @YesOrNo<>0  break
 if @HR=0 Select @objErrorObject=@objTextStream, 
  @strErrorMessage='reading from the output file "'+@filename+'"'
 if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Read', @chunk OUTPUT,4000
 SELECT @String=@string+@chunk
 end
if @HR=0 Select @objErrorObject=@objTextStream, 
 @strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'


if @hr<>0
 begin
 Declare 
  @Source varchar(255),
  @Description Varchar(255),
  @Helpfile Varchar(255),
  @HelpID int
 
 EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
  @source output,@Description output,@Helpfile output,@HelpID output
 Select @strErrorMessage='Error whilst '
   +coalesce(@strErrorMessage,'doing something')
   +', '+coalesce(@Description,'')
 select @String=@strErrorMessage
 end
EXECUTE  sp_OADestroy @objTextStream
 -- Fill the table variable with the rows for your result set
 
 RETURN @string
END 

Select dbo.ufsReadfileAsString ('MyPath','MyFileName')

STUFF

Stuff is another TSql Function which is used to delete a specified length of characters within a string and replace with another set of characters. The general syntax of STUFF is as below :

STUFF(character_expression1, start, length, character_expression2)Character_Expression1 represents the string in which the stuff is to be applied. start indicates the starting position of the character in character_expression1, length is the length of characters which need to be replaced. character_expression2 is the string that will be replaced to the start position.

Let us take an example :


EG:-

select STUFF((
SELECT DISTINCT TOP 100 PERCENT
                                ',' + t2.team_name
                        FROM    team_master AS t2
                        ORDER BY ',' + t2.team_name
                        FOR XML PATH('')
  ), 1, 1, '')

Time delay SQL injections

The following excerpt, courtesy of Wiley Publishing, is from Chapter 22 of the book "The Database Hacker's Handbook: Defending Database Servers" written by David Litchfield, Chris Anley, John Heasman and Bill Grindlay. Click for the complete book excerpt series or purchase the book.




Time delays
The previous examples of SQL injection techniques assumed that the client can view the error messages returned by the backend database server; however, often the Web server is set up so that error messages are not returned. In this case an attacker may suspect that the Web application is vulnerable to SQL injection but be unable to view any useful information because of its configuration. A method used here to extract the data is known as time delay SQL injection, and works on the basis that true or false queries can be answered by the amount of time a request takes to complete. The statement waitfor used with the delay argument causes

SQL Server to pause for the specified period of time:

waitfor delay '0:0:5'

This will pause the query for five seconds.

This feature can be leveraged to reveal information, such as whether the Web application's connection to the database is made as a system administrator:

if (is_srvrolemember('sysadmin') > 0) waitfor delay '0:0:5'

This will cause the query to pause if true, or return immediately if false. At the very lowest level, all data stored in the database is just a binary series of ones and zeros. This means that any data in the database can be extracted using a sequence of true/false questions. For example, the query

if (ascii(substring(@string, @byte, 1)) & (power(2, @bit))) > 0 waitfor
delay '0:0:5'

will trigger a delay only if the bit (@bit) of byte (@byte) in the string (@string) is set to 1. To retrieve the current database name from the server, execute

declare @string varchar(8192) select @string = db_name() if
(ascii(substring(@string, 1, 1)) & (power(2, 0))) > 0 waitfor delay
'0:0:5'

This will delay if the first bit of the first byte of the current database name is set to 1. The second bit of the first byte can then be queried:

declare @string varchar(8192) select @string = db_name() if
(ascii(substring(@string, 1, 1)) & (power(2, 1))) > 0 waitfor delay
'0:0:5'

and so on, building up the entire string. Obviously using this method, this would be a time-consuming process, mainly because of the five-second delay per set bit. It is not necessary, however, to run these queries sequentially or in any particular order. A small program, known as a harness, can be used to form the URLs to request with the necessary injected SQL to build the required string. Multiple requests can then be made to the server in multiple threads, and the harness program can then wait for the requests to return and build the string as they do.