Re: Stripping apostrophes from data

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