Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

replacing carriage return line feed usinf chr()

by Piercer (Beadle)
on Mar 14, 2003 at 14:24 UTC ( #243053=perlquestion: print w/replies, xml ) Need Help??
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);


    (the triplet paradiddle with high-hat)
Re: replacing carriage return line feed usinf chr()
by hv (Parson) 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;

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


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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://243053]
Approved by robartes
[Discipulus]: ah so there are also coccurence of 'good' weather? ;=)

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (6)
As of 2018-01-17 08:07 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (196 votes). Check out past polls.