SQLite URI Parameters Not Handling Plus Signs as Spaces Encoded in Query Strings

URI Parameter Parsing Discrepancy Between Plus Symbols and Percent-Encoded Spaces

Issue Overview: URI Query Parameter Encoding Mismatch in SQLite

The core problem arises when SQLite processes URIs containing query parameters with spaces represented as plus (+) symbols instead of the percent-encoded %20 format. For example, the URI file:///tmp/whatever?a+name=a+value is not parsed equivalently to file:///tmp/whatever?a%20name=a%20value. This violates expectations from developers accustomed to HTML form encoding conventions where + is treated as a space.

SQLite’s URI handling follows RFC 3986, which explicitly defines space encoding as %20. However, many web-centric systems (e.g., HTML form submissions, URL-encoded POST data) use + as a shorthand for spaces in query parameters. This divergence creates confusion when developers apply web-centric encoding rules to SQLite’s URI-based operations, such as opening database files with parameters or configuring virtual file systems (VFS).

The discrepancy manifests in scenarios where query parameters are passed to SQLite via URIs, particularly when custom VFS implementations or URI-based database connections require URL-encoded key-value pairs. For instance, a parameter like mode=ro+shm might be misinterpreted as mode=ro shm if the developer expects + to decode to a space, whereas SQLite treats + literally.

Key technical details include:

• RFC 3986 Compliance: SQLite adheres strictly to this standard for URI parsing, which does not recognize + as a space.
• HTML Form Encoding Legacy: The application/x-www-form-urlencoded format (used in web forms) allows + for spaces, creating a widespread but non-standard expectation.
• VFS and URI Parameters: SQLite’s Virtual File System interface uses URI query parameters for configuration (e.g., mode, cache, psow). Misencoding these parameters may lead to unintended behaviors.

This issue is exacerbated by the absence of explicit documentation in SQLite about the non-support of +-as-space, leading developers to assume parity with web standards. The lack of error messages or warnings when + is used further obscures the root cause.

Possible Causes: RFC 3986 vs. HTML Form Encoding Expectations

The root cause of the problem lies in conflicting encoding standards and assumptions about how URIs should be parsed:

1. RFC 3986 vs. application/x-www-form-urlencoded:

   • RFC 3986 mandates that spaces in URIs must be percent-encoded as %20. Reserved characters like + are allowed in their literal form unless percent-encoded.
   • HTML Form Encoding (defined in the WHATWG URL Living Standard) permits + as a space substitute in the query string. This convention originated from early web practices and persists due to backward compatibility.

   SQLite’s URI parser follows RFC 3986, treating + as a literal character. When developers use + expecting it to decode to a space (as in web forms), parameters are misinterpreted. For example, a parameter name=John+Doe would be parsed as John+Doe instead of John Doe.

2. Ambiguity in SQLite Documentation:
   The SQLite URI Documentation states that URIs are parsed per RFC 3986 but does not explicitly disclaim support for +-as-space. Developers familiar with web standards may assume SQLite handles + similarly, leading to incorrect parameter encoding.

3. VFS Parameter Handling:
   Custom VFS implementations may rely on query parameters for configuration. If a VFS expects a space-separated value (e.g., mode=ro+wal), the + will not decode to a space, potentially causing parsing errors or misconfigurations.

4. Silent Failure Mode:
   SQLite does not emit warnings or errors when encountering + in query parameters. This silence makes debugging difficult, as developers may not realize their encoding is incorrect.

5. Hybrid Use Cases:
   Applications that blend web frameworks with SQLite (e.g., generating URIs dynamically via web templates) might inadvertently apply HTML form encoding to SQLite URIs, propagating the issue.

Troubleshooting Steps, Solutions & Fixes: Correct Encoding and Workarounds

To resolve URI parameter encoding issues in SQLite, developers must align their encoding practices with RFC 3986 and address misconceptions arising from HTML form conventions. Below are actionable steps:

Step 1: Replace + with %20 in Query Parameters

Always percent-encode spaces as %20 in SQLite URIs. For example:

file:data.db?mode=ro%20wal&cache=shared

This ensures the mode parameter is parsed as ro wal (read-only with write-ahead logging).

Automating the Replacement:

• Use URL-encoding libraries that adhere to RFC 3986. For example, in Python:

  from urllib.parse import quote, urlencode
  params = {"mode": "ro wal", "cache": "shared"}
  encoded_params = urlencode(params, quote_via=quote)
  # Result: "mode=ro%20wal&cache=shared"
  

• Avoid libraries that default to HTML form encoding (e.g., JavaScript’s encodeURIComponent is RFC 3986-compliant, but application/x-www-form-urlencoded utilities like URLSearchParams convert spaces to +).

Step 2: Modify Custom VFS Implementations to Accept + as Literals

If a VFS expects spaces in parameters but receives +, update the VFS logic to either:

• Treat + as a literal (preferred for RFC compliance).
• Manually replace + with spaces during parsing (not recommended, but a pragmatic workaround).

Example (C API):

// In xOpen method of a custom VFS:
const char *mode = sqlite3_uri_parameter(zName, "mode");
if (mode) {
  // Replace '+' with spaces if necessary (caution advised):
  char *mode_decoded = sqlite3_mprintf("%s", mode);
  for (int i = 0; mode_decoded[i]; i++) {
    if (mode_decoded[i] == '+') mode_decoded[i] = ' ';
  }
  // Use mode_decoded...
  sqlite3_free(mode_decoded);
}

Step 3: Educate Teams and Update Documentation

Explicitly document that SQLite URIs require RFC 3986 encoding. Add internal notes or comments wherever URIs are constructed:

# SQLite URI Encoding Guidelines  
- Spaces in query parameters **must** be encoded as `%20`.  
- `+` symbols are treated as literals, not spaces.  
- Example: `file:app.db?query=SELECT%201+2;` encodes `1+2`, not `1 2`.  

Step 4: Use SQLite URI Parsing Functions Correctly

Leverage SQLite’s built-in URI parsing APIs to extract parameters without manual string manipulation:

• sqlite3_uri_parameter(): Retrieves a query parameter value as-is.
• sqlite3_filename_database(), sqlite3_filename_journal(): Handle encoded filenames.

Example (C Code):

sqlite3_open_v2("file:data.db?mode=ro%20wal", &db, SQLITE_OPEN_URI | SQLITE_OPEN_READONLY, NULL);
const char *mode = sqlite3_uri_parameter(sqlite3_filename_database("file:data.db?mode=ro%20wal"), "mode");
// mode = "ro wal"

Step 5: Advocate for RFC 3986 Compliance in Dependent Libraries

If using frameworks that generate SQLite URIs (e.g., ORMs, database connectors), ensure they encode spaces as %20. File issues or submit patches if necessary:

Example (Ruby on Rails):

# Bad: Uses form encoding
uri = "file:data.db?#{{mode: 'ro wal'}.to_query}"
# => "file:data.db?mode=ro+wal"

# Good: Force RFC 3986 encoding
require 'addressable/uri'
query = Addressable::URI.new.tap do |u|
  u.query_values = { mode: 'ro wal' }
end.query
uri = "file:data.db?#{query}"
# => "file:data.db?mode=ro%20wal"

Step 6: Propose Documentation Enhancements

While SQLite’s documentation states adherence to RFC 3986, a brief note about +-as-space could reduce confusion. Submit a documentation patch:

diff --git a/uri.html b/uri.html
--- a/uri.html
+++ b/uri.html
@@ -123,6 +123,9 @@
   key or value contains a '=' character, it must be escaped as %3D.
   Similarly, any '&' character in a key or value must be escaped as %26.
   Whitespace in keys or values must be escaped as %20.
+  Note that the "+" character is not treated as a substitute for whitespace
+  in SQLite URIs, contrary to some URL-encoding conventions. Use %20
+  for spaces.
 </blockquote>

Step 7: Implement Middleware for Legacy Systems

For systems that cannot easily switch from + to %20, introduce a middleware layer to translate parameters:

Example (Node.js Express Middleware):

app.use((req, res, next) => {
  if (req.url.startsWith('/sqlite/')) {
    const parsed = new URL(req.url, `http://${req.headers.host}`);
    parsed.searchParams.forEach((value, key) => {
      parsed.searchParams.set(key, value.replace(/\+/g, '%20'));
    });
    req.url = parsed.pathname + parsed.search;
  }
  next();
});

Step 8: Validate URIs During Development

Use tools like curl, Wireshark, or SQLite’s command-line shell to inspect how URIs are encoded:

# Check URI parsing in SQLite CLI
sqlite3 "file:test.db?mode=rwc&cache=shared&+a=1+2" \
  "SELECT * FROM sqlite3_uri_parameters();"
# Output:
# key    value
# mode   rwc
# cache  shared
# +a     1+2

Final Recommendation:

Adopt RFC 3986 encoding universally for SQLite URIs and deprecate +-as-space in all relevant code paths. While workarounds exist, consistent adherence to the standard prevents subtle bugs and aligns with SQLite’s design philosophy of simplicity and predictability.