Обсуждение: vulnerability of COPY command
I'm trying to build a way to bulk load from a script to a Dbase, postgres. Using single, parameterized statements is a pretty good defense against SQL injection, so I use Symfony as the main userinput. But for this bulk loading, it's tooooooo slow. If I build a text based, COPY file for bulk purposes, to be input via the command line, is Postgres vulnerable to SQL injectionfrom that? 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
Hello 2010/5/30 Dennis Gearon <gearond@sbcglobal.net>: > I'm trying to build a way to bulk load from a script to a Dbase, postgres. > > Using single, parameterized statements is a pretty good defense against SQL injection, so I use Symfony as the main userinput. > > But for this bulk loading, it's tooooooo slow. Maybe you have enabled autocomit - then it can be very very slow. > > If I build a text based, COPY file for bulk purposes, to be input via the command line, is Postgres vulnerable to SQL injectionfrom that? SQL database cannot be injected via NON SQL statemenst like COPY. Regards Pavel Stehule > > > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
i have mixed feelings about parameterised statements.
On the one hand a parameterised statement would be more difficult for a Wireshark criminal to insert their own c**p into a database because they would have to know the schema a-priori for example they would have to know the names of the database, table and the datatypes of these 2 statements
Insert DateField1 INTO DateTable VALUES(1) would gack as 1 is not a valid date
Insert NumericField1 INTO NumericTable VALUES('A') would gack as well as A is not a valid number
But thats only one line of defence..Struts..JSF and other Frameworks have field validators so a good design would involve 2 tiered defence
1)The web folks would put some kind of JS function to disallow client know from enetering a invalid date
2)The back-end folks (me) would validate Database params in the servlet or the DTO as it makes its way to the VO and before any of the offending values get into the database
The net effect is wireshark criminals are succeeding in making everyone's lives a living hell..the least we can do is take their pitchfork away!
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
> Date: Sat, 29 May 2010 22:41:04 -0700
> From: gearond@sbcglobal.net
> Subject: [GENERAL] vulnerability of COPY command
> To: pgsql-general@postgresql.org
>
> I'm trying to build a way to bulk load from a script to a Dbase, postgres.
>
> Using single, parameterized statements is a pretty good defense against SQL injection, so I use Symfony as the main user input.
>
> But for this bulk loading, it's tooooooo slow.
>
> 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?
>
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
The New Busy is not the old busy. Search, chat and e-mail from your inbox. Get started.
On the one hand a parameterised statement would be more difficult for a Wireshark criminal to insert their own c**p into a database because they would have to know the schema a-priori for example they would have to know the names of the database, table and the datatypes of these 2 statements
Insert DateField1 INTO DateTable VALUES(1) would gack as 1 is not a valid date
Insert NumericField1 INTO NumericTable VALUES('A') would gack as well as A is not a valid number
But thats only one line of defence..Struts..JSF and other Frameworks have field validators so a good design would involve 2 tiered defence
1)The web folks would put some kind of JS function to disallow client know from enetering a invalid date
2)The back-end folks (me) would validate Database params in the servlet or the DTO as it makes its way to the VO and before any of the offending values get into the database
The net effect is wireshark criminals are succeeding in making everyone's lives a living hell..the least we can do is take their pitchfork away!
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
> Date: Sat, 29 May 2010 22:41:04 -0700
> From: gearond@sbcglobal.net
> Subject: [GENERAL] vulnerability of COPY command
> To: pgsql-general@postgresql.org
>
> I'm trying to build a way to bulk load from a script to a Dbase, postgres.
>
> Using single, parameterized statements is a pretty good defense against SQL injection, so I use Symfony as the main user input.
>
> But for this bulk loading, it's tooooooo slow.
>
> 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?
>
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
The New Busy is not the old busy. Search, chat and e-mail from your inbox. Get started.
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 SQLinjection 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
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 >
Heyho! On Monday 31 May 2010 04.18:04 Dennis Gearon wrote: > One thing, can prepared statements be done, including the 'execute', > inside of a transaction, and what are the side effects? Semantically, the statement is exactly like a "not prepared" statement: it happens at execute time, the rest (prepare, variable binding) happens independently of the transaction as far as I know. Performance: there may be slight differencies since prepared statements will cache the query plan. Unless you have relatively complex queries where the execution plan depends heavily on the actual values to be bound to the prepared statement this shouldn't matter much, though. (Somebody please correct me if I'm wrong, I'm not a pg pro :-) cheers -- vbi -- Today is Sweetmorn, the 5th day of Confusion in the YOLD 3176 Celebrate Syaday
Вложения
On 2010-05-30, Martin Gainty wrote: > i have mixed feelings about parameterised statements. > > On the one hand a parameterised statement would be more > difficult for a Wireshark criminal to insert their own c**p > into a database because they would have to know the schema > a-priori for example they would have to know the names of the > database, table and the datatypes of these 2 statements > Insert DateField1 INTO DateTable VALUES(1) would gack as 1 is > not a valid date > Insert NumericField1 INTO NumericTable VALUES('A') would gack > as well as A is not a valid number > > But thats only one line of defence..Struts..JSF and other > Frameworks have field validators so a good design would > involve 2 tiered defence > 1)The web folks would put some kind of JS function to > disallow client know from enetering a invalid date Sorry, but that isn't a security measure at all, you cannot even ensure that it is executed. And if it is executed, it is executed on the client side. It is at best useful for some usability niceties. It catches some malformed input from non-malicious users, but any criminal just ignores your JS and builds the request he wants. > 2)The back-end folks (me) would validate Database params in > the servlet or the DTO as it makes its way to the VO and > before any of the offending values get into the database > That is what PreparedStatement.set*(...) (and the non-java pendants) does by properly escaping input and transforming it to a representation which exactly represents the datatype and not any kind of executable statement. > The net effect is wireshark criminals are succeeding in > making everyone's lives a living hell..the least we can do is > take their pitchfork away! > That is not a problem with (prepared) statements at all, that is a matter of protecting the wire. If you don't want your transmitted data to be manipulated, you should use at least encryption or validated signatures. -- Robert...