Named placeholders #1

New Issue

paul · 2024-06-20T19:32:01+02:00

paul commented

2024-06-20 19:32:01 +02:00

opium is based on libpq, which only supports numbered placeholders out of the box, i.e.

execute "INSERT INTO users VALUES ($1, $2)" ("blorbo", 25)

is possible but

data User = User { name :: String, age :: Int }
execute "INSERT INTO users VALUES ($name, $age)" (User "blorbo" 25)

isn't (note: the placeholder syntax should be considered pseudocode as it depends on the library). One of my goals for this library is supporting the latter, offering a record-centric interface for both serializing and deserializing values. In order to bolt support for this onto libpq, we need a way to transform the query

 INSERT INTO users VALUES ($name, $age, $name)

into

INSERT INTO users VALUES ($1, $2, $1)

There's several issues to consider here:

SQL injection: Modifying query strings always carries the risk of accidentally letting the user control the query. This should not be an issue here as we're still using libpq parameter interpolation for actually inserting the values.
Respecting quotation (see below).

In my opinion, parameters should only be replaced at the term level, not within quoted identifiers or strings, e.g.

INSERT INTO "$name" VALUES ($name, $age)
// should be transformed into
INSERT INTO "$name" VALUES ($1, $2)
// and NOT
INSERT INTO "$1" VALUES ($1, $2)

However, doing this "right" requires that all the quotes in the given query are lexed, i.e. a full lexer for PostgreSQL. Libraries that occupy a similar level of abstraction in other languages usually don't do this either, e.g.

`psycopg2` (Python)

Simple string substitution, see e.g. here.

Uses %(<name>)<format> as named placeholder, where <format> is one of s (auto), t (text) and b (binary).

`sqlx` (Go)

Simple string substitution, see e.g. here.

Uses :<name> as named placeholder. Also does some magic for arrays.

`opium` is based on `libpq`, which only supports numbered placeholders out of the box, i.e. ``` execute "INSERT INTO users VALUES ($1, $2)" ("blorbo", 25) ``` is possible but ``` data User = User { name :: String, age :: Int } execute "INSERT INTO users VALUES ($name, $age)" (User "blorbo" 25) ``` isn't (note: the placeholder syntax should be considered pseudocode as it depends on the library). One of my goals for this library is supporting the latter, offering a record-centric interface for both serializing and deserializing values. In order to bolt support for this onto `libpq`, we need a way to transform the query ```sql INSERT INTO users VALUES ($name, $age, $name) ``` into ```sql INSERT INTO users VALUES ($1, $2, $1) ``` There's several issues to consider here: - SQL injection: Modifying query strings always carries the risk of accidentally letting the user control the query. This should not be an issue here as we're still using `libpq` parameter interpolation for actually inserting the values. - Respecting quotation (see below). In my opinion, parameters should only be replaced at the term level, not within quoted identifiers or strings, e.g. ```sql INSERT INTO "$name" VALUES ($name, $age) // should be transformed into INSERT INTO "$name" VALUES ($1, $2) // and NOT INSERT INTO "$1" VALUES ($1, $2) ``` However, doing this "right" requires that all the quotes in the given query are lexed, i.e. a full lexer for PostgreSQL. Libraries that occupy a similar level of abstraction in other languages usually don't do this either, e.g. ### `psycopg2` (Python) Simple string substitution, see e.g. [here](https://github.com/psycopg/psycopg/blob/69ad4112cf25d64c3dc2c1272e4a1e0a860141b0/psycopg/psycopg/_queries.py#L347). Uses `%(<name>)<format>` as named placeholder, where `<format>` is one of `s` (auto), `t` (text) and `b` (binary). ### `sqlx` (Go) Simple string substitution, see e.g. [here](https://github.com/jmoiron/sqlx/blob/41dac167fdad5e3fd81d66cafba0951dc6823a30/named.go#L331). Uses `:<name>` as named placeholder. Also does some magic for arrays.

paul commented

2024-06-20 19:37:10 +02:00

Named placeholders #1

psycopg2 (Python)

sqlx (Go)

Suggested Solution

`psycopg2` (Python)

`sqlx` (Go)