CODEWITHSUNDEEP
mysqlperldbi
user3829086
user3829086

Reputation: 363

Escaping latin characters for DB insertion and fetching with perl

I am entering a note value as "£" from html Text area and submitting . and trying to insert into DB using following code, but it is taking some junk value from UI and display also showing junk value ,Ex: If I enter £ from text area , It is coming as £. Please tell me how to fix this issue.

#!/usr/local/perl-5.20.1/bin/perl
use HTML::Entities;
my $str= $cgi->param('note');
$str=HTML::Entities::encode($str);
my $sth = $dbh->prepare("INSERT INTO notes VALUES (null,'FD',?,?)");
$sth->execute('1','2',time(),$str);

my $sth = $dbh->prepare("SELECT ATTuid, noteDate, content FROM notes WHERE requestType = ? AND requestID = ? ORDER BY noteDate ".$order);
$sth->execute('1','2');
if ($sth->rows() > 0) {
while (my @temp = $sth->fetchrow_array()) {
$temp[2] =~ s|'|\\'|g;
$html = '<br/>'.$temp[2];
}

Upvotes: 1

Views: 169

Answers (2)

hd1
hd1

Reputation: 34667

You should ensure MySQL is set to use utf-8 and add the following before prepare:

$dbh->do("SET NAMES 'utf8'");

Hope that helps... Leave a comment if you have further problems.

Per comment, you now need an HTML meta tag within your head block to set the text encoding:

<html>
<head>
<meta charset="UTF-8">
<!-- rest goes here -->
</html>

Best of luck to you.

Upvotes: 2

aleroot
aleroot

Reputation: 72636

It seems a problem related to a possible character set mismatch between the application or the HTML page and the underling database server, What I suggest to you is to go with UTF-8 encoding on both sides.

In the web page (if that is what you have) should begin with : 

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

In my.cnf of MySQL Server :

init_connect = 'SET NAMES utf8'

or from perl : 

$dbh->do( "set names utf8" );

or when you establish the connection : 

my $dbh = DBI->connect(
    "dbi:mysql:dbname=db_name", 
    "db_user", "db_pass",
     {RaiseError => 0, PrintError => 0, mysql_enable_utf8 => 1}
 ) or die "Connect to database failed.";

Upvotes: 4

Related Questions