Skip to main content

Run the SQL on your database

Datafala returns the SQL; your system runs it. This page gathers what must be right before going to production.

1. Use a read-only user​

The SQL Guard already guarantees a single SELECT over the registered tables, but the read-only database user is your last line of defense. Grant access only to the tables you registered in the panel and set a maximum time per query, because the Guard limits rows, not cost (a large join between allowed tables is valid).

PostgreSQL:

CREATE ROLE datafala_ro LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE meu_banco TO datafala_ro;
GRANT USAGE ON SCHEMA public TO datafala_ro;
GRANT SELECT ON public.pedidos, public.clientes, public.produtos TO datafala_ro;
ALTER ROLE datafala_ro SET statement_timeout = '5s';
ALTER ROLE datafala_ro SET default_transaction_read_only = on;

MySQL:

CREATE USER 'datafala_ro'@'%' IDENTIFIED BY '...';
GRANT SELECT ON meu_banco.pedidos TO 'datafala_ro'@'%';
GRANT SELECT ON meu_banco.clientes TO 'datafala_ro'@'%';
GRANT SELECT ON meu_banco.produtos TO 'datafala_ro'@'%';
-- Maximum time per SELECT, in milliseconds (per session, when opening the connection):
SET SESSION max_execution_time = 5000;

Firebird 2.5+:

CREATE USER DATAFALA_RO PASSWORD '...';
GRANT SELECT ON TABLE PEDIDOS TO USER DATAFALA_RO;
GRANT SELECT ON TABLE CLIENTES TO USER DATAFALA_RO;

Grant access only to the tables registered in the panel. Configure each connection to start transactions as READ ONLY; in FDB, for example:

import fdb

con = fdb.connect(dsn="localhost:/dados/app.fdb", user="DATAFALA_RO", password="...")
con.begin(fdb.ISOLATION_LEVEL_READ_COMMITED_RO)

Firebird 2.5 and 3.0 do not offer a statement timeout on the server: use the timeout or cancellation provided by the driver and your client. On Firebird 4.0 or newer, you can also set SET STATEMENT TIMEOUT 5 SECOND to limit the duration of the query.

2. Always pass params​

The SQL uses named placeholders in the %(name)s style and the values come in params:

{
  "sql": "SELECT nome FROM clientes WHERE cidade = %(city)s AND nome LIKE 'A%%' LIMIT 100",
  "params": { "city": "Campinas" }
}

Python drivers (psycopg, psycopg2, PyMySQL, mysqlclient) accept this format directly and apply the % formatting to the whole text. That is why every % that is not a placeholder comes escaped as %%. For Firebird, FDB (2.5+) and firebird-driver (3+) use ?; convert the placeholders before running.

Never run without params

Pass params even when it is {}, never None. Without params the driver does not format the SQL, and the %% would reach the database as two characters. And never concatenate user text into the SQL: the values already arrive separately.

3. Convert the placeholders in other languages​

In other drivers, convert %(name)s to the driver's format and %% back to %. FDB, firebird-driver, Jaybird and node-firebird use ? for positional parameters; FireDAC in Delphi accepts named parameters such as :name. Walk the text from left to right recognizing both patterns together, so that a %% followed by parentheses inside a literal is not mistaken for a placeholder:

// %(name)s -> $1, $2... (PostgreSQL) or ? (MySQL/Firebird); %% -> %
export function toPositional(sql, params, style = "pg") {
  const values = [];
  const indexes = new Map();
  const text = sql.replace(/%%|%\(([A-Za-z_][A-Za-z0-9_]*)\)s/g, (match, name) => {
    if (match === "%%") return "%";
    if (!(name in params)) throw new Error(`missing param ${name}`);
    if (style === "pg") {
      if (!indexes.has(name)) {
        values.push(params[name]);
        indexes.set(name, values.length);
      }
      return `$${indexes.get(name)}`;
    }
    values.push(params[name]); // "?" repeats the value at each occurrence
    return "?";
  });
  return { text, values };
}

Full examples in Node.js and other languages.

4. Limit on your side too​

The Guard already applies a LIMIT (default 100, ceiling 1,000, adjustable with max_rows), except in aggregations that return a single row. Even so, use fetchmany or the equivalent so you do not load more than your screen shows.

Data scope per user or company​

Datafala generates SQL from the schema, not from who is asking. SELECT SUM(total) FROM pedidos adds up the orders of all companies if the table has several. If your system is multi-company or has row-level permissions, choose one of these defenses before releasing it to users with partial access:

  1. Row Level Security in PostgreSQL (recommended). Enable RLS on the exposed tables, with a policy that compares the company column with a session variable, and set the variable in the same transaction as the query:

    ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;
    CREATE POLICY pedidos_por_empresa ON pedidos
      USING (empresa_id = current_setting('app.empresa_id')::int);
    with conn.transaction():
        conn.execute("SELECT set_config('app.empresa_id', %s, true)", [str(user.empresa_id)])
        rows = conn.execute(answer["sql"], answer["params"]).fetchall()

    The datafala_ro user must not have BYPASSRLS nor own the tables.

  2. Per-company views. Register in the panel the DDL of views that are already filtered, instead of the tables.

  3. Restricted use. Release the feature only to people who can already see all the data (for example, managers).