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

**URL:** <https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494>\
**Category:** Ask us anything\
**Created:** [September 21, 2023, 2:55pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494 "2023-09-21T14:55:09Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![BertrandG](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/bertrandg/32/5129_2.png) [@BertrandG](https://community.weweb.io/u/BertrandG)\
**Post date:** [September 21, 2023, 2:55pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/1 "2023-09-21T14:55:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![flo](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/flo/32/13260_2.png) [@flo](https://community.weweb.io/u/flo)\
**Post date:** [September 28, 2023, 6:00pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/2 "2023-09-28T18:00:33Z")

</div>

Hi @BertrandG,

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

 ![CleanShot 2023-09-28 at 19.50.20@2x](https://us1.discourse-cdn.com/flex016/uploads/weweb/original/2X/e/eef87e5fb047e6c0780f73f90bed0222b542a264.png)

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:

 ![CleanShot 2023-09-28 at 19.52.39@2x](https://us1.discourse-cdn.com/flex016/uploads/weweb/original/2X/6/6ced0864af6b989ada0529f2c83daae8646f48e1.png)

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

```auto
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?

---

<div class="post-metadata">

**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\
**Post date:** [September 28, 2023, 6:15pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/3 "2023-09-28T18:15:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dorilama](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/dorilama/32/10403_2.png) [@dorilama](https://community.weweb.io/u/dorilama)\
**Post date:** [September 28, 2023, 9:35pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/4 "2023-09-28T21:35:26Z")

</div>

Isn’t this vurnerable to SQL injection?

---

<div class="post-metadata">

**Author:** ![Alexis](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/alexis/32/13248_2.png) [@Alexis](https://community.weweb.io/u/Alexis)\
**Post date:** [September 29, 2023, 10:26am UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/5 "2023-09-29T10:26:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![BertrandG](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/bertrandg/32/5129_2.png) [@BertrandG](https://community.weweb.io/u/BertrandG)\
**Post date:** [October 3, 2023, 11:37am UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/6 "2023-10-03T11:37:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\
**Post date:** [October 3, 2023, 11:58am UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/7 "2023-10-03T11:58:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Alexis](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/alexis/32/13248_2.png) [@Alexis](https://community.weweb.io/u/Alexis)\
**Post date:** [October 3, 2023, 12:05pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/8 "2023-10-03T12:05:47Z")

</div>

> [@BertrandG](#):
>
> But the question is whether this is a security risk.

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 😕

---

<div class="post-metadata">

**Author:** ![BertrandG](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/bertrandg/32/5129_2.png) [@BertrandG](https://community.weweb.io/u/BertrandG)\
**Post date:** [October 3, 2023, 12:23pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/9 "2023-10-03T12:23:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![BertrandG](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/bertrandg/32/5129_2.png) [@BertrandG](https://community.weweb.io/u/BertrandG)\
**Post date:** [October 3, 2023, 12:25pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/10 "2023-10-03T12:25:17Z")

</div>

> [@Alexis](#):
>
> n

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

---

<div class="post-metadata">

**Author:** ![dorilama](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/dorilama/32/10403_2.png) [@dorilama](https://community.weweb.io/u/dorilama)\
**Post date:** [October 3, 2023, 12:39pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/11 "2023-10-03T12:39:34Z")

</div>

> **[Exploits of a Mom](https://xkcd.com/327/)**
>
> Her daughter is named Help I'm trapped in a driver's license factory.

---

<div class="post-metadata">

**Author:** ![BertrandG](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/bertrandg/32/5129_2.png) [@BertrandG](https://community.weweb.io/u/BertrandG)\
**Post date:** [October 3, 2023, 2:04pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/12 "2023-10-03T14:04:34Z")

</div>

**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!

---

<div class="post-metadata">

**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\
**Post date:** [October 3, 2023, 3:13pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/13 "2023-10-03T15:13:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\
**Post date:** [October 3, 2023, 3:13pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/14 "2023-10-03T15:13:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Fix](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/fix/32/8212_2.png) [@Fix](https://community.weweb.io/u/Fix)\
**Post date:** [December 5, 2023, 4:36pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/15 "2023-12-05T16:36:06Z")

</div>

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](https://us1.discourse-cdn.com/flex016/uploads/weweb/original/2X/3/3573bb072d375e745eb1affb596b6bb4367ac984.png)

---

<div class="post-metadata">

**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\
**Post date:** [December 5, 2023, 6:34pm UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/16 "2023-12-05T18:34:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Fix](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/fix/32/8212_2.png) [@Fix](https://community.weweb.io/u/Fix)\
**Post date:** [December 6, 2023, 10:29am UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/17 "2023-12-06T10:29:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Alexis](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/alexis/32/13248_2.png) [@Alexis](https://community.weweb.io/u/Alexis)\
**Post date:** [December 6, 2023, 10:53am UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/18 "2023-12-06T10:53:30Z")

</div>

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)

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

```

---

<div class="post-metadata">

**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\
**Post date:** [December 6, 2023, 11:02am UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/19 "2023-12-06T11:02:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Fix](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/fix/32/8212_2.png) [@Fix](https://community.weweb.io/u/Fix)\
**Post date:** [December 6, 2023, 11:22am UTC](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494/20 "2023-12-06T11:22:26Z")

</div>

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

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

[Next page](https://community.weweb.io/t/sql-plug-in-update-postgresql-table-within-or-without-transaction/4494.md?page=2)