Hello,
Can anyone tell me how to safely add a ' to a mysql database, the way we have it now, we have the ' stripped out using the following code:
$zipcode =~ s/\s/\'/g;
$zipcode =~ s/\'/ /g;

But I'm wondering how to safely let the users use the single quote as we do get a lot of people that write messages back and forth to other members with things like, don't and won't and couldn't and I'm, so Im wondering if there is someway of doing this that will allow the mysql database to accept the ' when inserting the information, I believe it's okay to have a ' inside of the database once it's there, just not acceptable to actually put it in via a form submital. If we don't strip out the ' then the information never gets written to the database and I assume that's because of mysql and corruption issues with the ' character being as how that's used in routines to search the database.

Any clues?

Harrison



"I've got if's pretty good, but that's about it"

Perl's DBI module has a built in feature for escaping meta-characters. Read the perldoc DBI docs to understand how Perl interacts with a RDBMS.

if $dbh is your database handle you would use:

my $parsed_data = $dbh->quote($data_to_parse);

-- Gordon


s/(\d{2})/chr($1)/ge + print if $_ = '8284703280698276687967';

Try using html codes for single and double quotes:

$zipcode =~s/\"/\&\#034/g;
$zipcode =~s/\'/\&\#039/g;

They get interpreted by the browser back to single and double quotes.
Thanks Conrad

Or, do the proper thing, and use the quote function, as Gordon recommended.



--mark

Installation support is provided via ICQ at UIN# 53788453. I will only respond on that number.

Or use placeholders, they are your friends:

my $query = "INSERT INTO mydata VALUES (?, ?, ?)";
my $sth = $dbh->prepare($query);
$sth->execute($data1, $data2, $data3);

DBI will escape everything for you automagically.

Cheers,

Alex

--
Gossamer Threads Inc.

Im sorry if I sound stupid, but I can not for the life of me figure out how to do what Alex is recommending with what we have now.

What we have now is this.

my($query) = "SELECT * FROM emails where Sender = '$Sender' and Recipient = '$Recipient' and Subject = '$Subject' and Message ='$Message'";
my($sth) = $dbh->prepare($query);
$sth->execute || die("Couldn't exec sth!");
while(@row = $sth->fetchrow) {
$vmessagenumber = $row[0];
}
$sth->finish;

if ($vmessagenumber eq ""){
&sendthemail;
}
}

sub sendthemail{
$query = "INSERT INTO emails values('$messagenumber','$Sender','$Recipient','$date','$Subject','$Message','$status','$method','','','$receipt',UNIX_TIMESTAMP())";
$dbh->do($query);
}


"I've got if's pretty good, but that's about it"

Try:

my $query = 'SELECT * FROM emails WHERE Sender = ? AND Recipient = ? AND Subject = ? AND Message = ?';
my $sth = $dbh->prepare($query);
$sth->execute($Sender, $Recipient, $Subject, $Message);
while ($row = $sth->fetchrow_arrayref) {
$msg_number = $row->[0];
}

Cheers,

Alex

--
Gossamer Threads Inc.

Hi Alex,

I searched the forum for an answer to my problem, and believed that I had found one, but I am unable to get your instructions to work. Here is the snippet which is causing the problems:

# This query will select the info we want.
my $query = qq!'INSERT INTO Topics (Subject, Message, Author, Board_Name, Board_ID, Forum_Name, Forum_ID, Date_Posted, Updated) VALUES (?,?,?,?,?,?,?,?,?)'!;


# Prepare the query.
my $sth = $dbh->prepare($query);

# Execute it.
$sth->execute($tsubject,$Message,$Username,$bname,$bid,$fname,$fid,NOW(),NOW());

$sth->finish();

Any help would be appreciated.

Robert Blackstone
Webmaster of Scato Search
http://www.scato.com

NOW() is a mysql function so you want:

my $query = qq!'INSERT INTO Topics (Subject, Message, Author, Board_Name, Board_ID, Forum_Name, Forum_ID, Date_Posted, Updated) VALUES (?,?,?,?,?,?,?,NOW(),NOW())'!;

# Prepare the query.
my $sth = $dbh->prepare($query);

# Execute it.
$sth->execute($tsubject,$Message,$Username,$bname,$bid,$fname,$fid);

Cheers,

Alex

--
Gossamer Threads Inc.

One warning, if $data_to_parse is undefined, DBI will throw a fatal error (ugh). I also think depends on versions of DBI and DBD::mysql as it doesn't seem to be consistent. So you should do:

$parse_data = $dbh->quote(defined $data_to_parse ? $data_to_parse : '');

which is a mouthful.

Cheers,

Alex

--
Gossamer Threads Inc.

Hi Alex,

It worked like a charm. :)
Thanks again.

Robert Blackstone
Webmaster of Scato Search
http://www.scato.com