Hello,
NEW: I found a workaround although any help would still be welcome.
I’m now using a variable containing the complete SQL command and bind the query to this variable.
I wanted to update a field of a PostgreSQL table. But this formula doesn’t work:
“BEGIN; UPDATE “+ xxx +”.table_example SET name = ‘Roland’, city= ‘Bonn’
WHERE name = ‘Alfred Schmidt’;COMMIT;”
xxx is a variable for the schema name. But it also doesn’t work if I use the schema name as a string.
“UPDATE “+xxx +”.table_example SET name = ‘Roland’, city= ‘Bonn’ WHERE name = ‘Alfred Schmidt’”
doesn’t work either.
WeWeb error: “Invalid or unexpected token”
The same SQL command in DBeaver (or another SQL editor) works fine.
How can I achieve the desired result? BTW: “select * from “+ xxx+”.table_example” works fine.
Note: I will later also replace the values (Roland, etc.) by variables.
Then you can create a workflow or a collection and use this formula when you need to bind a query. You can easily work with variables in the formula to create Dynamic queries. Like this:
I didn’t react immediately because I was kind of shocked. Because safety is the top priority.
In our case, I would like to license the finished program to translation agencies. Each will have its own database. However, the same server is used for all. Only authenticated users can access the database and user interface. I also checked the Chrome developer console and couldn’t find the connection string in plain text. However, the SQL query was visible. But the question is whether this is a security risk. In this context, the question also arises as to how I should generally implement authentication. But that’s another topic.
I think you might want to reach for supabase or xano. Until a certain size, Supabase costs are free. Based on the fact that you’re using SQL fairly profficiently, I think Supabase might be the best bet for you. It provides everything including auth.
I don’t know exactly how you manage everything on your application, but be aware the SQL request sent can be replaced by something else. Even if the DB credentials are hidden behind our backend, the request is managed by the front end, this is why its not secure. You can probably try yourself to identify where the HTTP request containing the SQL is sent, and try to send something else on POSTMAN or any HTTP client, and you will be able to do everything allowed by the database
Hi Broberto,
I already had a look at Supabase and Xano but if I want to have different databases for every licensee it will be too expensive. I also don’t now how to share resources between all of them or make updates to all database “at once”. So I don’t think they are an option.
Supabase: I checked again and will probably use it. This resolves many issues I had not addressed yet, mainly authentication and security. Xano (I also have an account) isn’t as powerful as PostgreSQL and it also seems to be difficult using it for multiple “tenants”. And it’s much more expensive (3 x as much if I only use one workspace/project). Thank you for your support!
Hello @flo ,
Thank you very much for the tips. I’m doing the same as you showed but I don’t have any text displayed from my formula.
The problem comes when I insert ‘${}’ , is it because of the brackets?
