The dollar quoting appears to have fixed it; thank you. I apologize for my folly in sending out the original message.
Michael Glaesemann <grzm@seespotcode.net> wrote:
On Aug 20, 2007, at 11:19 , Andrew Edson wrote:
> Is there some program or procedure for stripping apostrophes (') > from data in the db? Most of our data has been shuffled over to > Postgres from an older system, and I'm occasionally running into > data entered in the old system that has apostrophes in it. (Most > recent example: A name field with the word "Today's" in it.)
Do you want to remove the double quotes around the word or the apostrophe between y and s? Regardless, you might want to look at the regexp_replace or translate functions:
> Given that most of my interactions with the database are through > perl scripts and php pages, I can't always tell ahead of time what > field I need is going to contain data that's deadly to my statements.
Sounds like a problem with how you're handling your data in your middleware, as this shouldn't be a problem regardless of the characters in the string if you're handling things correctly. If you post an example perhaps people can offer suggestions on how you can handle things more safely. Are you interpolating variables directly into SQL statements? If so, don't do that: use bind variables instead.
> Alternately, is there some way of inserting or selecting data from > the db which doesn't require the use of apostrophes for non-numeric > fields?
You could use dollar quotes, but it sounds like your problem might be able to be solved using bind variables.
