Using quotation marks in SQL strings and displaying quotes

[BigDoug]

I have a Perl auction script and there is a part that does SQL encoding and when a user inputs quotation marks into the description field of the item they are posting, the display shows the word

instead of displaying the actual quotation marks. I was told by a perl programmer that MySQL cannot display quotationmarks in this field because of a security issue with MySQL. Does that sound right? It does not look right when you have the word

where quotation marks should be and I am sure I have seen Ikonboard BBS display quotes in description fields before. Anybody want to help on this one? Do me justice, O God, and fight my fight against a faithless people; from the deceitful and impious man rescue me.

Psalm 43:1

 

[piti]

i would suggest editing your perl script, so that it replaces every quote with it's escaped sequence
single quote ' -> \'
double quote " -> \"

or there is a dbi method quote(), which returns the input string with properly escaped special characters

 

[cluelessNewbie]

This is really a Perl & not MySQL question but...

If you're doing this thru perl's DBI then what you need to do is prepare you inserts into the table using the "?" place holders. The DBI handles all of the messy quoting for you. Like so...
my $sth = $dbh->prepare("INSERT INTO table1 "
. "(field1, stringfield2) VALUES (?,?)&quot or die $!;

The precesing statement goes before your insert loop.

then in your insert loop you need to

$sth-execute($fld1, $str1);

no fuss no muss

 

[BigDoug]

Okay, thank you first of all for the help (?) although I have more questions now than before. Basically, here is my situation (an please don't think I am looking for anyone to just jump in and start coding for me. I just need help). The programmer of this script says that MySQL cannot accept quotation marks in a fields due to security issues. I said BUNK! Here is the cureent sql_encode sub. Could someone just give me some ideas on how this could be altered to accept quotes as quotes and not spell them out as

? Any help will be greatly appreciated:

#################################################
# Database Functions #
#################################################
sub sql_encode {
my $toencode = $_[0];
$toencode =~ s/\\/\\\\/g;
$toencode =~ s/\'/\\\'/g;
return $toencode;
}


Now I will admit, that I am not 100% sure this is where the encoding takes place, but I am 95% sure. When someone enters quotes in the title field, the output is

instead of " Do me justice, O God, and fight my fight against a faithless people; from the deceitful and impious man rescue me.

Psalm 43:1

 

[BigDoug]

I forgot this code also from another sub:

$form{'title'} =~ s/\>/\&gt\;/g;
$form{'title'} =~ s/\</\&lt\;/g;
$form{'title'}=~ s/\&quot;/\'/g;
Do me justice, O God, and fight my fight against a faithless people; from the deceitful and impious man rescue me.

Psalm 43:1

 

[cluelessNewbie]

The following code was just tested & it worked just fine.

#==============================================================================
# Create an empty 'test' table and prepare to insert into it
#==============================================================================
$dbh->do(&quot;DROP TABLE IF EXISTS test&quot;

$dbh->do(&quot;CREATE TABLE test (&quot;
. &quot;id INT UNSIGNED NOT NULL PRIMARY KEY,&quot;
. &quot;str TEXT)&quot;
) or die $!;

my $sth = $dbh->prepare(&quot;INSERT INTO test (id, str) VALUES (?,?)&quot or die $!;

#==============================================================================
# Create some junk strings
#==============================================================================
$sth->execute(0, &quot;\&quot;\&quot;\&quot;\&quot;&quot or die $!;
$sth->execute(1, &quot;\&quot;\&quot;my quotes are unbalanced\&quot;\&quot;&quot or die $!;
$sth->execute(2, &quot;\&quot;\&quot;but mine are balanced\&quot;\&quot;\&quot;&quot or die $!;

 

[cluelessNewbie]

I should mention that if you replace the literals with variables it still works fine.

so...

$sth->execute($id, $mystring) or die $!;

will still handle the messy chars

 

[BigDoug]

Thank you! Just what I needed to know. Do me justice, O God, and fight my fight against a faithless people; from the deceitful and impious man rescue me.

Psalm 43:1