How do I write a SQL UPDATE with dynamic columns

💬 Queries and Resources
,
1

Hi all!
I was wondering how I can change the value of a dynamic value (the column is chosen through user input).
As an example, I built a form with three input values.

  1. textinput1.value (Integer)
  2. select1.value (String)
  3. select2.value (String)

select2.value is used as an identifier (select a row). textinput1.value is the integer value I want to change in the column named as select1.value.

I've written the query as followed:

UPDATE table
SET {{ select1.value }} = {{ parseInt(textinput1.value) }}
WHERE agendaNaam = {{ select2.value }}

But then I get the following error:


Screenshot 2019-05-24 at 10.29.33.png
Screenshot 2019-05-24 at 10.29.33.png2794×96 15.8 KB

You can clearly see the problem are the quotation marks in the select1.value.
So I've tried to overcome it with javascript but the code is not working:

select2.value.replace( /'/g, '' )

Is there any other way to fix this?
Thanks in advance,
Pello

2 Likes
2

Hello! Thank you for the question! The reason dynamic column names aren’t supported is because we convert your SQL statement into a prepared statement to prevent SQL injection: https://docs.retool.com/docs/sql-queries#section-security

If you want, you can turn off prepared statements in the resources section. But that would expose you to potential SQL injection attacks from the end-users of your apps. LMK if that makes sense? Thanks!

3 Likes