Search code examples
postgresqlsecuritysql-injectionquoting

Can I protect against SQL injection attacks by wrapping SQL in PostgreSQL functions?

Can I use a function like this

CREATE FUNCTION create_user(_firstName text)
RETURNS void AS $$
  INSERT INTO user_account (first_name) VALUES (_firstName);
$$ LANGUAGE sql;

On the server to protect against SQL injection attacks? Then I can just run this on the client,

client.query(`SELECT create_user(${someUserInput})...`

Or will I still need to use parameterized queries with placeholders,

client.query(`SELECT create_user($1)`, [someUserInput])
Solution

  • Problem (exploit)

    client.query(`select create_user(${someUserInput})`

    The problem there is what happens if 

    let someUserInput = `'foo'); DROP DATABASE bar;`;

    That will get sent to your call as,

    client.query("select create_user('foo'); DROP DATABASE bar;")`

    And, that would be bad. Yes, the argument to create_user is protected against injection, but the call to that isn't.

    Solutions

    1. Use placeholders (obvious choice: most failsafe and secure solution.)

    2. Ensure someUserInput is properly quoted

      1. Use the client-library to quote it with something like PQescapeLiteral
      2. Use a second run to the server to quote it with quote_literal (requires placeholders anyway). SELECT quote_literal($1);

    I would not try to create the quoting-mechanism myself.