replacing carriage return line feed usinf chr()

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

I'm trying to put together an SQL statement to write some data to an access database. Some of the fields I want to write are multi line, its windows so the data contains carridge return line feed (ascii values 13 and 10). Unfortunately the sql statement regards this as a return character so it only processes half the line eg...

$a = "aaaaa\n";
$a = $a."bbbbb";
$sql="insert into test (field1) value '$a';

produces
insert into test (field1) value 'aaaaa
bbbbb'

what it actually runs is 
insert into test (field1) value 'aaaaa
[download]

Which obviously doesn't work. What I need to do (I think) is to replace the cr/lf characters in the text with ' + Chr(13) + Chr(10) + '

$sql="insert into test (field1) value 'aaaaaaa' + Chr(13) + Chr(10) + 
+'bbbbbbbb';
[download]

works correctly.
My question is....
how do I work the substitution.

$a =~ s/chr(13)chr(10)/' + Chr(13) + Chr(10) + '/;
[download]

doesn't work. As far as I can see it is interpreting + Chr(13) + Chr(10) and putting it back in as ascii value 13 and ascii value 10 rather than padding it out the way I need. Any help would be greatly appreciated.

Comment on replacing carriage return line feed usinf chr() Select or Download Code

Replies are listed 'Best First'.
(jeffa) Re: replacing carriage return line feed usinf chr() by jeffa (Bishop) on Mar 14, 2003 at 15:10 UTC
How about letting DBI handle this instead? `my $dbh = DBI->connect( ... ); my $text = "aaaaa\nbbbb"; my $sql = $dbh->quote($text);` [download] Or use placeholders on the fly: `my $sth = $dbh->prepare('insert into test(field1) values(?)'); $sth->execute($text);` [download] jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l] [select]
Re: replacing carriage return line feed usinf chr() by hv (Prior) on Mar 14, 2003 at 14:33 UTC
The first part of a substitution is a regular expression, so you can't put arbitrary code such as `chr(13)` in there and expect it to do anything useful. In general, you can refer to a given character using an escape sequence such as `\x` (for hexadecimal): `$a =~ s/\x0d\x0a/' + Chr(13) + Chr(10) + '/;` [download] These particular two cases are common enough to have their own names, though: ASCII chr(13) can be expressed in regular expressions as `\r` and chr(10) as `\n`: `$a =~ s/\r\n/' + Chr(13) + Chr(10) + '/;` [download] Note also that you probably need to change all such cr/lf sequences, so you should probably be using the `g` option on the substition: `$a =~ s/\r\n/' + Chr(13) + Chr(10) + '/g;` [download] Hugo	[reply] [d/l] [select]
Re: replacing carriage return line feed usinf chr() by DrManhattan (Chaplain) on Mar 14, 2003 at 15:20 UTC
"\r" is interpolated as chr(13) and "\n" is chr(10). You should be able to do something like this: `$sql = "insert into test (field1) value 'aaaaaaa\r\nbbbb'";` [download] -Matt	[reply] [d/l]
Re: replacing carriage return line feed usinf chr() by Piercer (Beadle) on Mar 14, 2003 at 17:45 UTC
Many thanks to all who replied - I eventually found a way to do it using split and join. This worked for me.... `$a = "\'aaaaa\nbbbbbb\'"; $a = join ('\' + Chr(13) + Chr(10) + \'', split(/\n/, $a)); $sql="insert into KB (Description) values ($a)";` [download] Cheers all.	[reply] [d/l]
Re: Re: replacing carriage return line feed usinf chr() by thraxil (Prior) on Mar 14, 2003 at 19:22 UTC
you really, really should follow jeffa's advice and use DBI's placeholders to handle this kind of thing. your solution handles \r and \n but if $a has a ')' or a quote or any other weird character that you haven't accounted for, it will still break. your solution at best is fragile and at worst could become a security hole. placeholders are the Right Thing^TM. anders pearson	[reply]