I know that the documentation states:
Prepared statements only last for the duration of the current database session.
Is there any way way to either
- save a prepared statement
- create code which will regenerate a prepared statement?
-
Do functions store execution plans? Are they also protected against SQL injection?– ManngoJul 13, 2016 at 12:30
-
I know that dynamic SQL can let SQLi through, but in writing a function as an alternative to a prepared statement, returning a table I would guess that it should protect against that much. Can you put your comment in the form of an answer?– ManngoJul 13, 2016 at 13:29
Add a comment
|
1 Answer
If I understand you correctly, you want to use functions.
Those are stored (with their code), their execution plans (may be) cached and so on. Plan caching is not so simple as it first sounds. Read https://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING and the linked page about PREPARE.
For the additional questions from your comments, PostgreSQL functions don't necessarily protect you from SQL injection. Building dynamic SQL statements the wrong way (using concatenation without proper quoting) can still open the possibility for such attacks.