Clean a database record off an apostrophe '

peacemaker1820 has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks.
I have a database of customers and their addresses. Some of the words in my address fields in the database contain apostrophe '.
Now, when i get the address field , i pass it to javascript method for some more work. here is part of the code:

# Fetch row
my ( $custno, $companyName, $address, $city, $state, $zip) = $SQL->fet
+chrow_array();

print <<EndHTML;
<script language="javascript">
fillCustomer('$custno', '$companyName', '$address', '$city', '$state',
+ '$zip');
</script>
EndHTML
[download]

my problem is that it gives me a runtime error when ever my address record contains an apostrophe character ('). My idea is that somehow i need to add an escape character in that record before the apostrophe, but i do not want to do that.
Any other ideas, guys?
Greatly appreciated.

Comment on Clean a database record off an apostrophe ' Download Code

Replies are listed 'Best First'.
Re: Clean a database record off an apostrophe ' by japhy (Canon) on Jul 29, 2002 at 19:39 UTC
You can remove the character altogether: `$field =~ tr/'//d`, or you could backslash it: `$field =~ s/'/\\'/g`, or perhaps replace it with the HTML entity code ''': `s/'/'/g;` _____________________________________________________ Jeff`[japhy]`Pinyan: Perl, regex, and perl hacker, who'd like a job (NYC-area) `s++=END;++y(;-P)}y js++=;shajsj<++y(p-q)}?print:??;`	[reply] [d/l] [select]
Re: Clean a database record off an apostrophe ' by dws (Chancellor) on Jul 29, 2002 at 19:36 UTC
my problem is that it gives me a runtime error when ever my address record contains an apostrophe character ('). You're on the right track in thinking that you need to escape the single quote, but on the wrong track in thinking that you need to do this in the database. It suffices to "escape" the single quotes right before you emit the Javascript. Try something like the following (untested, but I've done something like this before a few years back): `# after you've fetched the row my $escapedCustno = jsescape($custno); ... print <<EndHTML; <script language="javascript"> fillCustomer('$escapedCustno', ... sub jsescape { my $str = shift; $str =~ s/("')/\\$1/g; $str; }` [download]	[reply] [d/l]
Re: Clean a database record off an apostrophe ' by vladb (Vicar) on Jul 29, 2002 at 19:33 UTC
Well, one straightforward approach to solving something like this would be to replace ' with " like I did here: `# Fetch row my ( $custno, $companyName, $address, $city, $state, $zip) = $SQL->fet +chrow_array(); print <<EndHTML; <script language="javascript"> fillCustomer("$custno", "$companyName", "$address", "$city", "$state", + "$zip"); </script> EndHTML` [download] So, it wouldn't matter whether your address field has any ' in it. _____________________ `# Under Construction` [download]	[reply] [d/l] [select]
Re: Clean a database record off an apostrophe ' by aufrank (Pilgrim) on Jul 30, 2002 at 15:52 UTC
dbi provides useful quoting methods, which are designed to prepare your data for use in an sql statement, but could just as easily make it pretty enough for javascript. consider: `map {$dbh->quote($_) } ($custno, $companyName, $address, $city, $state +, $zip);` [download] or, for double quotes around most values, try `$dbh->quote_identifier($_)` in the map instead. hth, --au	[reply] [d/l] [select]