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:
http://www.postgresql.org/docs/8.2/interactive/functions-string.html
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.
Michael Glaesemann grzm seespotcode net ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
