pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to preserve \n in select statement
11+ messages / 6 participants
[nested] [flat]
* how to preserve \n in select statement
@ 2003-12-19 15:06 Matt Van Mater <nutter_@hotmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Matt Van Mater @ 2003-12-19 15:06 UTC (permalink / raw)
To: pgsql-sql
I have a table that has a few text value types, and I enter a bunch of text
with '\n' representing a newline. When I select the records from that
table, postgresql 7.3 represents those \n as newlines and actually outputs
the a newline rather than as a \n as entered. I want to be able to get my
\n text out of the select statement in the exact same manner it was
inserted.
I found a workaround where I can use the copy to command to copy a table to
a text file, and the command preserves the \n characters. The problem with
that is the copy to command overwrites the output file every time it is
written to. That stinks because then I can't run multiple queries and
direct the output to a file all at once. (I would prefer to set the output
file as \o '/path/to/oufile.txt' and have all queries dump their results
there)
Is there a way to make the select statement not interpret newline escape
characters?
Matt Van Mater
_________________________________________________________________
Have fun customizing MSN Messenger learn how here!
http://www.msnmessenger-download.com/tracking/reach_customize
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: how to preserve \n in select statement
@ 2003-12-19 18:29 Matt Van Mater <nutter_@hotmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Matt Van Mater @ 2003-12-19 18:29 UTC (permalink / raw)
To: guy@incentre.net; +Cc: pgsql-sql
I have been entering the data by having a command file that inserts the rows
one by one. (yes I know a copy command would probably be faster for
importing bulk data, but I prefer to insert each line individually)
IE: psql -U myusername mydatabase -f '/path/to/commandfile'
Inside the commandfile I have lines like:
INSERT INTO tablename (col1, col2, col3) VALUES ($val1, $val2, $val3);
How do I escape the data from a select statement? Something like: SELECT \*
FROM tablename; ?
----Original Message Follows----
From: Guy Fraser <guy@incentre.net>
To: Matt Van Mater <nutter_@hotmail.com>
Subject: Re: [SQL] how to preserve \n in select statement
Date: Fri, 19 Dec 2003 08:43:59 -0700
What are you using to enter and retrieve the data?
Either escape the data before you put it in the database or when you retieve
it, whichever gives you the results you are looking for.
Matt Van Mater wrote:
>I have a table that has a few text value types, and I enter a bunch of text
>with '\n' representing a newline. When I select the records from that
>table, postgresql 7.3 represents those \n as newlines and actually outputs
>the a newline rather than as a \n as entered. I want to be able to get my
>\n text out of the select statement in the exact same manner it was
>inserted.
>
>I found a workaround where I can use the copy to command to copy a table to
>a text file, and the command preserves the \n characters. The problem with
>that is the copy to command overwrites the output file every time it is
>written to. That stinks because then I can't run multiple queries and
>direct the output to a file all at once. (I would prefer to set the output
>file as \o '/path/to/oufile.txt' and have all queries dump their results
>there)
>
>Is there a way to make the select statement not interpret newline escape
>characters?
>
>Matt Van Mater
_________________________________________________________________
Check your PC for viruses with the FREE McAfee online computer scan.
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: how to preserve \n in select statement
@ 2003-12-19 21:26 Bruno Wolff III <bruno@wolff.to>
parent: Matt Van Mater <nutter_@hotmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Bruno Wolff III @ 2003-12-19 21:26 UTC (permalink / raw)
To: Matt Van Mater <nutter_@hotmail.com>; +Cc: pgsql-sql
On Fri, Dec 19, 2003 at 10:06:28 -0500,
Matt Van Mater <nutter_@hotmail.com> wrote:
> I have a table that has a few text value types, and I enter a bunch of text
> with '\n' representing a newline. When I select the records from that
> table, postgresql 7.3 represents those \n as newlines and actually outputs
> the a newline rather than as a \n as entered. I want to be able to get my
> \n text out of the select statement in the exact same manner it was
> inserted.
If you really want to store \n so that something else will interpret \n
as a newline, then use '\\n' in the string constant.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: how to preserve \n in select statement
@ 2003-12-20 04:23 Matt Van Mater <nutter_@hotmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Matt Van Mater @ 2003-12-20 04:23 UTC (permalink / raw)
To: bruno@wolff.to; +Cc: pgsql-sql
I just don't see why pgsql can't return my data exactly as I entered it. It
wouldn't be hard for me to manipulate it before entry and add another escape
character, but that's not really the point. The point is why is postgresql
changing the data I insert into a field, and not giving me away to get it
back in its original form? I wouldn't have a problem if I was notified
during an insert that my escape characters would be modified, or even if I
was given an error message and the insert failed.
One complaint about MYSQL is that it often does 'the next best thing' and
doesn't notify the user that their command or input has been altered in some
way. It seems like this is the same scenario with pgsql. I think this
behavior stems from a security problem psql had a while back where escape
characters were being interpreted, and this may be another instance of that
functionality.
----Original Message Follows----
From: Bruno Wolff III <bruno@wolff.to>
To: Matt Van Mater <nutter_@hotmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: [SQL] how to preserve \n in select statement
Date: Fri, 19 Dec 2003 15:26:07 -0600
On Fri, Dec 19, 2003 at 10:06:28 -0500,
Matt Van Mater <nutter_@hotmail.com> wrote:
> I have a table that has a few text value types, and I enter a bunch of
text
> with '\n' representing a newline. When I select the records from that
> table, postgresql 7.3 represents those \n as newlines and actually
outputs
> the a newline rather than as a \n as entered. I want to be able to get
my
> \n text out of the select statement in the exact same manner it was
> inserted.
If you really want to store \n so that something else will interpret \n
as a newline, then use '\\n' in the string constant.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
_________________________________________________________________
Tired of slow downloads? Compare online deals from your local high-speed
providers now. https://broadband.msn.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: how to preserve \n in select statement
@ 2003-12-20 04:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matt Van Mater <nutter_@hotmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2003-12-20 04:45 UTC (permalink / raw)
To: Matt Van Mater <nutter_@hotmail.com>; +Cc: bruno@wolff.to; pgsql-sql
"Matt Van Mater" <nutter_@hotmail.com> writes:
> I just don't see why pgsql can't return my data exactly as I entered
> it.
Because you are using an input syntax that requires that quotes and
backslashes be escaped. There are other input methods available that
don't require this, but they have disadvantages of their own. In
particular, you have to separate data from SQL command if you want a
no-escape-processing behavior for data.
> I think this behavior stems from a security problem psql had a while
> back where escape characters were being interpreted, and this may be
> another instance of that functionality.
Matt, you have no idea what you are talking about.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: how to preserve \n in select statement
@ 2003-12-20 17:58 Matt Van Mater <nutter_@hotmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Matt Van Mater @ 2003-12-20 17:58 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql
>Because you are using an input syntax that requires that quotes and
>backslashes be escaped. There are other input methods available that
>don't require this, but they have disadvantages of their own. In
>particular, you have to separate data from SQL command if you want a
>no-escape-processing behavior for data.
right, I was looking for the alternate input methods that you allude to. I
have been unable to find an example of exactly how to do this.
> > I think this behavior stems from a security problem psql had a while
> > back where escape characters were being interpreted, and this may be
> > another instance of that functionality.
>Matt, you have no idea what you are talking about.
I readily admit that I didn't read much into it, but I think you are
mistaken. If you look at the following links you will see that postgresql
definitely had a vulnerability that allowed sql injection through the use of
escape characters. I was simply saying that this behavior might be a way of
preventing that from happening again.
http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2002-0802
http://cert.uni-stuttgart.de/doc/postgresql/escape/
_________________________________________________________________
Get dial-up Internet access now with our best offer: 6 months @$9.95/month!
http://join.msn.com/?page=dept/dialup
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: how to preserve \n in select statement
@ 2003-12-20 18:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matt Van Mater <nutter_@hotmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Tom Lane @ 2003-12-20 18:35 UTC (permalink / raw)
To: Matt Van Mater <nutter_@hotmail.com>; +Cc: pgsql-sql
"Matt Van Mater" <nutter_@hotmail.com> writes:
>> In particular, you have to separate data from SQL command if you want a
>> no-escape-processing behavior for data.
> right, I was looking for the alternate input methods that you allude to. I
> have been unable to find an example of exactly how to do this.
The best way at the moment is to use the parameterized-statement
features that are new in 7.4. If using libpq, see PQexecParams()
and siblings.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: how to preserve \n in select statement
@ 2003-12-22 09:04 Richard Huxton <dev@archonet.com>
parent: Matt Van Mater <nutter_@hotmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Richard Huxton @ 2003-12-22 09:04 UTC (permalink / raw)
To: Matt Van Mater <nutter_@hotmail.com>; tgl@sss.pgh.pa.us; +Cc: pgsql-sql
On Saturday 20 December 2003 17:58, Matt Van Mater wrote:
>
> I readily admit that I didn't read much into it, but I think you are
> mistaken. If you look at the following links you will see that postgresql
> definitely had a vulnerability that allowed sql injection through the use
> of escape characters. I was simply saying that this behavior might be a
> way of preventing that from happening again.
> http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2002-0802
> http://cert.uni-stuttgart.de/doc/postgresql/escape/
Ah - this "vulnerability" is still there - and, in fact is in every database
ever produced. The issue is that applications using untrusted data to build a
query string can be duped by a malicious user.
So - say you have a query template:
SELECT * FROM recent_news WHERE topic='?'
If a malicious user supplies the string: '; DELETE FROM user_logins; SELECT '
then you will have the resulting query string:
SELECT * FROM recent_news WHERE topic=''; DELETE FROM user_logins; SELECT ''
If you allow multiple queries in one string, there is no way to prevent this.
If you disallow multiple queries, there are still attacks that are possible.
This is why it is vital to parse and validate user input. If you are asking
for an integer, check that it is. If you are asking for a string, quotes etc.
need to be escaped (AFAICT the stuttgart url describes a function that lets
you do this - you still need to call it).
The first URL seems to deal with an old bug that meant a cunning attacker
could bypass your escaping. It is still vital that the application (or some
middle layer) validates and checks all untrusted (and preferably all trusted)
input. All standard database layers (Perl/PHP/Java, whatever) supply tools
for this.
Your particular issue with \n is just down to PG's standard string parsing -
not really related.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: how to preserve \n in select statement
@ 2003-12-22 09:37 Denis <sqllist@coralindia.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 11+ messages in thread
From: Denis @ 2003-12-22 09:37 UTC (permalink / raw)
To: pgsql-sql
Hi Richard..
If your users are required to fire only SELECT and no DML, you can do the
following:
BEGIN;
execute the statements given by user
ROLLBACK;
This will not affect your SELECT and also if any malicious user gives DELETE
statement, that will not have any impact too..
HTH
Thanx
Denis
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Matt Van Mater" <nutter_@hotmail.com>; <tgl@sss.pgh.pa.us>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, December 22, 2003 2:34 PM
Subject: Re: [SQL] how to preserve \n in select statement
> On Saturday 20 December 2003 17:58, Matt Van Mater wrote:
> >
> > I readily admit that I didn't read much into it, but I think you are
> > mistaken. If you look at the following links you will see that
postgresql
> > definitely had a vulnerability that allowed sql injection through the
use
> > of escape characters. I was simply saying that this behavior might be a
> > way of preventing that from happening again.
> > http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2002-0802
> > http://cert.uni-stuttgart.de/doc/postgresql/escape/
>
> Ah - this "vulnerability" is still there - and, in fact is in every
database
> ever produced. The issue is that applications using untrusted data to
build a
> query string can be duped by a malicious user.
>
> So - say you have a query template:
> SELECT * FROM recent_news WHERE topic='?'
>
> If a malicious user supplies the string: '; DELETE FROM user_logins;
SELECT '
> then you will have the resulting query string:
> SELECT * FROM recent_news WHERE topic=''; DELETE FROM user_logins; SELECT
''
>
> If you allow multiple queries in one string, there is no way to prevent
this.
> If you disallow multiple queries, there are still attacks that are
possible.
>
> This is why it is vital to parse and validate user input. If you are
asking
> for an integer, check that it is. If you are asking for a string, quotes
etc.
> need to be escaped (AFAICT the stuttgart url describes a function that
lets
> you do this - you still need to call it).
>
> The first URL seems to deal with an old bug that meant a cunning attacker
> could bypass your escaping. It is still vital that the application (or
some
> middle layer) validates and checks all untrusted (and preferably all
trusted)
> input. All standard database layers (Perl/PHP/Java, whatever) supply tools
> for this.
>
> Your particular issue with \n is just down to PG's standard string
parsing -
> not really related.
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: how to preserve \n in select statement
@ 2003-12-22 10:48 Richard Huxton <dev@archonet.com>
parent: Denis <sqllist@coralindia.com>
0 siblings, 1 reply; 11+ messages in thread
From: Richard Huxton @ 2003-12-22 10:48 UTC (permalink / raw)
To: Denis <sqllist@coralindia.com>; pgsql-sql
On Monday 22 December 2003 09:37, Denis wrote:
> Hi Richard..
>
> If your users are required to fire only SELECT and no DML, you can do the
> following:
>
> BEGIN;
> execute the statements given by user
> ROLLBACK;
>
> This will not affect your SELECT and also if any malicious user gives
> DELETE statement, that will not have any impact too..
An interesting idea, though you'd need to be careful with side-effects
(triggers/functions etc). I seem to recall a "read-only" setting being
discussed for transactions too (though not as a security measure, I should
emphasise).
The other thing is to use the database user/group mechanism - something which
tends to be neglected with web-based apps (partly because different DBs have
different setups here).
If only an application super-user can add/delete users make sure the
permissions reflect this and connect as a more restricted user for other
logins.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: how to preserve \n in select statement
@ 2003-12-24 03:56 Iain <iain@mst.co.jp>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 11+ messages in thread
From: Iain @ 2003-12-24 03:56 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Denis <sqllist@coralindia.com>; pgsql-sql
Isn't the simple answer to use bind variables?
SQL using bind variables instead of making a new SQL string each time will
prevent malicious users from invoking functions and inserting other sql, as
well as handle the original problem regarding storage of newlines vs \n.
I don't know much about Postgres' SQL cache, but it is well known in Oracle
circles that using bind variables is is a critical part of system design,
not just for security, but for performance and scalability. I suspect that
the same issues apply more or less to postgres.
Correct me if I'm wrong, please...
regards
Iain
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Denis" <sqllist@coralindia.com>; <pgsql-sql@postgresql.org>
Sent: Monday, December 22, 2003 7:48 PM
Subject: Re: [SQL] how to preserve \n in select statement
> On Monday 22 December 2003 09:37, Denis wrote:
> > Hi Richard..
> >
> > If your users are required to fire only SELECT and no DML, you can do
the
> > following:
> >
> > BEGIN;
> > execute the statements given by user
> > ROLLBACK;
> >
> > This will not affect your SELECT and also if any malicious user gives
> > DELETE statement, that will not have any impact too..
>
> An interesting idea, though you'd need to be careful with side-effects
> (triggers/functions etc). I seem to recall a "read-only" setting being
> discussed for transactions too (though not as a security measure, I should
> emphasise).
>
> The other thing is to use the database user/group mechanism - something
which
> tends to be neglected with web-based apps (partly because different DBs
have
> different setups here).
> If only an application super-user can add/delete users make sure the
> permissions reflect this and connect as a more restricted user for other
> logins.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-12-24 03:56 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-19 15:06 how to preserve \n in select statement Matt Van Mater <nutter_@hotmail.com>
2003-12-19 21:26 ` Bruno Wolff III <bruno@wolff.to>
2003-12-19 18:29 Re: how to preserve \n in select statement Matt Van Mater <nutter_@hotmail.com>
2003-12-20 04:23 Re: how to preserve \n in select statement Matt Van Mater <nutter_@hotmail.com>
2003-12-20 04:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-20 17:58 Re: how to preserve \n in select statement Matt Van Mater <nutter_@hotmail.com>
2003-12-20 18:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-22 09:04 ` Richard Huxton <dev@archonet.com>
2003-12-22 09:37 ` Denis <sqllist@coralindia.com>
2003-12-22 10:48 ` Richard Huxton <dev@archonet.com>
2003-12-24 03:56 ` Iain <iain@mst.co.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox