Suspicious type casts when working with PostgreSQL

[person1534]

Introduction

Example User Management App casts form field parameters to the type TEXT and then compares them against INTEGER columns

To Reproduce

Create user and address tables in PostgreSQL and point SQLPage to a database

CREATE TABLE IF NOT EXISTS public."user"
(
    id integer NOT NULL,
    first_name text COLLATE pg_catalog."default" NOT NULL,
    last_name text COLLATE pg_catalog."default" NOT NULL,
    email text COLLATE pg_catalog."default",
    CONSTRAINT user_pkey PRIMARY KEY (id)
)
TABLESPACE pg_default;

CREATE TABLE IF NOT EXISTS public.address
(
    id integer NOT NULL,
    user_id integer NOT NULL,
    street text COLLATE pg_catalog."default" NOT NULL,
    city text COLLATE pg_catalog."default" NOT NULL,
    country text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT address_pkey PRIMARY KEY (id),
    CONSTRAINT address_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
TABLESPACE pg_default;

Actual behavior

Failed to execute SQL statement: SELECT 'First name' AS name, true AS required, (SELECT first_name FROM public.user WHERE id = CAST($1 AS TEXT)) AS value ⬆️ line 1, character 93

operator does not exist: integer = text

Screenshots

Expected behavior

I would suppose there's a way to figure out actual column types and avoid converting any imaginable form field to TEXT as it happens for now.

Version information

• OS: Docker
• Database: PostgreSQL 15.5
• SQLPage Version: main

[lovasoa]

Hello and welcome to SQLPage !

I suppose you are referencing the master-details-forms example ? The example was written for SQLite, but I realize I hadn't mentioned that anywhere ! I just updated the README and changed the parts of the example you mentioned, that relied on SQLite's flexible typing, which is very different from what postgres has: https://github.com/lovasoa/SQLpage/commit/2b6b981dfe79e12460ad275421a130b5701958d3 .

The CASTs generated by SQLPage are not a bug, they serve as a cross-database way to indicate parameters type (which are always text) to the database at statement preparation time. This is not very beautiful, but I think it's the best we can do. Web browsers don't send typing information with URL parameters and form data, everything is transferred over http as a simple sequence of bytes. SQLPage doesn't try to be smart and just passes the data as is to your sql statement. When you need to parse the data (as an integer, in your case), you have to do it SQL. In most cases, in postgres, what this means is you'll want to use

select * where id = $id::int

instead of just

select * where id = $id