Thread: how to preserve \n in select statement
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
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
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.
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 textvalue 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 asnewlines 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
"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
>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
"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
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
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
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
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