Sapphire Project Sp. z o.o.
Brilliancy of quality
Practical syntax of SQLMacros for managing DB queries
Documentation

Practical syntax of SQLMacros for managing DB queries

Sapphire I.C.D.S.

Purpose and scope of SQLMacros

SQLMacros is a templating layer for SQL in Sapphire I.C.D.S. A regular query is stored in the catalog SQL/<Module>/<dialect>/, receives a prepared table of values, generates the final SQL text, and passes it to the active database driver. SQLMacros does not open a connection, select a backend, start a transaction, or decide which query is allowed to execute. These responsibilities remain with the module and the DB layer.

For MySQL and PostgreSQL, maintain separate files even if the queries are similar. Identifier quotes, date functions, type conversions, upsert, and aggregates differ. Attempting to hide two dialects in one complex template is usually worse than two short, verifiable files.

Basic Table.Data substitution

The following examples use an illustrative scheme with tables example_articles and example_article_notes; these are not real Sapphire I.C.D.S. tables.

The main source of input data is Table.Data("field"). For a single row, the cursor is usually already prepared by the calling party. Example selection with numeric parameters:

SELECT id, topic, created_at
FROM example_articles
WHERE layout_id = {{ Table.Data("layout_id") }}
  AND language_id = {{ Table.Data("language_id") }}
ORDER BY id DESC
LIMIT {{ Table.Data("limit") }} OFFSET {{ Table.Data("offset") }};

Only values created as integers and checked by range can be output this way. The user string "10; DELETE ..." should not reach the numeric field. The macro does not convert arbitrary text into a safe number.

String literals and escape

The string is placed in SQL quotes, and the value is passed through the active DB escape provider:

SELECT id, topic
FROM example_articles
WHERE status = '{{ escape(Table.Data("status")) }}'
  AND LOWER(topic) LIKE CONCAT('%', LOWER('{{ escape(Table.Data("query")) }}'), '%');

escape(...) and compatible variant Escape(...) escapes the content for the selected driver. Quotes around the string literal remain part of the SQL template. Do not use HTML escaping, manual apostrophe replacement, or URL encoding instead of DB escape.

Escaping a value does not allow it to be an identifier. Never write ORDER BY {{ Table.Data("sort") }}, FROM {{ ... }} or a dynamic column name from the user's query. Allowed sorting and columns are selected in the module via an allowlist, after which a specific static template is called or a pre-known safe fragment is passed via a separate contract.

Conditions in the template

Control structures are written between {% and %}. They are useful for a small number of optional filters:

SELECT id, topic, status
FROM example_articles
WHERE layout_id = {{ Table.Data("layout_id") }}
{% if Table.Data("status") %}
  AND status = '{{ escape(Table.Data("status")) }}'
{% endif %}
{% if Table.Data("query") %}
  AND LOWER(topic) LIKE CONCAT('%', LOWER('{{ escape(Table.Data("query")) }}'), '%')
{% endif %}
ORDER BY id DESC
LIMIT {{ Table.Data("limit") }};

Ensure that any set of conditions leaves a syntactically correct query. It is convenient to start with a mandatory WHERE, and add optional parts using AND. If there are many branches, it is better to create several logical queries: this is easier to test and safer to maintain.

The template comment has the form {# explanation #} and does not appear in the final SQL. A regular -- SQL comment remains in the query. Do not store secrets, real personal data, or temporary rights bypasses in comments.

INSERT and UPDATE

INSERT INTO example_article_notes (article_id, note, created_at)
VALUES ({{ Table.Data("article_id") }},
        '{{ escape(Table.Data("note")) }}',
        NOW());

Here article_id should be a positive number from a trusted structured table, and note — a string via escape. Owner verification, user rights, article existence, and note length are checked before the query. SQLMacros does not add this policy automatically.

For update/delete, always include the exact entity restriction and, where required, the owner or layout. Mass modification without WHERE is not acceptable because it is stored in the template. For concurrent editing, use the revision/transaction contract adopted by the module, not a homemade string.

Pager: data query and count

A paginated list usually has two templates: the main query with LIMIT/OFFSET and a file *.count.sql. The filtering conditions in them must match. Otherwise, Pager will show an incorrect number of pages. Convenient order of checking:

  1. copy only the filter block to the count query;
  2. check empty search;
  3. check string with apostrophe and percent symbols;
  4. check limit 1, last page, and offset outside the result;
  5. compare the number of rows from the main query with count for the same input.

Environment, POST, and Cookie

SQLMacros technically provides environment and request values through calls like Env.Get(...), Post.Get(...) and Cookie.Get(...). For regular application SQL logic, do not access them directly. The module must read the HTTP input, check the type, normalize the value, apply rights, and place the result in Table. This path makes SQL independent of the transport and suitable for CGI, FastCGI, AJAX, service, or test.

What constitutes a safe template

  • The structure of SQL, tables, columns, and operators are written statically.
  • Each string passes through escape(Table.Data(...)) inside a string literal.
  • Numbers, flags, limit, and offset are typed and restricted before rendering.
  • The template resides in the correct module and dialect catalog.
  • Query selection and execution permission remain with the module.
  • Tests and diagnostics confirm that a successful result does not contain unrevealed {{ ... }} or {% ... %}.
  • The current runtime upon failure of SQLMacros may return the original template text; the calling DB layer does not guarantee separate fail-closed blocking. Do not consider rendering as a security boundary: such errors must be detected by tests and logs before production.

Test SELECT on test data, and modify queries within a controlled transaction or on an isolated database. Do not test new DELETE/UPDATE on production. SQLMacros reduces duplication and centralizes escaping, but security only arises with typed input, server authorization, and static query structure.

Diagnosis of render result

In case of error, first record the logical name of the query, dialect, and set of input fields without secret values. Then obtain the final SQL in a safe diagnostic context and ensure that all macros have been resolved, string quotes are balanced, and optional branches yielded the expected text. Do not fix the problem by adding random quotes around numbers: return to the field type and the contract of the calling module.

For a new query, prepare boundary cases: empty string, apostrophe, Unicode, zero result, maximum allowed length, minimum and maximum identifier. The check must cover both supported dialect files. If one backend is not used locally, the query should still not be considered ready until syntactic or integration testing in the appropriate environment.