http://www.perlmonks.org?node_id=243053

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
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';
works correctly.
My question is....
how do I work the substitution.
$a =~ s/chr(13)chr(10)/' + Chr(13) + Chr(10) + '/;
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.

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);
    Or use placeholders on the fly:
    my $sth = $dbh->prepare('insert into test(field1) values(?)'); $sth->execute($text);

    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)
    
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) + '/;

    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) + '/;

    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;

    Hugo
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'";

    -Matt

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)";
    Cheers all.

      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 ThingTM.

      anders pearson