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.
validate_identifier()
as an identifier: it's just a string and it won't correlate it to the table in any way. IOW in most cases it will doSELECT * FROM some_table WHERE false