Performance issue with the `escapeLiteral` utility about node-postgres HOT 1 CLOSED

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.

from node-postgres.