Re: vulnerability of COPY command

Well, I will use COPY with some confidence, then. And really look into the proper escaping. For now, though, I will use prepared statements.

One thing, can prepared statements be done, including the 'execute', inside of a transaction, and what are the side effects?

BTW, speaking of SQL injection, anyone seen this site?
http://sqlmap.sourceforge.net/demo.html

Dennis Gearon

Signature Warning
----------------
EARTH has a Right To Life,
otherwise we all die.

Read 'Hot, Flat, and Crowded'
Laugh at http://www.yert.com/film.php

--- On Sun, 5/30/10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Subject: Re: [GENERAL] vulnerability of COPY command
> To: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
> Cc: "Dennis Gearon" <gearond(at)sbcglobal(dot)net>, pgsql-general(at)postgresql(dot)org
> Date: Sunday, May 30, 2010, 7:14 AM
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> writes:
> > 2010/5/30 Dennis Gearon <gearond(at)sbcglobal(dot)net>:
> >> If I build a text based, COPY file for bulk
> purposes, to be input via the command line, is Postgres
> vulnerable to SQL injection from that?
>
> > SQL database cannot be injected via NON SQL statemenst
> like COPY.
>
> Well, that depends.  If you construct a script file
> like
>
>     COPY mytable FROM STDIN;
>     ... data rows here ...
>     \.
>
> then obviously somebody could inject SQL if they could get
> a line
> beginning with \. into the data rows.  However, if you
> put the data
> rows in a *separate file* this is not possible.
>
> ISTM though that this discussion is largely missing the
> point.
> If you want to build COPY input from raw data, you have to
> be
> prepared to do suitable quoting/escaping --- the rules are
> a bit
> different from plain SQL quoting, but the concept is the
> same.
> And if you do do that, you're immune from SQL injection in
> any case,
> as is also true of plain old INSERTs.  SQL injection
> is only a problem
> for applications that fail to do quoting/escaping at all,
> or do it
> incorrectly, and COPY is really not any safer if you blow
> that than
> regular SQL is.
>
>            
> regards, tom lane
>