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.
>
(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.
David J.
