On Wed, Aug 9, 2017 at 3:21 PM, Jym Morton <j...@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.

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.

David J.

Reply via email to