External Filesystem Access

Sybase has an extremely rich mechanism for interaction with the native filesystem, exposed via Component Integration Services' Proxy Table support. To enable it, an administrator must execute

 sp_configure "enable cis", 1 sp_configure "enable file access", 1 

The server need not be rebooted; as soon as the configuration is changed the external filesystem mechanism should be available. To read the contents of an external file, you create a proxy table for it, and then "select" from it as you would a normal table:

 create proxy_table foo_txt external file at "c:\temp\foo.txt"  select * from foo_txt 

The table is created by default with a single VARCHAR column of width 255 characters. If you need to handle more characters per line, you can use the "create existing table" syntax:

 create existing table foo_txt (record varchar(1000) null)  external file at "c:\temp\foo.txt" 

You can natively insert, select, and truncate the table, but you cannot update it, though you can edit foo.txt using the update statement and a temporary table. Suppose foo.txt contains the following:

 record ------ hello world line 2 line three 

and you wish to edit the first line to read " goodbye world," you can do so like this:

 create table #foo( record varchar(1000)) insert into #foo select * from foo_txt update #foo set record='goodbye world' where record like 'hello world' select * from #foo truncate table foo_txt insert into foo_txt select * from #foo drop table #foo 

Note that there is a period of time, between the "truncate" and the "insert" that follows it, where foo.txt contains no data. If you are editing a configuration file, this might be a problem for you, so use the technique with care. The effects of editing configuration files as a suitably privileged user are left to the reader's imagination .

It is possible to compromise most hosts given sufficient time and the ability to edit text files with sufficient authority, but it is also possible to use the Sybase file API to create (almost) arbitrary binary files.

A slight difficulty arises because Sybase will insert a single "newline" character (0x0a) at the end of each "line." Fortunately each line can be fairly long, and the line can contain totally arbitrary bytes, so within these restrictions it is possible to upload almost any binary file to a Sybase server, albeit with a few slight modifications.

To create arbitrary binary files you simply create the table backed by an external file with the appropriate name , and define an appropriately sized VARCHAR maximum line length, as follows:

 create table foo_exe (record varchar(1000))  external file at "c:\temp\foo.exe" 

You can then insert VARBINARY literals into the file. Again, please note that each literal "line" will be truncated to the specified line length and will then have the single byte 0x0a appended to it:

 insert into foo_exe values(0x00010203040506070809fffefdfcfbfa) 

Using this technique it is possible to upload a custom extended stored procedure DLL or library, load it with sp_addextendedproc or CREATE PROCEDURE, and then execute the code contained in the library by calling the new extended stored procedure. Fortunately, the external filesystem functionality is accessible only to administrators (that is, accounts with either sa or sso roles).