1

From a great reply:

in PostgreSQL, CREATE FUNCTION is indeed a "SQL statement" but is is merely a "wrapper" to specify a block of code that is executed by something different than the SQL query "engine". Postgres (unlike other DBMS) supports multiple "runtime engines" that can execute the block of code that was passed to the "CREATE FUNCTION" statement - one artifact of that is that the code is actually a string so CREATE FUNCTION only sees a string, nothing else.

What are the consequences of "the code is actually a string so CREATE FUNCTION only sees a string, nothing else"?

Is that considered as dynamic SQL? Does it prevent or introduce SQL injection risk, compared to dynamic SQL?

How is that different from other RDBMS (if any?) where "the code is not a string"?

Thanks.

Tim
  • 1
  • 141
  • 372
  • 590

2 Answers2

1

All 3GL+ code is basically a string. The "parameter" passed to CREATE FUNCTION is code (to be executed outide the core SQL engine), which is a string (that's not SQL).

Other RDMS's only support SQL as the function/procedure body.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Thanks. What does "3GL+ " mean? Does being a string here introduce SQL injection risk? – Tim Jun 15 '18 at 01:28
  • @Tim I meant [*3rd Generation Language*](https://en.wikipedia.org/wiki/Third-generation_programming_language) or later. Even a [2GL](https://en.wikipedia.org/wiki/Second-generation_programming_language) is still a "string" but it's on the edge of not being one in the way modern programmers think about a program being "text". – Bohemian Jun 15 '18 at 01:45
  • CREATE FUNCTION treats the code in its block as a string, and does that prevents or introduces SQL injection risk, compared to dynamic SQL? – Tim Jun 15 '18 at 01:47
  • @Tim neither, unless you are allowing user input to be source for creating functions (both extremely unlikely and an extremely bad idea). The same principles apply to creating SQL on the fly based on user/api input with or without a call to your function: Be careful about what is passed to your SQL query! – Bohemian Jun 15 '18 at 01:55
  • Thanks. (1) In "The "parameter" passed to CREATE FUNCTION", does "parameter" mean the code inside the function body block? If yes, why do you call it "parameter"? – Tim Jun 15 '18 at 02:01
  • (2) "All 3GL+ code is basically a string." But here PotgreSQL and other RDBMS differ in whether treating the code in a function body as string or SQL code. What do you mean by "All 3GL+ code is basically a string" then? – Tim Jun 15 '18 at 02:03
1

PostgreSQL is highly extensible, and you can for example define your own procedural language to write functions in.

PostgreSQL knows nothing about the language except that it has to call a certain language handler to execute the function.

The way that was chosen to implement this is to simplify pass the code as a string.

This is just an implementation detail and does not make PostgreSQL functions any more or less vulnerable to SQL injection than other RDBMS.

There are several levels on which you have to defend yourself against injection:

  • The function arguments: Here you should choose non-string data types whenever possible.

  • The SQL statements within the function: Here you should avoid dynamic SQL whenever possible, and if you have to use dynamic SQL, you should insert variables using the %L pattern of the format function.

Again, this is the same if function bodies are specified as strings or not.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263