How can I access files (like a SQLite database) outside of my app's folder in a UWP application?

Question

I know that UWP applications can use SQLite databases within their own AppData directory, but I would like to access a SQLite database that the user picks from another location (such as their Downloads directory). I could copy the database to my app's directory and open it there, but if it's large then the copy will take a long time, or if the user modifies the database then I have to copy it back etc. and I don't want to manage that complexity.

I know that UWP apps can have access to files outside their own private directories if the user picks the file with a FileOpenPicker or the app has the broadFileSystemAccess capability, but that only applies to StorageFile objects and not to existing libraries like SQLite that just take a filename as an argument. I also know I could build a "Full Trust" Packaged Win32 Desktop application, but I'd like to build a UWP app that runs on other platforms.

Is there anything new in UWP that can help?

Answer 1

There are several new features in UWP that can solve the specific problem of opening SQLite databases. The general technique used here can solve some other UWP file access problems, but not all of them -- see caveat at the end.

The first feature that makes this possible is the ...FromApp APIs introduced in Windows 10 version 1803. These are variations of older Win32 APIs like CreateFileW and DeleteFileW that work from within an AppContainer (the security context in which a UWP app runs) and allow access to files outside of the app's private directories. If you are writing new code from scratch, calling these APIs instead of the older ones ensures your code will "just work" from a UWP context. Although MSDN doesn't have great documentation on these yet, you can find them in the fileapifromapp.h header in the Windows SDK. Modifying the SQLite codebase to use these newer APIs will make it "just work" for you (see below for the APIs to change).

But what if you don't want to recompile SQLite, or you're using a different library for which you don't have the source code?

This is where the second feature that makes this possible comes in handy -- API Redirection, introduced in Windows 10 version 1809. This feature allows a UWP application to "redirect" the API imports from its own DLLs and to call different APIs instead. So if you have a DLL in your project that tries to call CreateFileW and you want it to call CreateFileFromAppW instead, that's now possible. No modifications to the source code or the compiled DLL are needed.

API Redirection relies on a DLL in your package that exports a special table named __RedirectionInformation__. This table lists the set of APIs to be replaced, and the functions to call instead. The functions to be called instead are implemented inside the DLL itself.

How does it work?

First, the redirection file. Create a C++ UWP DLL and add the following code into the main CPP file. Let's assume this project produces an output named AppRedirections.dll:

#include "pch.h"

#ifndef WIN32_LEAN_AND_MEAN
#define WIN32_LEAN_AND_MEAN
#endif

#include <windows.h>
#include <fileapifromapp.h>

// Same signature are CreateFile2, forward it on to ...FromApp
HANDLE WINAPI CreateFile2Forwarder(LPCWSTR lpFileName, DWORD dwDesiredAccess,
  DWORD dwShareMode, DWORD dwCreationDisposition, LPCREATEFILE2_EXTENDED_PARAMETERS pCreateExParams)
{
  return CreateFile2FromAppW(lpFileName, dwDesiredAccess, dwShareMode, dwCreationDisposition, pCreateExParams);
}

// Same signature are DeleteFileW, forward it on to ...FromApp
BOOL WINAPI DeleteFileWForwarder(LPCWSTR lpFileName)
{
  return DeleteFileFromAppW(lpFileName);
}

// Same signature are GetFileAttributesExW, forward it on to ...FromApp
BOOL WINAPI GetFileAttributesExWForwarder(LPCWSTR lpFileName, GET_FILEEX_INFO_LEVELS fInfoLevelId,
  LPVOID lpFileInformation)
{
  return GetFileAttributesExFromAppW(lpFileName, fInfoLevelId, lpFileInformation);
}

// List of {exporting DLL}, {exported function name}, {replacement function pointer}
const REDIRECTION_FUNCTION_DESCRIPTOR RedirectedFunctions[] =
{
    { "api-ms-win-core-file-l1-2-1.dll", "CreateFile2", &CreateFile2Forwarder },
    { "api-ms-win-core-file-l1-2-1.dll", "DeleteFileW", &DeleteFileWForwarder },
    { "api-ms-win-core-file-l1-2-1.dll", "GetFileAttributesExW", &GetFileAttributesExWForwarder },
};

// The exported table, with version and size information.
extern "C" __declspec(dllexport) const REDIRECTION_DESCRIPTOR __RedirectionInformation__ =
{
    1, // version number of the structure
    ARRAYSIZE(RedirectedFunctions),
    RedirectedFunctions
};

This file re-directs the three APIs CreateFile2, DeleteFileW, and GetFileAttributesExW from the API Set api-ms-win-core-file-l1-2-1.dll (these are the three APIs needed to make SQLite work - at least for basic operations). Note that the APIs implementing the redirections don't have to be exported, since nobody is linking directly to them (although you can export them if you like).

Next, make sure to include AppRedirections.dll in the UWP app project that is using SQLite. Typically you can just "Add Reference..." to the redirection project from your main project.

Now add / update the following entries to your Package.appxmanifest file (or AppXManifest.xml if you're not using Visual Studio). You'll need to right-click and "Open with..." the XML editor since the designer doesn't support adding this functionality.

<Package
  [other stuff]
  xmlns:uap7="http://schemas.microsoft.com/appx/manifest/uap/windows10/7" 
  IgnorableNamespaces="[other stuff] uap7">

  [more stuff...]

  [place after 'VisualElements']
  <uap7:Properties>
    <uap7:ImportRedirectionTable>AppRedirections.dll</uap7:ImportRedirectionTable>
  </uap7:Properties>
</Application>

This tells Windows that when it loads your app, it should first load the AppRedirections.dll file, process the redirection table, and then fix-up all future imports it sees for the rest of the files in your package. Note that if you get the filename wrong, or Windows can't find the file, or it doesn't export the redirection table correctly, your app will fail to activate (launch).

Assuming you have SQLite3.dll in your package (and it's the same version I tested with) you will now be able to open SQLite databases with code such as the following - note the required use of the FutureAccessList to "prove" you have the right to access the file:

#include <sqlite3.h>
#include <ppltasks.h>

// ...

sqlite3* db;

void OpenDatabase()
{
  using namespace Windows::Storage;
  using namespace Windows::Storage::Pickers;
  using namespace Windows::Storage::AccessCache;

  auto picker = ref new FileOpenPicker();
  picker->FileTypeFilter->Append(L".db");
  picker->SuggestedStartLocation = PickerLocationId::Desktop;
  concurrency::create_task(picker->PickSingleFileAsync()).then([](StorageFile^ pickedFile)
  {
    // StorageFile *must* be added to the future access list to ensure the Win32 APIs can grant access.
    StorageApplicationPermissions::FutureAccessList->Add(pickedFile);

    // now SQLite "just works"... carry on from here
    int err = sqlite3_open16(pickedFile->Path->Data(), &db);
  });
}

Now your UWP application should work with external SQLite database files. A similar technique could be used with other libraries, with the following caveats (as of December 2019):

1. The redirections only apply to DLLs in the app's package graph (that is, the app and any Framework Packages it uses).
2. The redirections do not apply to functions accessed via GetProcAddress; they only work for functions directly listed in the import table.

The first limitation means that functions in system-provided DLLs will not get redirected, so you must include a version of sqlite3.dll in your app rather than rely on the system-provided one (this is the default behaviour anyway). It also means that whilst you can redirect APIs from within the VCLibs Framework Package, you cannot redirect APIs from ucrtbase.dll... this means that this technique currently doesn't work if the app uses fopen or std::fstream etc. You can statically-link the CRT into your application to solve this problem, but it might not pass Store Certification (if you care about the Microsoft Store).

The second limitation mostly affects .NET code, since the CLR relies on LoadLibrary / GetProcAddress for resolving P/Invoke calls (although some version-adaptive C/C++ libraries use GetProcAddress as well). Note that the .NET Native compiler generates proper DLL import tables, but the normal debug builds (F5) won't work.