File Extension Whitelist


A new feature to limit the file extensions that can be uploaded to the filestore.

Available for

  • Appframe365
  • Pims R4

To enable

File table must be valid table and have "Extension" column for this check to work. Otherwise please extract extension from File name.
    
        --Add extension if not exists
        ALTER TABLE [dbo].[SomeTable]
            ADD [Extension] as (
                case    when charindex('.', reverse(FileName)) > 0 
                    then reverse(left(reverse(FileName), charindex('.', reverse(FileName)) - (1))) 
                    else ''
                end) persisted
    

Insert at least one row in the stbl_System_FilesExtensionWhitelist table. Only extensions that have Allowed set to 1 will be possible to upload.

Important: ITrig's and UTrig's should be re-generated (or manually updated) for all File Tables in the system!
Make sure they contain SQL to safeguard updating file names and thus changing extension. The SQL should look something like this (extract from an UTrig):

    
    -- If no whitelist defined (empty table), then accept all file types
    IF UPDATE(FileName) AND EXISTS(SELECT * FROM dbo.stbl_System_FilesExtensionWhitelist WITH (NOLOCK))
        -- Check that all inserted files match whitelist, otherwise throw error.
        IF EXISTS (
            SELECT *
            FROM Inserted i
            WHERE NOT EXISTS(
                SELECT *
                FROM dbo.stbl_System_FilesExtensionWhitelist wl WITH (NOLOCK)
                WHERE wl.[extension] = i.Extension and wl.[Allowed] = 1
            )
        )
        BEGIN
            throw 50002, 'File extension did not match the whitelist', 1;
        END
    

There is a UI for this in Appframe 365 in the system-setup article (Accepted File Extensions).

To disable

Remove all rows from stbl_System_FilesExtensionWhitelist table

Technical

New / Upgraded components required to stop upload of non whitelisted file types:

  • Table stbl_System_FilesExtensionWhitelist - New table containing allowed extensions
  • Trigger stbl_System_Files_ITrig - Added Check for file extension(s) against whitelist table if whitelist table contains any rows.

Upgraded components required to avoid being able to manipulate the system by renaming file extension:

  • Trigger stbl_System_Files_UTrig - Added checking of new file extension(s) against whitelist table when extension is updated (only if whitelist table contains any rows)
  • Templates for generating File Table ITrig and UTrig - Checks new file extension(s) against whitelist table when extension is inserted updated (only if whitelist table contains any rows)

Related articles

Placeholder "LocalizeWeb2016" failed