CREATE OR REPLACE PACKAGE mip_virus_check IS

  -- Author  : HARDYA
  -- Created : 23/01/2008 09:55:52
  -- Purpose : Virus checking package
  -- Updates : MULLENMD - added functionality to run the virus checker for a file
  --                      added load file (to replace BLOB)
  PROCEDURE write_file(p_name     IN wwv_flow_files.NAME%TYPE,
                       p_location IN VARCHAR2 DEFAULT 'WEBMIP_VIRUS',
                       p_fs_name  IN VARCHAR2);

  /**
    check_file - allows MIP to run an anti-virus scan on a given file

    %param p_name - the APEX name of the file
    %param p_location - the directory on the server where the file will be tested
   */
  PROCEDURE check_file(p_name     IN wwv_flow_files.NAME%TYPE,
                       p_location IN VARCHAR2 DEFAULT 'WEBMIP_VIRUS');
  --

  /**
    get_fs_name - returns the file name from teh APEX doc's URI

    %param p_name - the APEX doc URI (in format unique ID/filename)
    %return VARCHAR2 - the actual name of the file
  */
  FUNCTION get_fs_name(p_name IN documents.uri%TYPE) RETURN VARCHAR2;
END mip_virus_check;
/
CREATE OR REPLACE PACKAGE BODY mip_virus_check IS
  /**
   Updates:
     MM 25-Feb-2008 added the load file
  */
  PROCEDURE pl(p_in   VARCHAR2
              ,p_line IN NUMBER DEFAULT NULL) IS
  BEGIN
    $IF mip_debug_constants.debugging OR mip_debug_constants.friendly_messages 
    $THEN
    mip_debug.pl(p_unit => $$PLSQL_UNIT
                ,p_line => p_line
                ,p_in   => p_in);
    $END
    NULL;
    END pl;

  PROCEDURE write_file(p_name     IN wwv_flow_files.NAME%TYPE,
                       p_location IN VARCHAR2 DEFAULT 'WEBMIP_VIRUS',
                       p_fs_name  IN VARCHAR2) IS
    l_lob_loc     BLOB;
    l_buffer      RAW(32767);
    l_buffer_size BINARY_INTEGER;
    l_amount      BINARY_INTEGER;
    l_offset      NUMBER(38) := 1;
    l_chunksize   INTEGER;
    l_out_file    utl_file.file_type;

  BEGIN
    pl('Writing file ' || p_name || ', ' || p_fs_name);
    SELECT blob_content
      INTO l_lob_loc
      FROM wwv_flow_files
     WHERE upper(NAME) = upper(p_name);
    pl('Got file from APEX.  Downloading');
    l_chunksize := dbms_lob.getchunksize(l_lob_loc);

    IF (l_chunksize < 32767) THEN
      l_buffer_size := l_chunksize;
    ELSE
      l_buffer_size := 32767;
    END IF;

    l_amount := l_buffer_size;

    dbms_lob.OPEN(l_lob_loc, dbms_lob.lob_readonly);

    l_out_file := utl_file.fopen(location     => p_location,
                                 filename     => p_fs_name,
                                 open_mode    => 'wb',
                                 max_linesize => 32767);

    WHILE l_amount >= l_buffer_size LOOP

      dbms_lob.READ(lob_loc => l_lob_loc,
                    amount  => l_amount,
                    offset  => l_offset,
                    buffer  => l_buffer);

      l_offset := l_offset + l_amount;

      utl_file.put_raw(file      => l_out_file,
                       buffer    => l_buffer,
                       autoflush => TRUE);

    --utl_file.fflush(file => l_out_file);

    END LOOP;

    utl_file.fflush(file => l_out_file);

    utl_file.fclose(l_out_file);

    dbms_lob.CLOSE(l_lob_loc);
  END write_file;
  --

  /**
   load_file - procedure to reload the resultant file after virus checking is complete.

   %param p_uri - the file's unique URI as stored in the Apex view
  */
  PROCEDURE load_file(p_uri wwv_flow_files.NAME%TYPE) IS
    l_directory_path     all_directories.directory_path%TYPE;
    l_source_file        BFILE;
    l_source_file_length BINARY_INTEGER;
    l_blob               BLOB;
    l_filename           VARCHAR2(250);
  BEGIN

    pl('reload_after_av');
    dbms_lob.createtemporary(lob_loc => l_blob, cache => TRUE);
    -- lock the table by selecting for update
    SELECT f.blob_content
      INTO l_blob
      FROM wwv_flow_files f

     WHERE f.NAME = p_uri
       FOR UPDATE;

    SELECT directory_path
      INTO l_directory_path
      FROM all_directories
     WHERE directory_name = 'WEBMIP_VIRUS';

    l_filename := REPLACE(p_uri, '/', '!');

    l_source_file        := bfilename('WEBMIP_VIRUS', l_filename);
    l_source_file_length := dbms_lob.getlength(l_source_file);

    dbms_lob.OPEN(file_loc  => l_source_file,
                  open_mode => dbms_lob.lob_readonly);

    dbms_lob.loadfromfile(dest_lob => l_blob,
                          src_lob  => l_source_file,
                          amount   => l_source_file_length);

    -- update the BLOB
    UPDATE wwv_flow_files f
       SET f.blob_content = l_blob
     WHERE f.NAME = p_uri;

    --close the file
    dbms_lob.fileclose(file_loc => l_source_file);

    pl('Loaded:' || l_filename);
  END load_file;
  --

  /*\**
      trigger_av_scan - triggers the anti-virus scan on the file

      %param p_location - the location of the file on the file server
      %param p_fs_name - the name of the file to check
    *\
  FUNCTION trigger_av_scan(p_fs_name  IN VARCHAR2) RETURN BOOLEAN IS
    av_cmd VARCHAR2(250);
    av_checker VARCHAR2(250);
    l_location VARCHAR2(250);
  BEGIN
    dbms_output.put_line('Checking file.');
    av_checker := cout_system_configuration.get_configuration_item('AV_LOCATION');
    l_location := cout_system_configuration.get_configuration_item('AV_DIRECTORY');

    av_cmd := '"' || av_checker ||'" /automation '||l_location||'\'||p_fs_name;

    dbms_output.put_line('about to check');
    dbms_output.put_line(av_cmd);

    mip_systemcall.syscall(p_cmd_str => av_cmd);

    dbms_output.put_line('Finished checking');

    RETURN TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
      RETURN NULL;
  END trigger_av_scan;
  --*/

  /**
    check_file_exists - tests if the file is still on the server
                        if the file is no longer there then it has
                        failed the virus scan and been quarantined

    %param p_location - the location of the file on the file server
    %param p_fs_name - the name of the file to check
  */
  FUNCTION check_file_exists(p_location IN VARCHAR2 DEFAULT 'WEBMIP_VIRUS',
                             p_fs_name  IN VARCHAR2) RETURN BOOLEAN IS
    l_file_exists BOOLEAN;
    l_file_length NUMBER;
    l_block_size  NUMBER;
  BEGIN
    utl_file.fgetattr(location    => p_location,
                      filename    => p_fs_name,
                      fexists     => l_file_exists,
                      file_length => l_file_length,
                      block_size  => l_block_size);
    pl('File exists:' || sys.diutil.bool_to_int(l_file_exists));
    RETURN l_file_exists;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END check_file_exists;
  --

  /**
    delete_file_from_server - removes the files from the server once AV
                              scan is complete.  Housekeeping.

    %param p_location - the location of the file on the file server
    %param p_fs_name - the name of the file to check
  */
  PROCEDURE delete_file_from_server(p_location IN VARCHAR2 DEFAULT 'WEBMIP_VIRUS',
                                    p_fs_name  IN VARCHAR2) IS
  BEGIN
    utl_file.fremove(location => p_location, filename => p_fs_name);
  END delete_file_from_server;
  --

  /**
    delete_file_from_MIP - removes an infected file from the MIP system

    %param p_location - the location of the file on the file server
    %param p_fs_name - the name of the file to check
  */
  PROCEDURE delete_file_from_mip(p_doc_id documents.id%TYPE) IS
    l_result BOOLEAN;
  BEGIN
    l_result := mip_files.delete_file(p_id         => p_doc_id,
                                      p_doc_status => 'REMOVED MALICIOUS',
                                      p_reason     => 'Malicious content found - deleted by Anti-virus.');

  END delete_file_from_mip;
  --

  /**
    check_file - allows MIP to run an anti-virus scan on a given file

    %param p_name - the APEX name of the file
    %param p_location - the directory on the server where the file will be tested
   */
  PROCEDURE check_file(p_name     IN wwv_flow_files.NAME%TYPE,
                       p_location IN VARCHAR2 DEFAULT 'WEBMIP_VIRUS') IS
    av_scan_result BOOLEAN;
    file_result    BOOLEAN;
    l_fs_name      VARCHAR2(250);
    l_doc_id       documents.id%TYPE;
  BEGIN
    -- need to remove the slash from the URI so we can store multiple files
    -- change it to a "!" for now.
    l_fs_name := REPLACE(p_name, '/', '!');

    SELECT doc.id INTO l_doc_id FROM documents doc WHERE doc.uri = p_name;

    -- write the file to the file system, trigger a scan, check if the file is still there
    -- and then act accordingly if it isn't.
    write_file(p_name     => p_name,
               p_location => p_location,
               p_fs_name  => l_fs_name);
 
    --dbms_lock.sleep(5);

    pl('Checking file exists:' || l_fs_name);
    file_result := check_file_exists(p_fs_name => l_fs_name);

    IF NOT file_result THEN
      delete_file_from_mip(p_doc_id => l_doc_id);
      pl('File removed from MIP');
    ELSE
      -- reload the leftovers into MIP
      load_file(p_uri => p_name);

      pl('Delete file from server');
      delete_file_from_server(p_location => p_location,
                              p_fs_name  => l_fs_name);

      mip_documents.set_doc_available(p_docu_id     => l_doc_id,
                                      p_description => 'Virus checked by MIP');

    END IF;
  EXCEPTION
    WHEN no_data_found THEN
      delete_file_from_mip(p_doc_id => l_doc_id);
      pl('File uploaded was empty and has been removed from MIP');
      
  END check_file;
  --

  /**
    get_fs_name - returns the file name from the APEX doc's URI

    %param p_name - the APEX doc URI (in format unique ID/filename)
    %return VARCHAR2 - the actual name of the file
  */
  FUNCTION get_fs_name(p_name IN documents.uri%TYPE) RETURN VARCHAR2 IS
    ret_fs_name   VARCHAR2(250);
    seperator_pos NUMBER;
  BEGIN
    seperator_pos := instr(p_name, '/', -1);

    IF NOT seperator_pos = 0 THEN
      ret_fs_name := substr(p_name, seperator_pos + 1);

      RETURN ret_fs_name;
    ELSE
      RETURN NULL;
    END IF;
  END get_fs_name;
  --
END mip_virus_check;
/