Retool DB SQL vs GUI string escape

💬 App Building
1

I have a string with that contains characters that need escaping:

params = {
  content: "Hello, I'm an AI designed to help you, your agent and your carrier" 
}

to insert a row using the GUI mode I'd use

{{ params.content }}

and the retool backend would escape the string properly. I'm now going back and combining a bunch of workflow blocks into a single SQL statement or moving them to the query library, both of which require using SQL mode.

INSERT INTO
  ainsure_messages (
    "created_on",
    "updated_on",
    "created_by",
    "content",
    "thread_id",
    "source",
    "role",
    "platform",
    "agency",
    "from",
    "to"
  )
VALUES
  (
    TO_TIMESTAMP ({{ createThread.data.created_at }}),
    TO_TIMESTAMP ({{ createThread.data.created_at }}),
    '{{ params.user_id }}',
    '{{ params..content }}',
    '{{ insertNewThread.data[0].thread_id }}',
    '{{ params.source }}',
    '{{ params.role }}',
    'retool_desktop',
    '{{ params.agency }}',
    'ai',
    '{{ params.role }}'
  );

when using the value up at the top (the hello message) this throws an error:
image
It took me a while to figure out the m it's referring to is actually in the evaluated expression... it took me even longer to figure the proper way to escape this. I tried using the psql functions quote_literal and quote_nullable since postgresql seems to use ' as the escape character instead of \ like JS but this didn't work either. I ended up having to use JS .replace():

INSERT INTO
  ainsure_messages (
    "created_on",
    "updated_on",
    "created_by",
    "content",
    "thread_id",
    "source",
    "role",
    "platform",
    "agency",
    "from",
    "to"
  )
VALUES
  (
    TO_TIMESTAMP ({{ createThread.data.created_at }}),
    TO_TIMESTAMP ({{ createThread.data.created_at }}),
    '{{ params.user_id }}',
    '{{ params.messages[0].content.replace("'", "''") }}',
    '{{ insertNewThread.data[0].thread_id }}',
    '{{ params.source }}',
    '{{ params.role }}',
    'retool_desktop',
    '{{ params.agency }}',
    'ai',
    '{{ params.role }}'
  );

I'm guessing this is intended, but since the GUI mode does this behind the scenes you need indepth knowledge to figure this out. Would it be possible to make it so retool automatically escapes text columns even in SQL mode to avoid having to remember to do this everywhere?

1 Like
3

Have you disabled prepared statements for your Postgres resource?

I believe by default Retool converts SQL queries to prepared statements and will properly pass any inserted string values by binding them as parameters.

If you haven't disabled prepared statements, then surrounding your embedded expressions with single-quotes isn't necessary. It will cause them to be inserted as string literals instead of being bound as a parameter, i.e.

VALUES ('Hello, I'm an AI')

instead of

VALUES ($1)

I believe the setting only affects SQL mode and that GUI mode prepares the statement regardless, to ensure that you're safely writing the data. So this could explain the difference in behavior that you're seeing.

I was able to recreate a version of your query with the sample content string and without the surrounding quotes and insert successfully.

insert into
  messages (created_on, updated_on, created_by, content)
values
  (
    TO_TIMESTAMP ('22 Feb 2025', 'DD Mon YYYY'),
    TO_TIMESTAMP ('22 Feb 2025', 'DD Mon YYYY'),
    {{ params.user_id }},
    {{ params.content }}
  )

I played around with the best way to escape the string if you, in fact, do need to have prepared statements disabled. I also wasn't able to get quote_literal, quote_nullable, or something like format('%L', ...) to work. I think the best approach is probably formatting with JS, as you are doing.

If you didn't want to have to .replace() each value, you could add a helper function to the Preloaded JS section of App Settings like:

// Escapes single-quote in input string by
// adding an additional single-quote before it
function escapeString(input) {
    return input.replace(/'/g, "''");
}```

and then call it in your embedded expression like

`{{ escapeString(params.content) }}`
2 Likes
4

ahhhhhhhhhhh, it all makes much more sense :bowing_man: !!!! I'm gonna have to look into the ups and downs of the prepared statements, so for now I rather like the simplicity of the preloaded js function so I think I'll go that route until I can make a more informed decision since it's a global setting