Well, I will use COPY with some confidence, then. And really look into the proper escaping. For now, though, I will use
preparedstatements.
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@sss.pgh.pa.us> wrote:
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [GENERAL] vulnerability of COPY command
> To: "Pavel Stehule" <pavel.stehule@gmail.com>
> Cc: "Dennis Gearon" <gearond@sbcglobal.net>, pgsql-general@postgresql.org
> Date: Sunday, May 30, 2010, 7:14 AM
> Pavel Stehule <pavel.stehule@gmail.com>
> writes:
> > 2010/5/30 Dennis Gearon <gearond@sbcglobal.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
>