Introduction
I've been trying using v.40.0 and now, just released v.41.0. In both versions, I cannot pass any parameter values through a URL which would be properly seen by a Postgresql database. I am using currently Postgres 18.
To Reproduce
It can be reproduced with any table you might have in the existing postgres database.
Partial sql from my index.sql page:
-- Daily Summary Table (31 rows per page)
SELECT 'table' AS component, 'Daily Summary' AS title, TRUE AS sort, TRUE AS search, 'top' AS search_position;
-- Data rows (paginated)
SELECT
"Date_Collected"::date AS "Date",
"TemperatureHighF" AS "Temp High (°F)",
"TemperatureAvgF" AS "Temp Avg (°F)",
"TemperatureLowF" AS "Temp Low (°F)",
"HumidityAvg" AS "Humidity (%)",
"DewpointHighF" AS "Dew Pt (°F)",
"WindSpeedAvgMPH" AS "Wind Avg (mph)",
"PrecipitationSumIn" AS "Rain (in)"
FROM homedb.t_acurite_temps
ORDER BY "Date_Collected" DESC
LIMIT 31
OFFSET GREATEST(0, (COALESCE(:page::integer, 1) - 1) * 31);However, an even easier way to check this behavior is simply to create a sql page that queries your database for a value, for example, I tried it with the date: 2025-12-01 as passed as a parameter in URL. For example:
http://localhost:8080/index.sql?selected_date=2025-01-01
The selected_date parameter passed through the URL is not passed by sqlpage and it will be reported as NULL in Postgresql log. This is serious as this is the stuff that breaks your apps.
For example:
select * from mytable
where local_date::date = :selected_date
This will return 0 results.
So I noticed sqlpage casts everything to TEXT (why?), so obviously the above would have to be changed to something like:
where local_date::date = :selected_date::date
The same problem happens as sqlpage is NOT passing the URL parameter to the Postgresql database. I saw another but opened to OFFSET..LIMIT related to MySQL. Are things starting to break for sqlpage?
-- Pagination controls (after data)
SELECT 'pagination' AS component;
SELECT 'First' AS title, 'index.sql?page=1' AS link, COALESCE(:page::integer, 1) = 1 AS active;
SELECT 'Previous' AS title, 'index.sql?page=' || GREATEST(1, COALESCE(:page::integer, 1) - 1) AS link, COALESCE(:page::integer, 1) > 1 AS active;
SELECT 'Next' AS title, 'index.sql?page=' || (COALESCE(:page::integer, 1) + 1) AS link;
SELECT 'Last' AS title, 'index.sql?page=last' AS link;
The expected behavior after either pressing a button below the page (2, 3, .., last) is to advance the table data by 31 records as per query above. However nothing happens and the same records are displayed because the :page variable passed by sqlpage.bin to Postgres is NULL as seen in postgres logs:
2025-12-27 18:32:25.848 [1311517] LOG: execute sqlx_s_2661972343: SELECT "Date_Collected"::DATE AS "Date", "TemperatureHighF" AS "Temp High (°F)", "TemperatureAvgF" AS "Temp Avg (°F)", "TemperatureLowF" AS "Temp Low (°F)", "HumidityAvg" AS "Humidity (%)", "DewpointHighF" AS "Dew Pt (°F)", "WindSpeedAvgMPH" AS "Wind Avg (mph)", "PrecipitationSumIn" AS "Rain (in)" FROM homedb.t_acurite_temps ORDER BY "Date_Collected" DESC LIMIT 31 OFFSET GREATEST(0, (COALESCE(CAST($1 AS TEXT)::INTEGER, 1) - 1) * 31);
2025-12-27 18:32:25.848 [1311517] DETAIL: Parameters: $1 = NULL
2025-12-27 18:32:25.848 [1311517] LOG: duration: 0.055 ms
2025-12-27 18:32:25.852 [1311517] LOG: duration: 0.020 ms bind sqlx_s_2661972344: SELECT 'First' AS title, 'index.sql?page=1' AS link, COALESCE(CAST($1 AS TEXT)::INTEGER, 1) = 1 AS active;
2025-12-27 18:32:25.852 [1311517] DETAIL: Parameters: $1 = NULL
2025-12-27 18:32:25.852 [1311517] LOG: execute sqlx_s_2661972344: SELECT 'First' AS title, 'index.sql?page=1' AS link, COALESCE(CAST($1 AS TEXT)::INTEGER, 1) = 1 AS active;
2025-12-27 18:32:25.852 [1311517] DETAIL: Parameters: $1 = NULL
2025-12-27 18:32:25.852 [1311517] LOG: duration: 0.009 ms
2025-12-27 18:32:25.852 [1311517] LOG: duration: 0.057 ms bind sqlx_s_2661972345: SELECT 'Previous' AS title, 'index.sql?page=' || GREATEST(1, COALESCE(CAST($1 AS TEXT)::INTEGER, 1) - 1) AS link, COALESCE(CAST($1 AS TEXT)::INTEGER, 1) > 1 AS active;
2025-12-27 18:32:25.852 [1311517] DETAIL: Parameters: $1 = NULL
2025-12-27 18:32:25.852 [1311517] LOG: execute sqlx_s_2661972345: SELECT 'Previous' AS title, 'index.sql?page=' || GREATEST(1, COALESCE(CAST($1 AS TEXT)::INTEGER, 1) - 1) AS link, COALESCE(CAST($1 AS TEXT)::INTEGER, 1) > 1 AS active;
2025-12-27 18:32:25.852 [1311517] DETAIL: Parameters: $1 = NULL
2025-12-27 18:32:25.852 [1311517] LOG: duration: 0.009 ms
2025-12-27 18:32:25.852 [1311517] LOG: duration: 0.028 ms bind sqlx_s_2661972346: SELECT 'Next' AS title, 'index.sql?page=' || (COALESCE(CAST($1 AS TEXT)::INTEGER, 1) + 1) AS link;
2025-12-27 18:32:25.852 [1311517] DETAIL: Parameters: $1 = NULL
2025-12-27 18:32:25.852 [1311517] LOG: execute sqlx_s_2661972346: SELECT 'Next' AS title, 'index.sql?page=' || (COALESCE(CAST($1 AS TEXT)::INTEGER, 1) + 1) AS link;
2025-12-27 18:32:25.852 [1311517] DETAIL: Parameters: $1 = NULL
2025-12-27 18:32:25.852 [1311517] LOG: duration: 0.008 ms
Actual behavior
After following these steps, what happened ?
The same set of records is displayed. No advance by X number is made.
There are no errors
Log generated by sqlpage.bin:
[2025-12-28T00:32:25.238Z INFO actix_web::middleware::logger] 192.168.1.169 "GET /html,%20body%20%7B%20overflow-y:%20scroll;%20%7D%20%20%20%20%20%20%20form%20button[type=%22submit%22]%20%7B%20display:%20none;%20%7D%20%20%20%20%20%20%20.pagination%20a%20%7B%20color:%20 HTTP/1.1" 404 14 "http://localhost:8080/index.sql?page=4" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.7024.86 Safari/537.36 Edg/136.0.2958.104" 0.000310
[2025-12-28T00:32:25.852Z INFO actix_web::middleware::logger] 192.168.1.169 "GET /index.sql?page=4 HTTP/1.1" 200 22984 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.7024.86 Safari/537.36 Edg/136.0.2958.104" 0.703225
Expected behavior
A returned page of records returned should be advanced or retreated by the OFFSET :page value.
Version information
- OS: Linux Mint, Zara 22.2
- Database Postgres 18
- SQLPage sqlpage 0.41.0
Introduction
I've been trying using v.40.0 and now, just released v.41.0. In both versions, I cannot pass any parameter values through a URL which would be properly seen by a Postgresql database. I am using currently Postgres 18.
To Reproduce
It can be reproduced with any table you might have in the existing postgres database.
Partial sql from my index.sql page:
However, an even easier way to check this behavior is simply to create a sql page that queries your database for a value, for example, I tried it with the date: 2025-12-01 as passed as a parameter in URL. For example:
http://localhost:8080/index.sql?selected_date=2025-01-01
The selected_date parameter passed through the URL is not passed by sqlpage and it will be reported as NULL in Postgresql log. This is serious as this is the stuff that breaks your apps.
For example:
This will return 0 results.
So I noticed sqlpage casts everything to TEXT (why?), so obviously the above would have to be changed to something like:
The same problem happens as sqlpage is NOT passing the URL parameter to the Postgresql database. I saw another but opened to OFFSET..LIMIT related to MySQL. Are things starting to break for sqlpage?
The expected behavior after either pressing a button below the page (2, 3, .., last) is to advance the table data by 31 records as per query above. However nothing happens and the same records are displayed because the :page variable passed by sqlpage.bin to Postgres is NULL as seen in postgres logs:
Actual behavior
After following these steps, what happened ?
The same set of records is displayed. No advance by X number is made.
There are no errors
Log generated by sqlpage.bin:
Expected behavior
A returned page of records returned should be advanced or retreated by the OFFSET :page value.
Version information