Tuesday, September 9, 2008

Importing text files into tables, and Creating Text Files using BCP

[Importing text files into tables]

Using the 'bulk insert' statement, it is possible to insert a text file into a temporary table. Simply create the table like this:

create table foo( line varchar(8000) )

…and then run an bulk insert to insert the data from the file, like this:

bulk insert foo from 'c:\inetpub\wwwroot\process_login.asp'

…the data can then be retrieved using any of the above error message techniques, or by a 'union' select, combining the data in the text file with the data that is normally returned by the application. This is useful for obtaining the source code of scripts stored on the database server, or possibly the source of ASP scripts.

[Creating Text Files using BCP]

It is fairly easy to create arbitrary text files using the 'opposite' technique to the 'bulk insert'. Unfortunately this requires a command line tool, 'bcp', the 'bulk copy program'

Since bcp accesses the database from outside the SQL Server process, it requires a login. This is typically not difficult to obtain, since the attacker can probably create one anyway, or take advantage of 'integrated' security mode, if the server is configured to use it.
The command line format is as follows:

bcp "SELECT * FROM test..foo" queryout c:\inetpub\wwwroot\runcommand.asp -c -Slocalhost -Usa -Pfoobar

The 'S' parameter is the server on which to run the query, the 'U' is the username and the 'P' is the password, in this case 'foobar'.

Split the String values with a special character in MS Flow to convert this into Array

 Many times we have a requirement to prepare the Mailing address for some of the documents, suppose there are Address Line1, Address Line2, ...