Обсуждение: Prepared statements with bind parameters for DDL

On 02/11/2015 09:42 AM, deepak wrote:
> Hi,
>
> I find that one can't have a prepared statement with bind parameters for
> a DDL statement,
> although I couldn't find the rationale for this restriction.  Is this
> limitation due to the database
> design, or is it something that's imposed by the SQL standard and/or the
> JDBC drivers?
>
> Please clarify.

That is going to require a lot more information:

What Postgres version?

Where exactly are you preparing the statement?

Since you mention JDBC, are we to assume that is what you are using?

Can we see an example of what you are trying to do?

Have you looked at?:

http://www.postgresql.org/docs/9.3/static/functions-string.html#FUNCTIONS-STRING-FORMAT

>
>
> --
> Deepak


--
Adrian Klaver
adrian.klaver@aklaver.com

deepak <deepak.pn@gmail.com> writes:
> I find that one can't have a prepared statement with bind parameters for a
> DDL statement,

Nope.  DDL commands generally don't have any support for evaluating
expressions, which would be the context in which parameters would
be useful.  Nor have they got plans, which would be the requirement
for prepared statements to be good for much either.

            regards, tom lane

On Wed, Feb 11, 2015 at 02:22:10PM -0500, Tom Lane wrote:
> deepak <deepak.pn@gmail.com> writes:
> > I find that one can't have a prepared statement with bind parameters for a
> > DDL statement,
>
> Nope.  DDL commands generally don't have any support for evaluating
> expressions, which would be the context in which parameters would
> be useful.  Nor have they got plans, which would be the requirement
> for prepared statements to be good for much either.

Not really true, there are plenty of cases where you just want to fill
in literals without having to worry about quoting. For example:

DROP TABLE %s

is opening yourself up to SQL injection. I've wondered if it were
possible to be able to say:

DROP TABLE IDENTIFIER($1);

where in the grammer IDENTIFIER($x) would be parsed as an identifier
token and the parameter would be required to be a string.  You don't
need to evaluate any expressions to make this work, but it saves you
from any quoting issues.

Of course, it gets more complicated if you want to allow cases like:

PREPARE get_by_id AS SELECT * FROM IDENTIFIER($1) WHERE id=$2;

EXECUTE get_by_id('mytable', 400);

But DDL would be a great start.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Wed, Feb 11, 2015 at 02:22:10PM -0500, Tom Lane wrote:
>> Nope.  DDL commands generally don't have any support for evaluating
>> expressions, which would be the context in which parameters would
>> be useful.  Nor have they got plans, which would be the requirement
>> for prepared statements to be good for much either.

> Not really true, there are plenty of cases where you just want to fill
> in literals without having to worry about quoting. For example:

> DROP TABLE %s

True, but that is not what Postgres thinks is a parameter; for example
you cannot do "SELECT * FROM %s", nor could you persuade it to interpret a
parameter as a column reference in a SELECT.

> ... is opening yourself up to SQL injection. I've wondered if it were
> possible to be able to say:

> DROP TABLE IDENTIFIER($1);

A meta-function like that would just provide a different route for SQL
injection, I suspect, particularly when attacking applications that
hadn't gotten the memo about "IDENTIFIER()" being magic.

I think there's considerable value in a client-library function for safe
interpolation of this sort, but I doubt that trying to shoehorn it into
the server is the answer.

            regards, tom lane