Обсуждение: Backslashes in bytea values
My company is using Postgres 8.3.8. We have some tables with bytea columns that store file data such as the contents of PDF or RTF files. The escape_string_warning parameter is on, and inserting this kind of data causes the backslash warning to be generated, since the backslash character appears in the raw data of these types of files. I have updated much of our code to use the E'' syntax in order to suppress these messages. I am trying to figure out what the repercussions would be if I were to turn on the standard_conforming_strings parameter. None of our code actually uses a backslash to escape a double-quote, but I don't know how this would affect the bytea values which I discussed above. How are backslashes affected if they are actually part of the raw data? -- Thank you, Lewis Kapell Computer Operations Seton Home Study School
Lewis Kapell <lkapell@setonhome.org> wrote: > My company is using Postgres 8.3.8. We have some tables with > bytea columns that store file data such as the contents of PDF or > RTF files. Yeah, we do a lot of that, too. > The escape_string_warning parameter is on, and inserting this kind > of data causes the backslash warning to be generated, since the > backslash character appears in the raw data of these types of > files. We turn that off. > I have updated much of our code to use the E'' syntax in order to > suppress these messages. If you do that, you'd better be doing something to make sure you have cured the problem the messages warned of; otherwise you're going to have corrupted documents. Are you using prepared statements and setting the values through those (recommended) or escaping the backslashes, etc. yourself? > I am trying to figure out what the repercussions would be if I > were to turn on the standard_conforming_strings parameter. With that on, absolutely all characters are taken as part of the literal except for apostrophes. That makes it a bit easier to do the escaping, and could result in a slight reduction in statement size. > None of our code actually uses a backslash to escape a double- > quote, but I don't know how this would affect the bytea values > which I discussed above. How are backslashes affected if they are > actually part of the raw data? If you're just throwing them inside a E'xxx' literal that you build yourself, it sounds like you've currently got a problem. If you are using parameters on prepared statements, it shouldn't matter much. -Kevin
Kevin, Thanks for your reply. I forgot to mention that all of these insertions are being done via PHP scripts, and we are using the pg_escape_bytea function (part of the PHP Postgres extension) to escape the data being inserted into bytea columns. Rather important item, that. Does this address all the concerns which you raised? Thanks Lewis Kevin Grittner wrote: > Lewis Kapell <lkapell@setonhome.org> wrote: > >> I have updated much of our code to use the E'' syntax in order to >> suppress these messages. > > If you do that, you'd better be doing something to make sure you > have cured the problem the messages warned of; otherwise you're > going to have corrupted documents. Are you using prepared > statements and setting the values through those (recommended) or > escaping the backslashes, etc. yourself? > >> I am trying to figure out what the repercussions would be if I >> were to turn on the standard_conforming_strings parameter. > > With that on, absolutely all characters are taken as part of the > literal except for apostrophes. That makes it a bit easier to do > the escaping, and could result in a slight reduction in statement > size. > >> None of our code actually uses a backslash to escape a double- >> quote, but I don't know how this would affect the bytea values >> which I discussed above. How are backslashes affected if they are >> actually part of the raw data? > > If you're just throwing them inside a E'xxx' literal that you build > yourself, it sounds like you've currently got a problem. If you are > using parameters on prepared statements, it shouldn't matter much. > > -Kevin
Lewis Kapell <lkapell@setonhome.org> wrote: > I forgot to mention that all of these insertions are being done > via PHP scripts, and we are using the pg_escape_bytea function > (part of the PHP Postgres extension) to escape the data being > inserted into bytea columns. Rather important item, that. > > Does this address all the concerns which you raised? I'm not familiar with that extension or its functions, but it sounds like you're probably in better shape than I thought when I read your first email. :-) Perhaps someone who knows that extension can comment on the issues around E'' literals, the escape_string_warning setting and the standard_conforming_strings setting. -Kevin
Now that I think of it, pg_escape_bytea operates on a handle to the current database connection, and I believe it queries the value of standard_conforming_strings so that it can determine how the data should be escaped. I think I verified this by testing some time ago, although I hadn't thought of it until just now, and I would need to test again to make sure. Sorry I didn't think this through more carefully before I wrote. Thanks Lewis Kevin Grittner wrote: > Lewis Kapell <lkapell@setonhome.org> wrote: > >> I forgot to mention that all of these insertions are being done >> via PHP scripts, and we are using the pg_escape_bytea function >> (part of the PHP Postgres extension) to escape the data being >> inserted into bytea columns. Rather important item, that. >> >> Does this address all the concerns which you raised? > > I'm not familiar with that extension or its functions, but it sounds > like you're probably in better shape than I thought when I read your > first email. :-) > > Perhaps someone who knows that extension can comment on the issues > around E'' literals, the escape_string_warning setting and the > standard_conforming_strings setting. > > -Kevin