Problems with new fetch function

[DSMejantel]

Introduction

I've tried the fetch function to add school to my application. But something goes wrong. It is on a localhost server for testing.

To Reproduce

select 
    'form'            as component,
    'TEST' as title,
    'etab_ajout_v2.sql?user_search='||$recherche as action,
    'Chercher sur la carte'     as validate;

select 
    'recherche' as name;

set url = 'https://nominatim.openstreetmap.org/search?format=json&q=' || sqlpage.url_encode($user_search);
set api_results = sqlpage.fetch($url);

select 'map' as component;
select $user_search as title,
  CAST($api_results->>0->>'lat' AS FLOAT) as latitude,
  CAST($api_results->>0->>'lon' AS FLOAT) as longitude;

Actual behavior

My error message :

Failed to set the GetOrPost("api_results") variable to StmtWithParams { query: "SELECT CAST(? AS TEXT)", params: [Fetch(GetOrPost("url"))] }

Unable to fetch https://nominatim.openstreetmap.org/search?format=json&q=Mende: Failed to connect to host: invalid peer certificate contents: invalid peer certificate: UnknownIssuer

Version information

• OS: Ubuntu
• Database [ SQLite]
• SQLPage Version [0.20.3]:

[lovasoa]

The currently released version of sqlpage does not include built-in certificates to validate https connections. It relies on the root certificates present on the server where it is running.

You can update these certificates with

sudo apt-get update
sudo apt-get install --reinstall ca-certificates

sudo update-ca-certificates --fresh

additionally, sqlpage currently doesn't set an user-agent by default, so you will have to set one yourself if you want to access nominatim:

select 
    'form' as component,
    'GET' as method,
    'Chercher sur la carte'  as validate;
select 'user_search' as name, $user_search as value;

set url = '{
    "url": "https://nominatim.openstreetmap.org/search?format=json&q=' || sqlpage.url_encode($user_search) ||'",
    "headers": {"user-agent": "ecole-inclusive/1.0"} 
}'
set api_results = sqlpage.fetch($url);

select 'map' as component;
select $user_search as title,
  CAST($api_results->>0->>'lat' AS FLOAT) as latitude,
  CAST($api_results->>0->>'lon' AS FLOAT) as longitude;

I'll publish a new version with built-in root certificates and a default user agent.

[lovasoa]

@DSMejantel, can you try again with the new v0.20.4 ? It should contain the root certificates inside the sqlpage binary (and thus work even if the certificates on your machine are out of date or inexistant), and set a User-Agent header by default (so the nominatim api won't reject it).

[DSMejantel]

It works on an https server with 0.20.3 But not on my computer at localhost:8080 with 0.20.4

`Error We are sorry, but an error occurred while generating this page. You should contact the site's administrator. Error in query number 2: Failed to set the GetOrPost("api_results") variable to StmtWithParams { query: "SELECT CAST(? AS TEXT)", params: [Fetch(GetOrPost("url"))] }

Backtrace Unable to fetch { "url": "https://nominatim.openstreetmap.org/search?format=json&q=Mende", "headers": {"user-agent": "ecole-inclusive/1.0"} }: Failed to connect to host: invalid peer certificate: UnknownIssuer`

[lovasoa]

Do you have a proxy or a special network set up on the machine that may be trying to manipulate your requests ?

[DSMejantel]

You are a good and clever investigator. You're right. I was hinter the proxy of my school. It wasn't a good idea to code during a meeting during my working hours !