🦆
ws4sqlite
v0.16
v0.16
  • 🌱Introduction & Credits
  • 🥇Features
    • 🚄Performances
  • 🏫Tutorial
  • 🔦Cheat Sheet
  • 🏗️Building & Testing
  • 🛡️Security
  • 📖Documentation
    • 🔧Installation
      • 🐳Docker
    • 🏃Running
    • 📃Configuration File
    • 🔓Authentication
    • 📦Stored Statements
    • ❓Requests
    • ❗Responses
    • ❌Errors
    • 🔨Scheduled Tasks
    • 🌐Embedded Web Server
  • 💡Client Libraries
  • 🎓Advanced Topics
  • 🪐Integrations
    • ⚙️Reverse Proxy
  • ⚖️License
  • 👷GitHub Repository
  • 🚁Support on Discord
Powered by GitBook
On this page
Edit on GitHub
  1. Documentation

Stored Statements

PreviousAuthenticationNextRequests

Last updated 1 year ago

As stated while discussing the , Stored Statements are a way to specify (some of) the statement/queries you will use in the server instead of sending them over from the client.

This can be done to shorten the requests from the client; a more important reason is for security: when coupled with the next parameter, this allows the server to actually limit the SQL tat is performed to a set of predefined, controlled values. If your db contains areas that are sensitive, and you don't want to expose them, this can be very effective. See also the .

Configuration

For any database, you can add a similar piece of YAML to specify one or more stored statements:

storedStatements:
  - id: Q1
    sql: SELECT * FROM TABLE_1
  - id: Q2
    sql: SELECT * FROM TABLE_2 WHERE UPPER(cf) = UPPER(:cf)

As you can see, each stored statement has an ID that you can use to refer to it from a request.

Usage in requests

Simply specify the ID, prepended by a # sign, instead of any SQL:

{
    "transaction": [
        {
            "query": "#Q1"
        }
    ]
}

Of course, despite the term, it's not limited to statements, but also querys works the same. "Statement" is just used in a generic sense here.

Limiting the server to executing stored statements

Specify the relevant configuration (line 2):

[...]
useOnlyStoredStatements: true
storedStatements:
[...]

This way, the server will not accept any SQL from the client, but will only allow Stored Statements.

A possible use case is this: you have a database with a table that lists some sensitive data for a key.

CREATE TABLE MY_SENSITIVE_TABLE (
  MY_KEY     TEXT NOT NULL PRIMARY KEY,
  MY_SECRET  TEXT NOT NULL
)

You want to avoid that the whole table is extracted, but still keep it accessible for queries for a single key.

Just define a query, and restrict the server to it:

[...]
useOnlyStoredStatements: true
storedStatements:
  - id: Q1
    sql: SELECT MY SECRET FROM MY_SENSITIVE_TABLE WHERE MY_KEY = :key

From the client, anyone will only be allowed to perform a lookup by key, so they'll need to know the key in order to access the data. No other statements are allowed, and no SQL can be passed from the client.

📖
📦
relevant section
configuration file