Taking care of backslashes (related to mySQL)

Hi,

I have an array @body, which is actually the body of an email. In the contents of the email, there are some "", so when I insert @body into mySQL, all the "" disappear because they are used for escape characters such as n. How can I correct this problem? (double quotes work fine though..)

The way I insert it is like this:
$dbh->do("INSERT INTO tbl VALUES ('@body')");
where the field is of type LONGTEXT.

I was trying something like
@body =~ s///;
in an attempt to replace all single backslashes to double backslashes, but apparently the above has incorrect syntax.

Thanks for the help in advance!

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by syin:
I have an array @body, which is actually the body of an email. In the contents of the email, there are some "", so when I insert @body into mySQL, all the "" disappear because they are used for escape characters such as n.[/quote]

There is a DBI call to fix this. You have other sequences to worry about (like single quotes). The function is $dbh->quote($string);

I don't think you can use it to do entire arrays (try!)... if not, you would do this:

foreach $line (@body) { $dbh->quote($line); }

This function does the same sort of substitution you tried... you just need to double your slashes:

foreach (@body) { s///; }

DISCLAIMER: This is all from memory and untested. Good luck.

amead:

Thank you very much for your help. I used

foreach (@body) { s///; }

and it worked. However, when I have a path like: dirsubdirfile, the above only change it to dirsubdirfile. Why is that? I thought it would find all instances of and replace it with , so I was expecting dirsubdirfile. What did I do wrong?

Also, I gave the quote() a try. First of all, I did the experiement and it did not take an array as an arguement. It went thru when I try your other suggested method (foreach $line....), but somehow the string coming out is exactly the same as before. I am wondering if quote() put the result somewhere else instead of modifiy the variable I pass to it? I want the modified string to be in the @body array...

Anyway, thanks again for your help!

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by syin:
amead:

Thank you very much for your help. I used

foreach (@body) { s///; }

and it worked. However, when I have a path like: dirsubdirfile, the above only change it to dirsubdirfile.[/quote]

I forgot that you need to append a 'g' to the sub:

foreach (@body) { s///g; }


<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Also, I gave the quote() a try. [...] when I try your other suggested method (foreach $line....), but somehow the string coming out is exactly the same as before. I am wondering if quote() put the result somewhere else instead of modifiy the variable I pass to it?[/quote]

Hmm. Try:

foreach $item (@body) {
$item = $dbh->quote($item);
}

Of course, only lines with slashes, single apostrophes, newlines, etc. will be affected.

-Alan

Thanks again. I used the /g at the end of the pattern match and it worked. I didn't use quote because the result is not too HTML-friendly.