SQL plug-in: Update PostgreSQL table within (or without) transaction

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.

Hi @BertrandG,

The best way to work with SQL queries is to create a custom formulas with parameters. Here is an example:

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:

Here is the code I used in the formula. Remember to select Javascript and to click Create

return `
SELECT ${column} FROM myTable WHERE ${column} = '${where}';

Could you show us the Current value of your binding, so we could help you with the syntax and understand what does not work like it should?


Dang, that’s a fancy approach, I’m writing that down :smiley:


Isn’t this vurnerable to SQL injection?


Yes, this is why SQL plugin should be used with caution, only for internal app where every user can be trusted.

We developed this plugin for specific enterprise needs, building self hosted internal apps. Its not secure to expose such app on internet.


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.

1 Like

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 :confused:

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.

OK. I might then use PostgREST connections which I used before knowing WeWeb.

1 Like

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!

You can make a “dimension” for each company and they will have each their own gated piece of a database protected by multiple layers of security.

If you know what you’re doing Xano is a waste of time and money.

1 Like

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?

Thank you in advance for your answer
CleanShot 2023-12-05 at 17.35.40

Are you using the right symbols fro opening and closing the string?

@Broberto i am using '' (on mac it is 4 touch) is it the good one ?

Hi, you have to use backstick to wrap the whole string, it’s another character. (`)

The position depends of your keyboard layout (azerty, qwerty, qwertz)

`SELECT * FROM table WHERE '${variable}' IS NULL;`
1 Like

Yeah you need backticks, otherwise it won’t work with variables within a string.

1 Like

Ok thank you @Broberto, @Alexis. I am such a newbie :joy:

And thank you @Alexis to take the time to answer me while you put in production the new interface :wink: