PostgreSQL – Efficiency of Using plpgsql Functions for Identifier and Value Validation

plpgsqlpostgresqlsql-injection

Will my queries be safe from SQL injections if I use plpgsql functions only to validate identifiers and values, instead of executing queries.

Here are the example functions:

CREATE OR REPLACE FUNCTION validate_identifier(identi TEXT)
  RETURNS TEXT AS
$func$
BEGIN
  RETURN quote_ident(identi);
END;
$func$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION validate_value(val TEXT)
  RETURNS TEXT AS
$func$
BEGIN
  RETURN quote_literal(val);
END;
$func$ LANGUAGE plpgsql;

then i.e. simply use query:

SELECT * FROM some_table WHERE validate_identifier('user_input_identifier') = validate_value('user_input_value');

Is this query safe from SQLi, when user_input_identifier and user_input_value are replaced by actual user input, which might be an SQLi attempt?

Best Answer

Is this query safe from SQLi, when user_input_identifier and user_input_value are replaced by actual user input, which might be an SQLi attempt?

The whole sql injection vulnerability is about sanitized input, depending on how you're getting the inputs to the sanitizing function it may not even be safe to use them. In this case, I'll assume you're calling the SELECT statement from a client-library. In such a case, it's

  • no safer than if you just called the functions directly.
  • less efficient
  • still vulnerable to sql injection

The important thing to be safe is that you're binding the values to the input of the function with placeholders.

If your code looks like this,

k = sprintf("
  SELECT *
  FROM some_table
  WHERE validate_identifier(%s) = validate_value(%s)
");

You're not safe. If it looks like this,

k = sprintf("
  SELECT *
  FROM some_table
  WHERE quote_ident(%s) = quote_value(%s)
");

You're not safe. If it looks like this,

k = sprintf("
  SELECT *
  FROM some_table
  WHERE \"%s\" = '%s'
");

You're not safe. And, for all the same reasons. If it looks like this,

k = "
  SELECT *
  FROM some_table
  WHERE col = ?
";
k.exec($foo);

You're safe. If you don't know col at compile time, for instance if you want to safely do ? = ?, the library will either prefetch the escaped version by running something like this,

k = "SELECT quote_ident(?);"

Or it'll simply use a client-side version (which usually binds to libpq) thus saving a trip to the server.

So in answer to your question

Is it efficient to use plpgsql functions only to validate identifiers and values?

It's certainly less efficient, and depending on how you're providing those functions their values, potentially not safe either.

Related Question