1

I will write a stored procedure in PostgreSQL which accepts a variable (my knowledge of SQL is close to zero, so I apologize if the question is obvious). Since this variable will be used verbatim in the call, I wanted to ensure that it is properly escaped to avoid injection.

Is there a function I can wrap the variable in, which would properly do the escaping?

I specifically would like to do that in SQL, as opposed to sanitizing the input (that variable) in the code which calls the SQL query (which would have arguably been easier).

I am surprised not to find any prominent documentation about such a functionality, which leads me to believe that this is not a standard practice. The closest I could get to was with the lexer source code of Postgresql but this is beyond my capacities to understand whether this is the right escaping that is mentioned (and which would lead to string being used as u&’stringuescape’’’, which looks quite barbaric)

WoJ
  • 27,165
  • 48
  • 180
  • 345

1 Answers1

2

There are several quoting functions in PostgreSQL, documented at https://www.postgresql.org/docs/current/functions-string.html

quote_ident(string text)    text    Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled. See also Example 40-1.   quote_ident('Foo bar')  "Foo bar"
quote_literal(string text)  text    Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable. See also Example 40-1.    quote_literal(E'O\'Reilly') 'O''Reilly'
quote_literal(value anyelement) text    Coerce the given value to text and then quote it as a literal. Embedded single-quotes and backslashes are properly doubled. quote_literal(42.5) '42.5'
quote_nullable(string text) text    Return the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is null, return NULL. Embedded single-quotes and backslashes are properly doubled. See also Example 40-1.    quote_nullable(NULL)    NULL
quote_nullable(value anyelement)    text    Coerce the given value to text and then quote it as a literal; or, if the argument is null, return NULL. Embedded single-quotes and backslashes are properly doubled.   quote_nullable(42.5)    '42.5'

But if you're designing procedures that prepare SQL from a string, you should use query parameters instead.

PREPARE fooplan (int, text, bool, numeric) AS
    INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

Read more in https://www.postgresql.org/docs/current/sql-prepare.html

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    @LaurenzAlbe, True, that is good for quoting identifiers, but for literals, developers should still get into the habit of using parameterized queries. – Bill Karwin Dec 13 '18 at 22:35