0

I want to prevent SQL injection attacks in a rather abstract application. Therefore I want to escape all user provided input as described here. The other options provided on this page don't fit in my scenario.

I couldn't find the right place in the postgres docs regarding this. Any ideas on how to escape inputs in a PostgreSQL query like explained on the OWASP cheat sheet?

3
  • 2
    "The other options provided on this page doesn't fit in my scenario." - the best defense are parameterized queries (option 1 in the linked cheat sheet) since these provide a clear separation between code and data. If this defense does not fit your scenario it might be better to rethink your design. Escaping can easily go wrong with complex input. It can be slightly different between databases, rules might slightly change when upgrading the database version ... Commented May 30, 2023 at 14:23
  • Apart from that, here you have the definition of a string constant - escaping must ensure that anything with a special meaning (which is documented there) gets properly escaped. Note though that escaping also depends on context, i.e. quote-delimited strings have a different context than dollar-quoted strings. Numbers are another context with different rules etc. Like I said, hard to do right, easy to mess up. Commented May 30, 2023 at 14:27
  • thanks! I think I found something in the docs for me. since I need to provide the ability for both dynamic values and dynamic identifiers, it seems I have to do a combination of parameterzied query and escaping like it's shown in the second or third example here
    – Jan
    Commented May 30, 2023 at 15:04

1 Answer 1

0

The PostgreSQL C library has the following functions for this purpose:

  • PQescapeStringConn
  • PQescapeLiteral
  • PQescapeByteaConn
  • PQescapeIdentifier

Depending on which programming language you use, you'll have to install the specific library/extension/gem/... which makes those C functions available.

Do not try to implement escaping yourself, because this has to be done at the database level to take the specific connection settings (especially the current character encoding) into account.

An as Steffen Ullrich already said, the only reliable solution are prepared statements / parameterized queries.

Since you asked about identifiers in your comments, consider using a whitelist, so that only specific hard-coded strings can appear as identifiers in the query. You can additional use the above functions to escape the identifier.

5
  • thanks! I'm using Javascript so I cannot use the C lib. I think I have to do it like shown in the examples here since I need dynamic values as well as dynamic identifiers. It seems there is no lib I can use, so I might need to build the statements with the placeholder etc myself and let the database to the injection then.
    – Jan
    Commented May 30, 2023 at 15:09
  • Are you talking about server-side JavaScript? node.js does have a PostgreSQL C library.
    – Ja1024
    Commented May 30, 2023 at 15:11
  • yes on node.js. oh you mean this one? so far I was using this one.
    – Jan
    Commented May 30, 2023 at 15:19
  • Both should work. According to the GitHub README, the latter also has bindings for the C functions above. Also note my comment about whitelisting the identifiers: If you can predefine the possible identifiers that might appear in a query, you can prevent injections by only allowing specific hard-coded JavaScript strings.
    – Ja1024
    Commented May 30, 2023 at 15:25
  • I cannot predefine the possible identifiers that might appear. but thanks so much for the suggestion! I just found out that the latter lib can be enhanced with the other one to get the native binding. unfortunately it doesn't work in combination with streams.. It seems I need to find another way to handle the identifiers then using the native c bindings of that lib.
    – Jan
    Commented May 30, 2023 at 21:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .