-1

Is this function susceptible to SQL injection? Specifically, the use of where signature = _sig in the select statement in the if not exists arm. Can someone pass a malicious value as a parameter to the function?

CREATE OR REPLACE FUNCTION INSERT_RECORD(_sig text)
RETURNS BOOLEAN AS $$
DECLARE
BEGIN
    
    IF NOT EXISTS (SELECT 1 FROM records where signature = _sig) THEN
        return TRUE;
    end if;
    

    RETURN false;
END;
$$ language 'plpgsql';

If yes, how can I avoid SQL injection in this?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
sudoExclaimationExclaimation
  • 7,992
  • 10
  • 47
  • 105
  • @tadman Postgres 9.4 was the only tag available in Stack Overflow. I have edited it to create a new tag for Postgres 15 which I am using. If you post the second comment as answer, I will accept it. Thanks. – sudoExclaimationExclaimation Jul 23 '23 at 19:52
  • 1
    Congrats on being *first* with Postgres 15! – tadman Jul 23 '23 at 19:55
  • 1
    @tadman Thanks. Note that due to how Stack Overflow shows tags, the oldest version tags still show up as the recommended ones. – sudoExclaimationExclaimation Jul 23 '23 at 20:09
  • No SQL injection possible while values are used as values. But the function is needlessly convoluted. Just use `SELECT NOT EXISTS (SELECT FROM records where signature = _sig)` instead of the function. (If you need this check at all. You wouldn't for an UPSERT.) – Erwin Brandstetter Jul 23 '23 at 23:41
  • @ErwinBrandstetter this was just a simple example of my function. My actual function is more complex and uses different things inside the if then else statement. – sudoExclaimationExclaimation Jul 24 '23 at 00:21

1 Answers1

0

You're not composing a SQL statement from a string here, so not sure how injection would happen. This is a parameter value being used in a query, as one normally does.

The injection, if it happens, would need to occur when composing the query that calls this function.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • Would that also be true if I have a `insert into records (signature) values (_signature);` in the function as long as it's not in a string? – sudoExclaimationExclaimation Jul 23 '23 at 19:58
  • 1
    "Injection" happens when you're composing a query as a string, then running it as SQL. If you're using SQL the whole time, no strings, you can't really inject, as the query will not use variable interpolation or concatenation, two things that create injection bugs. So regardless of your query, so long as the syntax is valid, and the argument types match, you should be fine. It's always worth auditing any code, *especially* within stored procedures as those are hard to update in a production environment without breaking things. – tadman Jul 23 '23 at 20:01