SQLite-Based HTTP Application Server: Preventing SQL Injection and Handling HTTP Complexity

Issue Overview: SQLite as an HTTP Application Server with SQL Injection Prevention

The core idea revolves around using SQLite as the backbone for an HTTP application server where SQL scripts handle HTTP requests directly, eliminating the risk of SQL injection. The proposed architecture involves creating temporary tables (qp for query parameters and reply for constructing HTTP responses) and running predefined SQL scripts stored in a protected table (app). The goal is to ensure that SQL injection is impossible by design, as SQL lacks an eval()-like functionality that could dynamically construct queries from user inputs.

However, this approach introduces several challenges and complexities. First, the assumption that query parameter names are unique is flawed, as HTTP requests often contain duplicate parameter names (e.g., multi-select form inputs). Second, the proposed schema for handling HTTP responses (reply table) may not account for the full complexity of HTTP, such as headers, content negotiation, and streaming responses. Third, the reliance on temporary tables raises concerns about performance, concurrency, and resource management, especially under high load or during HTTP request storms.

Additionally, the lack of native JSON support in SQLite complicates the construction of API responses, which are increasingly expected to be in JSON format. The absence of PostgreSQL-like functions such as ROW_TO_JSON() makes it cumbersome to return structured data directly from SQL queries. Finally, the proposal lacks a mechanism for handling procedural logic, which is often necessary for complex web applications, and raises questions about the feasibility of reimplementing something akin to mod_plsql for SQLite without stored procedures or procedural language support.

Possible Causes: Flaws in the Proposed Architecture and Assumptions

The issues stem from several underlying assumptions and architectural decisions in the proposed design. First, the assumption that query parameter names are unique is incorrect. HTTP requests frequently include duplicate parameter names, especially in forms with multi-select inputs or checkboxes. This violates the primary key constraint in the proposed qp table, which uses name TEXT PRIMARY KEY. This design flaw would cause SQLite to reject valid HTTP requests, breaking the application.

Second, the proposed reply table schema is overly simplistic. It assumes that HTTP responses can be constructed by concatenating rows with integer sequence values, but this ignores the complexity of modern web applications. HTTP responses often require headers (e.g., for content negotiation, caching, or security tokens), streaming content (e.g., for large files or dynamic data), and support for multiple content types (e.g., JSON, HTML, images). The current design does not account for these requirements, limiting its practicality.

Third, the reliance on temporary tables introduces performance and concurrency challenges. Temporary tables in SQLite can be either memory-backed or disk-backed, but the choice is currently tied to the database connection pragma and library compile-time options. This lack of flexibility can lead to performance bottlenecks under high load, as disk-backed temporary tables may slow down the application, while memory-backed tables could exhaust system resources during HTTP request storms, potentially causing denial-of-service (DoS) conditions.

Fourth, the absence of native JSON support in SQLite complicates the construction of API responses. While SQLite has basic JSON functions, it lacks advanced features like PostgreSQL’s ROW_TO_JSON(), which simplifies the conversion of query results into JSON format. This limitation makes it cumbersome to implement SQL-based API endpoints, as developers must manually construct JSON responses using string concatenation or custom functions.

Finally, the proposal does not address the need for procedural logic in web applications. Complex applications often require conditional logic, loops, and other procedural constructs that are not easily expressed in pure SQL. While custom SQL functions can provide access to scripting languages, this approach introduces additional complexity and potential security risks if not implemented carefully.

Troubleshooting Steps, Solutions & Fixes: Addressing the Challenges in the Proposed Design

To address these challenges, the proposed architecture must be refined to handle the complexities of HTTP requests and responses, improve performance and concurrency, and support modern web application requirements. Below are detailed solutions and fixes for each issue:

1. Handling Duplicate Query Parameters

The qp table must be redesigned to accommodate duplicate query parameter names. Instead of using name TEXT PRIMARY KEY, the table should use a composite primary key that includes both the parameter name and a unique identifier (e.g., an auto-incrementing integer). This allows multiple values for the same parameter name while maintaining data integrity.

CREATE TEMP TABLE qp(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    value TEXT NOT NULL
);

To retrieve all values for a specific parameter, the SQL script can use a WHERE clause with the parameter name:

SELECT value FROM qp WHERE name = 'param_name';

This approach ensures that the application can handle duplicate query parameters without violating primary key constraints.

2. Enhancing the reply Table for Complex HTTP Responses

The reply table must be extended to support headers, streaming content, and multiple content types. One possible design is to add columns for headers and content type, and to use a combination of sequence values and row IDs to manage the response body.

CREATE TEMP TABLE reply(
    sequence INTEGER,
    content_type TEXT,
    headers TEXT,
    content BLOB
);

To construct the HTTP response, the SQL script can first retrieve the headers and content type:

SELECT content_type, headers FROM reply WHERE sequence = 'mimetype';

Then, it can concatenate the response body using the sequence values:

SELECT content FROM reply WHERE typeof(sequence) = 'integer' ORDER BY sequence, rowid;

This design allows the application to handle complex HTTP responses, including headers and streaming content.

3. Improving Performance and Concurrency with Temporary Tables

To address performance and concurrency issues, the application should allow dynamic configuration of temporary table storage (memory-backed or disk-backed) at runtime. This can be achieved by extending SQLite’s pragma system or using custom functions to switch between storage modes.

For example, a custom SQL function could be implemented to create memory-backed temporary tables:

SELECT create_memory_temp_table('qp');

Alternatively, the application could use a connection pool with preconfigured pragma settings to optimize performance under different load conditions.

4. Adding Native JSON Support for API Responses

To simplify the construction of JSON responses, SQLite should be extended with custom SQL functions that mimic PostgreSQL’s ROW_TO_JSON() and related functions. These functions can be implemented using SQLite’s C API or a scripting language like Python or JavaScript.

For example, a custom row_to_json() function could be defined as follows:

SELECT row_to_json(
    (SELECT json_object('id', id, 'name', name, 'value', value) FROM qp WHERE id = 1)
);

This function would convert a row into a JSON object, making it easier to construct API responses directly from SQL queries.

5. Supporting Procedural Logic with Custom Functions

To handle procedural logic, the application should provide access to scripting languages through custom SQL functions. These functions can be implemented using SQLite’s sqlite3_create_function() API or an external scripting engine.

For example, a custom if_then_else() function could be implemented to support conditional logic:

SELECT if_then_else(condition, true_value, false_value);

Similarly, a custom for_loop() function could be implemented to support loops:

SELECT for_loop(start, end, step, 'SELECT * FROM qp WHERE id = ?');

These functions would allow developers to implement complex procedural logic within SQL scripts, making the application more flexible and powerful.

6. Securing the Application Against Script Manipulation

To prevent unauthorized access or manipulation of the app table, the application should use SQLite’s sqlite3_set_authorizer() API to restrict access during script execution. Additionally, the app table should be stored in a read-only attached database to ensure that scripts cannot be modified at runtime.

For example, the application could attach the app table from a read-only database:

ATTACH DATABASE 'app.db' AS app_db READ ONLY;

Then, it could restrict access to the app table using the authorizer callback:

sqlite3_set_authorizer(db, authorizer_callback, NULL);

This approach ensures that the application remains secure even if a breach occurs.

By addressing these challenges, the proposed SQLite-based HTTP application server can achieve its goal of preventing SQL injection while handling the complexities of modern web applications. The refined architecture provides a robust foundation for building secure, high-performance web applications using SQLite as the backend.