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

Question

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})...`

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

@EvanCarroll -- Why move it to DBA? This isn't really a question about DB administration, architecture, or query optimization. Seems like a better fit on SO, no? — Mark McKelvy
– Mark McKelvy, Commented Feb 27, 2018 at 18:01
Fair point. That said, I think I'll keep the question here for now. — Mark McKelvy
– Mark McKelvy, Commented Feb 27, 2018 at 19:00

Evan Carroll · Accepted Answer · 2018-02-28 17:36:06Z

3

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.

Use placeholders (obvious choice: most failsafe and secure solution.)
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.

answered Feb 27, 2018 at 17:31

Evan Carroll

1

Sign up to request clarification or add additional context in comments.