Sidestepping Retool's SQL Limitations

Community Show & Tell
1

The lack for transaction support was my first surprise when I started doing more complex Retool apps. Recently I stumbled upon another interesting fact: bulk inserts do to return the ids of the inserted rows.

As a best practice Retool uses prepared statements by default in order to avoid SQL injections attacks. When this setting is enabled, however, Retool does not support custom SQL code for transactions or inserting rows.

One solution is to disable the prepared statements. You now have a new problem however: SQL injections. In order to solve it, you have to use a library to escape your inputs. While not the most recommended approach, as long as you manage to escape all of your inputs, you should be fine (SQL Injection Prevention - OWASP Cheat Sheet Series).

Here's an example application using this approach that bulk inserts rows into the onboarding_db users table and returns their ids: SqlEscaping.json (20.4 KB)

How it works:

  • The app uses this library for escaping the inputs: GitHub - mysqljs/sqlstring: Simple SQL escape and format for MySQL. It's made for Node.js, so I had to slightly modify the code.

    It's really simple:

    • Copy the contents of this file https://github.com/mysqljs/sqlstring/blob/master/lib/SqlString.js to your apps global JavaScript.

    • Replace the first row from var SqlString = exports; to var SqlString = {};. You can also take the code from the example app.

    • The library also supports escaping a Buffer, but this is not supported in the browser. So finally remove the following check else if (Buffer.isBuffer(val)) from the escape function.

  • Go to the onboarding_db resource in the Resources tab and check Disable converting queries to prepared statements.

  • Create a new resource query for the onboarding_db and select SQL mode.

  • Paste it the following for the query body:

    {{(() => {
  const values = SqlString.escape([
    ['John', new Date(), new Date()],  
    ['Richard', new Date(), new Date()],
  ])
  // Note the addition of the `returning id` clause in order to get the ids
  return `insert into users (first_name, created_at, updated_at) values ${values} returning id`
})()}}

  • Run the query and see the resulting ids:

    Screenshot 2022-09-05 at 16.33.41

You can read the README of the library here: GitHub - mysqljs/sqlstring: Simple SQL escape and format for MySQL for more examples of how to use it.