Escaping CRLF (What Happened)

[1DMF]

Ok I have a SQL db it saves user input from a <textarea> input box. Everything was working fine, then today, and believe me no code regarding diplaying and saving has changed, every 2 CRLF displayed with 4 CRLF and then when saved and screen displayed there were 6 CRLF, and so on each time the form was submitted and re-displayed 2 extra CRLF were appearing between each line of text.

Looking for a mortgage but don't know where to turn?

Need extra cash for that special purchase?

became

Looking for a mortgage but don't know where to turn?



Need extra cash for that special purchase?

next time round

Looking for a mortgage but don't know where to turn?





Need extra cash for that special purchase?

you get the picture but WHY?, why did it save and retrieve fine before and not now and nothing has changed (I know you don't believe me guys - but here is the save routine and after checking the SQL there we only 2 CRLF as expected.

sub page_upd {
    
#_[0] = PageID

# Build Style String
my $tit = "$data{'TFont'},$data{'TFSize'},$data{'TitCol'},$data{'TBGCol'}";
my $txt = "$data{'PFType'},$data{'PFSize'},$data{'PTxtCol'},$data{'TxtBGColSel'}";
my $upd = "";

# Swap DSN to AR
&swap_dsn("AR");

# Check if page text has changed for user editible text.
if($_[0] eq "index" || $_[0] eq "aboutus"){
    
    # Escape data
    my @oldtxt = &getSQL("AR_Pages","PageText","ARID='$AR' AND PageID ='$_[0]'");
    
    if($oldtxt[0]{'PageText'} ne $data{'PageText'}){
        $upd = "y";
    }
    else {$upd = "n";}
}

# Escape Apostophe
$data{'PageText'} =~ s/'/''/g;

# Update Page Info
my $rs = &updSQL("AR_Pages","PageTitleStyle='$tit',PageTextStyle='$txt',PageText='$data{'PageText'}',PageImg='$data{'PageImg'}',Upd='$upd'","ARID='$AR' AND PageID='$_[0]'");

# Redirect to Main 

print "Location: " . URL_TO_CGI . "/website.cgi?user=$user\n\n";

}

and here is the display

# Add Menu and Page Header
my $catlist = &get_menu("$script");

# Swap DSN to AR
&swap_dsn("AR");

# Check which page text to get
my @hlp = "";

if($_[0] eq "index" or $_[0] eq "aboutus"){
    @hlp = &getSQL("AR_Pages","PageTitle,PageText","ARID='$AR' AND PageID ='$_[0]'");
}
else {  
    @hlp = &getSQL("HLP_Pages","PageTitle,PageText","PageID='$_[0]'");
}

# Get Page Info
my @page = &getSQL("AR_Pages","PageTitleStyle,PageTextStyle,PageImg","ARID='$AR' AND PageID='$_[0]'");

###########################
# Split into style arrays #
###########################
# Font , size, colour, bgcol (if applicable)

my @ptit_style = split(/,/, $page[0]{'PageTitleStyle'});
my @ptxt_style = split(/,/, $page[0]{'PageTextStyle'});
my $oldtxt = uri_escape($hlp[0]{'PageText'});

# Continue with Main page
$catlist .= " 
<TABLE width=\"85%\" >
<tr>
    <td height=10 class=\"titlecell\" onmouseover=\"window.status='Please choose a menu option'\">&nbsp;$_[1] Configuration</td>
</tr>
<tr>
<td>
<table>
<form name=\"arpage\" action=\"" . URL_TO_CGI . "/website.cgi\" method=\"post\">
<input type=\"hidden\" name=\"PageID\" value=\"$_[0]\">
<input type=\"hidden\" name=\"user\" value=\"$user\">
<tr >
<td width=\"260\">
<!--- User Selectable Images --->
<center>
<font style=\"font-size:14pt;\">Image : </font><select name=\"PageImg\" style=\"cursor:pointer\" onChange=\"document.PageImg.src='" . AR_URL_TO_WEB . "/images/' + document.arpage.PageImg.value;\">
<option value=\"$page[0]{'PageImg'}\">Please Select ($page[0]{'PageImg'})</option>";

# Get Images
my @img = glob(AR_DIR_TO_WEB . '/images/*');

# Loop & Build html select list
for(@img){
    my $path = AR_DIR_TO_WEB . "/images/";
    $_ =~ s/$path//g;
    $catlist .= "<option value=\"$_\">Image($_)</option>";
}

$catlist .= "</select>
<img src=\"" . AR_URL_TO_WEB . "/images/$page[0]{'PageImg'}\" name=\"PageImg\" width=\"254\" height=\"400\">
</td>

<td valign=top width=315><center><font style=\"font-size:14pt;\">
Main Page Text</font><br>
<textarea name=\"PageText\" onFocus=\"$ronly\" id=\"PageText\" style=\"width:315px; height=400px; font-family:$ptxt_style[0]; font-size:$ptxt_style[1]pt; color:$ptxt_style[2]; background:$ptxt_style[3]; scrollbar-base-color:$ptxt_style[3]; scrollbar-shadow-color:$ptxt_style[2]; border:none;\" $_[2]>$hlp[0]{'PageText'}</textarea>

I've left all the code in from the top down to the textarea in question just so you can see there is nothing being done to the data $hlp[0]{'PageText'}.

now before the display i've added this line

# sort out carriage returns for some reason
$hlp[0]{'PageText'} =~ s/\r\n\r\n/\r\n/g;

which rectifies the problem but why do i now need to do it?


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[tchatzi]

Does the content of $hlp[0]{'PageText'} includes both lines and they are showing in the same textarea?

What exactly is happening in your 'updSQL' sub?


``The wise man doesn't give the right answers,
he poses the right questions.''
TIMTOWTDI

 

[1DMF]

updSQL & get SQL is as follows..

###############################################
############## Get SQL Routine ################
###############################################

sub getSQL {

#_0 = Table
#_1 = Columns
#_2 = Where
#_3 = Order By

# Build SQL Statement
my $sel = "SELECT $_[1] FROM $_[0] WHERE $_[2]";

# Check for ORDER BY
if($_[3]){$sel .= " ORDER BY $_[3]";}

# Open DB Connection

my $db = new Win32::ODBC("FILEDSN=$DSN;") || die "getSQL Error Connecting: " . Win32::ODBC::Error();

# Run SQL Command
if(!$db->Sql("$sel")) {

    # Set Array for hashes
    my @rs;

	# Loop SQL Record Set
	while($db->FetchRow()){ 
	    my %dt = $db->DataHash();
		$rs[@rs] = \%dt;		
	}

	# Close DB Connection
	$db->Close();

	# Return Record Set Array of Hashes
	@rs;

} 
else{die "Error in getSQL ($sel)" . Win32::ODBC::Error();}

}

##################################################
############## Update SQL Routine ################
##################################################

sub updSQL {

#_0 = Table
#_1 = Values
#_2 = Where

# Set Variables
my ($rowcount);

#Build SQL Statement
my $sel = "UPDATE $_[0] SET $_[1] WHERE $_[2]";

# Open DB Connection
my $db = new Win32::ODBC("FILEDSN=$DSN;") || die "updSQL Error Connecting: " . Win32::ODBC::Error();

# Run SQL Command
if(! $db->Sql("$sel") ) {
	# Set rows updated count 
	$rowcount = $db->RowCount(); 

	#Close DB Connection
	$db->Close(); 
} 
else{die "Error in updSQL ($sel)" . Win32::ODBC::Error();}

# Return number of rows updated
$rowcount;

}

$hlp[0]{'PageText'} is all the data for the text to apear on the web page, it is an 8,000 sized varchar in the SQL DB.

it's very weird i have solved the problem as I said with the substitute , but I just don't understand why it just started to happen. I mean i never used the substitute, then deleted it and had to put it back in, the substitute is brand new code to rectify this bizzare behaviour.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.