Обсуждение: copymanager question
Hello all I am using cm instead of preparedstmts for faster inserts . But have a question on inserting values which have quotes in them (maybe single or double). Using preparedstmts, we know its safe to do so by calling setString. Is it possible to do the same here as i do not want to blindly single quote a string value in copyin. Basically i am looking for the same robustness of preparedstmt@setString Can i use functions like quote_literal ? Any help is very much appreciated. thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/copymanager-question-tp5817197.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
swaroop wrote > Hello all > > I am using cm instead of preparedstmts for faster inserts . But have a > question on inserting > values which have quotes in them (maybe single or double). Using > preparedstmts, we know > its safe to do so by calling setString. Is it possible to do the same here > as i do not want > to blindly single quote a string value in copyin. > > Basically i am looking for the same robustness of preparedstmt@setString > > Can i use functions like quote_literal ? Any help is very much > appreciated. > > thanks Copy data is literal. Since there is no interpretation of the data there is no opportunity for injection. You might want to provide an example as I'm not fully clear on exactly what your need is. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/copymanager-question-tp5817197p5817227.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 09/01/2014 11:24 PM, swaroop wrote: > Basically i am looking for the same robustness of preparedstmt@setString > > Can i use functions like quote_literal ? Any help is very much appreciated. You don't need to. Feed the data in as-is. So long as it's valid CSV (for CSV-format COPY) or meets the PostgreSQL spec for the input format (for non-CSV) it's fine. So for CSV format you must escape embedded quotes, otherwise you'll produce invalid CSV and get an error on input. That's the worst that'll happen though. SQL injection is not possible in the data consumed by COPY. (A truly tricky user could sneak malicious code into a table then trick you into running a vulnerable PL/PgSQL procedure that used unsafe dynamic SQL later, but that's also true with calling the function using prepared statements.) -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
swaroop wrote > one more - i also need to insert useragent strings which have all sorts of > characters in them eg > Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/525.13 (KHTML, > like Gecko) Chrome/0.2.149.29 Safari/525.13 > > Here there is a comma which causes the copy to fail. Other words do not > have comma in them. > > Can you please point me to the correct way to escape this ? > > To summarize - how do i replace the input strings in java (data is > streamed in and i do a copy to postgres) > so that CSV copy does not fail. > a. words with comma > b. words with double quotes in them > c. words with \ (backslash) In CSV (comma), strings are to be surrounded with double-quotes. These are optional unless the string can contain a comma or double-quote itself. Inside a double-quoted strong a comma is just a comma. A double-quote has to be escapedd but doubling it. "String ""quote"" , string". Backslash doesn't do anything special. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/copymanager-question-tp5817197p5817357.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Thanks for getting back. So just to confirm if a string value , we need to take care off it ourselves by escaping any special character and quote (single, double) ? I am not exactly sure off the escaping part for each of the characters - have read that for single quotes, we need to add one more single quote to escape . Is this true ? Please can you confirm this and specifically for any other character. basically i want to ensure that if i get a string say abc'd"e\fgh/" or something which has characters i need to escape, i need to be aware off which are those characters for which copy command can consider it as invalid CSV. Currently using this COPY table(col1,col2,...coln) FROM STDIN WITH CSV NULL AS 'NULL-VAL' , new ByteArrayInputStream(bytes) thanks again. -- View this message in context: http://postgresql.1045698.n5.nabble.com/copymanager-question-tp5817197p5817315.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
one more - i also need to insert useragent strings which have all sorts of characters in them eg Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/525.13 (KHTML, like Gecko) Chrome/0.2.149.29 Safari/525.13 Here there is a comma which causes the copy to fail. Other words do not have comma in them. Can you please point me to the correct way to escape this ? To summarize - how do i replace the input strings in java (data is streamed in and i do a copy to postgres) so that CSV copy does not fail. a. words with comma b. words with double quotes in them c. words with \ (backslash) -- View this message in context: http://postgresql.1045698.n5.nabble.com/copymanager-question-tp5817197p5817334.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 09/02/2014 01:28 AM, swaroop wrote: > > Thanks for getting back. > > So just to confirm if a string value , we need to take care off it ourselves > by escaping > any special character and quote (single, double) ? I am not exactly sure off > the escaping part > for each of the characters - have read that for single quotes, we need to > add one more single > quote to escape . Is this true ? Please can you confirm this and > specifically for any other character. > > basically i want to ensure that if i get a string say abc'd"e\fgh/" or > something which has characters > i need to escape, i need to be aware off which are those characters for > which copy command > can consider it as invalid CSV. A good place to start is: http://www.postgresql.org/docs/9.3/interactive/sql-copy.html CSV Format ".... The values in each record are separated by the DELIMITER character. If the value contains the delimiter character, the QUOTE character, the NULL string, a carriage return, or line feed character, then the whole value is prefixed and suffixed by the QUOTE character, and any occurrence within the value of a QUOTE character or the ESCAPE character is preceded by the escape character. You can also use FORCE_QUOTE to force quotes when outputting non-NULL values in specific columns. ..." There is more, so I would read the whole section. > > Currently using this COPY table(col1,col2,...coln) FROM STDIN WITH CSV NULL > AS 'NULL-VAL' , new ByteArrayInputStream(bytes) > > > thanks again. > > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/copymanager-question-tp5817197p5817315.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 09/02/2014 03:20 AM, swaroop wrote: > one more - i also need to insert useragent strings which have all sorts of > characters in them eg > Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/525.13 (KHTML, > like Gecko) Chrome/0.2.149.29 Safari/525.13 > > Here there is a comma which causes the copy to fail. Other words do not have > comma in them. My solution is either quote the entire string or use a different delimiter. I usually use | as it not often found in data. > > Can you please point me to the correct way to escape this ? > > To summarize - how do i replace the input strings in java (data is streamed > in and i do a copy to postgres) > so that CSV copy does not fail. > a. words with comma > b. words with double quotes in them > c. words with \ (backslash) > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/copymanager-question-tp5817197p5817334.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
hi david, Thanks for all the help . following those steps helped. -- View this message in context: http://postgresql.1045698.n5.nabble.com/copymanager-question-tp5817197p5817425.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 09/02/2014 06:20 PM, swaroop wrote: > To summarize - how do i replace the input strings in java (data is streamed > in and i do a copy to postgres) > so that CSV copy does not fail. > a. words with comma > b. words with double quotes in them > c. words with \ (backslash) Don't write the escaping yourself. Use a CSV library that can consume your input data and emit correct CSV. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services