Thread

1. Prepared statements with bind parameters for DDL

   deepak <deepak.pn@gmail.com> — 2015-02-11T17:42:22Z

   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.
   
   
   --
   Deepak
   

2. Re: Prepared statements with bind parameters for DDL

   Adrian Klaver <adrian.klaver@aklaver.com> — 2015-02-11T17:53:55Z

   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
   
   
   

3. Re: Prepared statements with bind parameters for DDL

   Tom Lane <tgl@sss.pgh.pa.us> — 2015-02-11T19:22:10Z

   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
   
   
   

4. Re: Prepared statements with bind parameters for DDL

   Martijn van Oosterhout <kleptog@svana.org> — 2015-02-11T23:34:01Z

   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
   

5. Re: Prepared statements with bind parameters for DDL

   Tom Lane <tgl@sss.pgh.pa.us> — 2015-02-11T23:47:21Z

   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