Re: [GENERAL] Stripping apostrophes from data
[Please don't top post as it makes the discussion more difficult to follow.] On Aug 20, 2007, at 13:21 , Andrew Edson wrote: The dollar quoting appears to have fixed it; thank you. I apologize for my folly in sending out the original message. I think this might be giving you a false sense of security. It looks like I wasn't the only one to think you're probably doing something unsafe. If you're interested in improving your code to make sure this can never be a problem, look into bind variables (and prepared statements). If you're directly interpolating variables into a query string, you're just asking for trouble, regardless of what quoting method you're using. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Stripping apostrophes from data
The dollar quoting appears to have fixed it; thank you. I apologize for my
folly in sending out the original message.
Michael Glaesemann <[EMAIL PROTECTED]> 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:
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
-
Be a better Heartthrob. Get better relationship answers from someone who knows.
Yahoo! Answers - Check it out.
Re: [GENERAL] Stripping apostrophes from data
On 8/20/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > > > 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? > > Uhmm just prepare all your statements and this shouldn't be an issue. > .. which is a good idea anyway when you're dealing with data which cannot easily be verified as 'safe' (such as text data) -- it's a great way to prevent all SQL injections, even when you're not expecting quotes. -- Leon Mergen http://www.solatis.com
Re: [GENERAL] 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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Stripping apostrophes from data
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.) 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.
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?
Luggage? GPS? Comic books?
Check out fitting gifts for grads
at Yahoo! Search.
In php you can use |pg_escape_string function:
http://us3.php.net/manual/en/function.pg-escape-string.php|
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Stripping apostrophes from data
On Mon, Aug 20, 2007 at 09:19:14AM -0700, 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.) 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.
>
> 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?
Umm, why are apostrophes causing a problem? Normally you just escape
them or, if you don't want to worry about them at all, use queries with
placeholders.
Have a nice day,
--
Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] Stripping apostrophes from data
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
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.) 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.
>
> 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?
Uhmm just prepare all your statements and this shouldn't be an issue.
Joshua D. Drake
>
>
> -
> Luggage? GPS? Comic books?
> Check out fitting gifts for grads at Yahoo! Search.
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGycGdATb/zqfZUUQRAkZpAJ0dbBVc8Y/Sk5mIwMICin1UyL3eWwCfTlLJ
uAHznl5Cf0geQYLvOcUs+ks=
=t9rL
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] Stripping apostrophes from data
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.) 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.
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?
-
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search.
