Performance issue with the `escapeLiteral` utility

[mrmlnc]

Hello,

In my application, I use a field with the jsonb type. The size of this field can be more than 1000 characters. There can be many text-based columns in the table.

I've noticed that insert queries with long strings take a long time. My investigation led me to the escapeLiteral function. This function iterates over the entire line and builds it again.

Here are some measurements (node@20) showing the growth of operation time depending on the length of the string:

Input string length	Function execution time
11	0.053ms
110	0.065ms
1100	0.11ms
11_000	0.554ms
110_000	4.946ms

var input = "hello world".repeat(10000)

console.time('\nperf_escape_literal')
utils.escapeLiteral(input)
console.timeEnd('\nperf_escape_literal')

console.dir(input.length)

An example of a simplified string that is written to the jsonb column. We also have users with a large amount of data (the string will be longer x2…10).

[
 {"type": "local", "action": "Open projects page", "expectation": ""},
 {"type": "local", "action": "Click on button \"Create new project\"", "expectation": "You are being redirected to the new project form"},
 {"type": "local", "action": "Fill the form", "expectation": "Project code should be generated automatically"},
 {"type": "local", "action": "Click on \"Create project\" button", "expectation": "You are being redirected to repository page with no cases or suites"},
 {"hash": "1_4", "type": "shared", "action": "Do action 0", "version": 23, "expectation": "Action 0 is indicated as 0.13738214216469524 on the ui", "sharedStepsGroupId": 1},
 {"hash": "1_4", "type": "shared", "action": "Swipe 0.4240689256483676", "version": 23, "expectation": "The sun is shining with the color temperature of 0.8792701581028319", "sharedStepsGroupId": 1},
 {"hash": "1_4", "type": "shared", "action": "Rotate at 0.3493667640291416 degrees", "version": 23, "expectation": "Wind speed is changed to 0 m/s", "sharedStepsGroupId": 1}
]

perf_escape_literal: 0.104ms
input_length: 1014

At the moment, these are small numbers, but escaping is called for each text-based column. In total, these are significant numbers. For example, when adapting the solution below, we got an speed-up from ~950ms to ~550ms for some insert queries.

I found a solution for this problem: Dollar-Quoted string. It eliminates the need to construct a string each time, saving time.

const escapeLiteral = function (str) {
  if (str.length > 100) return `$__pg__$${str}$__pg__$`
  
  return escapeLiteralOld(str)
}

I believe it would be beneficial to use the previous solution for strings that are less than 50 or 100 characters, as it would reduce the amount of data sent over the network for the sake of the smaller strings.

Here are the measurements for this solution:

Input string length	Function execution time
11	0.051ms
110	0.056ms
1100	0.05ms
11_000	0.051ms
110_000	0.047ms

Of course, I am able to replace the current implementation of the function within the ORM being used in the project. However, I believe it would be beneficial to explore options for "native" optimization for all users.

[brianc]

Hey @mrmlnc - thanks for your thoughts & deep explanation & research into this! First of all, if at all possible, I highly recommend you use parameterized sql statements over using escapeLiteral, then you can have PostgreSQL backend do the escaping for you! You can use jsonb params for every param in your query if you want. I've written utility functions that will build a dynamic insert statement with parameters based on an arbitrary number of jsonb values to be inserted. Postgres supports unsigned int16 (~32k) parameters per query so you have quite a bit of headroom. Surprisingly I've sent megabytes of parameters in a single upsert statement to a postgres backend and it handles it without even a hiccup.

If escaping isn't possible for some reason then I your best bet is to bite the bullet & use the actual implementation of escapeLiteral in this project & eat the performance overhead. My gut is telling me there's probably an architectural decision you could change to work around things. Either change the queries so they are parametrizable or limit input size or maybe just yolo and don't worry about query speed if your system isn't under heavy load?

While I'm not a leading expert on sql injection I'm curious how your faster version using would avoid someone literally substituting the $__pg__$ into their input and therefore escaping out and then having a sql injection vector? If you are comfortable with that in your system (perhaps sanitizing input before escaping it w/ escapeLiteral in some way) then I think your best bet might to be to take that function you put in your comment & include it into your own codebase as a utility function for the one off situations where you have very very large unsanitized or partially trusted text input that needs to be escaped...I'm not willing to change the implementation of escapeLiteral as its as best we could do a 1 to 1 port of the C code in libpq that does the same thing. The security impact could be disastrous for a small potential performance gain in a rather edge-case scenario.

[Llorx]

@brianc I improved the performance without using $__pg__$$. Just a different code that runs faster but has the same logic (replace and such): #3553

Inserting 5k strings of 100k characters is faster by scaping them with this function than sending the arguments to the server. 9 seconds vs 35 seconds in my laptop, so I prefer the performant local escape solution, as it only spends a fraction of a second to do the escaping of the 5k strings and the 8.xxx seconds remaining is just the server processing and replying. Less CPU stress for both.