On Wed, Aug 9, 2017 at 3:21 PM, Jym Morton <jym@outlook.com> wrote:
When I write software, and use a database I don’t need to escape literals if I have a Prepared Statement. This is a major reason some of us use Prepared Statements. So, when I looked at this page, I was unclear about was whether it or not I had to do it.
(pseudo-code)
PREPARE 'SELECT $1';
EXECTUE ('; TRUNCATE pg_catalog');
PostgreSQL is going to ensure the "; TRUCATE pg_catalog" is considered a single literal value when it execute SELECT $1 and so the final result is that you get a 1x1 result with the text "; TRUNCATE pg_catalog" instead of a broken database. That is the major reason I use prepared statements, to prevent SQL-injection.
You still have write the literal value within EXECUTE in a manner that PostgreSQL can parse the command. That is why you must escape a single quote in the value otherwise that single quote will mark the end of the literal and the statement will provoke a syntax error.
EXECUTE ('; bob's niece is 4 years old'); -- '; bob' is the literal and the stray "s" will a problem. Chapter 4 covers this dynamic.
There is no material difference compared to the rules to write:
SELECT 'bob''s niece is 4 years old';
and none of the examples on the SELECT page use a literal value with an embedded single quote...
Comment “More precisely, the vast majority of the docs assume you've read "Chapter 4. SQL Syntax".”
Response: I don’t have any issue with SQL Syntax.
I'm sorry but your
suggestion is to have this page of the documentation help clarify a point of SQL syntax with which you were unfamiliar. In may be PostgreSQL's particular flavor of SQL that is in question here but it is still properly called SQL syntax.
Unless the vast majority of the docs are sentient beings they can not assume.
Ok, so "the writers of" the documentation assume some level of knowledge for the reader so that they can avoid being repetitive. You may disagree with what is assumed but I stand by my opinion that whomever wrote this example did not need to consider that their example would be improved if they used a value that required escaping.
Ultimately, if someone wanted to act on your suggestion it wouldn't bother me. But I see no systematic problem with this example.