Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

mySQL Query Problem

by kidd (Curate)
on Feb 11, 2003 at 16:51 UTC ( #234452=perlquestion: print w/replies, xml ) Need Help??
kidd has asked for the wisdom of the Perl Monks concerning the following question:

Hello... Im having some problems with adding info to mySQL database.. Here is the code that inputs the information to the database:

$dbh = BaboonDB->connect(); $sth = $dbh->prepare('INSERT INTO anuncios (usuario,titulo,mensaje, +categoria,estado,pais,imagen,image_align,expiracion,firma) VALUES(?,? +,?,?,?,?,?,?,?,?)') or die("Couldn't prepare statement: " . $dbh->err +str); $sth->execute($user,$title,$msg,$cat,$state,$country,$img,'left',$e +xpiration,$name) or die("Couldn't execute statement: " . $sth->errstr +); $dbh->disconnect;

The problem resides in the variable $msg that has this value: tengo todo por aprender ¿quien quiere  enseñarme?

The problem I figured out is the ? that conflicts with the prepare statement.

I tried to escape it with something likes this: $msg = $dbh->quote($msg);

But without success...can anyone tell me how can I safely escape the ?

By the way...Im using the DBD::mySQL module...


Replies are listed 'Best First'.
Re: mySQL Query Problem
by gmax (Abbot) on Feb 11, 2003 at 21:01 UTC
    I could not replicate your problem.
    I am using Perl 5.6.1, DBI 1.30 and DBD::mysql 2.1018
    #!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect("DBI:mysql:test;host=localhost;" ."mysql_read_default_file=$ENV{HOME}/.my.cnf", undef,undef,{RaiseError => 1}); $dbh->do(qq{drop table if exists testq}); $dbh->do(qq{create table testq (i int not null, c varchar(50))}); my $sth= $dbh->prepare(qq{insert into testq values (?, ?)}); my $i = 10; my $msg = 'tengo todo por aprender ¿quien quiere enseñarme?'; $sth->execute($i, $msg); my @results = @{ $dbh->selectall_arrayref(qq{select * from testq})}; print "@$_\n" for @results; $dbh->disconnect();
    This code accepts the variable, inserts it into the table and retrieves the values correctly.
    Can you make a simple test case, with appropriate values, so that we can evaluate what is wrong?
    Use the above code as a template.

    update 12-Feb-2003 17:50 CET
    Tested the above code with DBD::mysqlPP and it works as well.
    Just replace the connection string with the following.
    my $dbh = DBI->connect("DBI:mysqlPP:test;host=localhost;", "gmax","mysecretpwd",{RaiseError => 1});
    Therefore, the placeholder mechanism is not the culprit.

    update 2
    The mistake is in the DBD::mysqlPP driver. Check the Changelog. Versions prior to 0.04 are affected by a quoting bug, which was corrected 20 days ago!
    So, after all, the culprit was the placeholder mechanism. Just update your copy of DBD::mysqlPP
    _ _ _ _ (_|| | |(_|>< _|

      Im sorry I made a major mistake...the module Im using is DBD::mysqlPP, the reason Im using the module is that my client's hosting service refuses to install the DBD::mysql module...

      Anyway I wanted to say thanks for your answers and sorry for not been clear since the begining...

      I tried to look for a new version of this module but it seems that I already have the newest one..I hope someone can help me sort this out...


Re: mySQL Query Problem
by adrianh (Chancellor) on Feb 11, 2003 at 17:02 UTC

    What error message is DBI giving you? Placeholder arguments are automatically escaped so I doubt that it's the "?" in $msg that's causing the problem.

Re: mySQL Query Problem
by bart (Canon) on Feb 11, 2003 at 17:53 UTC
    I'm sure I've seen this mentioned before, not too long ago, on the DBI-users mailing list. I think this must be a bug in the DBD driver. I've tried searching though the archives, but no luck: I don't really have many valuable keywords to search for. I don't know if this got fixed in the latest driver...

    Your best bet would be to upgrade to a recent version of DBD::mysql, and if that doesn't help, maybe ask about the status yourself on the mailing list. There are many smart peoplen, even quite a few of the developers who (co-)wrote DBI or one of the DBD::* drivers, are on that list. If anybody can give you an answer, they are it.

Re: mySQL Query Problem
by jammin (Novice) on Feb 11, 2003 at 17:57 UTC
    What is the error message that you get as it's very unlikely to be the ? causing the problem as the execute statement should escape it for you.
Re: mySQL Query Problem
by Jazz (Curate) on Feb 13, 2003 at 02:42 UTC
    As others have said, posting the error that mysqlPP is giving you would help identify the error. It'll let you know if it's dying on the prepare or execute instead of assuming it's on the prepare (which it can't be, because it's the execute that's trying to insert the values of your variables). If memory serves, passing values through the execute statement automatically (and correctly) quotes your variables.

    Are you checking to make sure that each of your variables ($user,$title,$msg,$cat,$state,$country,$img,'left',$expiration,$name) have values and that each of those values match the type of data that the database is expecting? If one or more of them are undefined, you're binding 10 fields to less than 10 variables and mysql doesn't like that. Also, if a field is defined as an integer, but receives varchar, mysql doesn't like that either.

    If this doesn't help, please post the dbi error message.


      Here is a test I created to see the problem...considering this code:

      #!/usr/bin/perl -w use strict; use CGI::Carp qw(fatalsToBrowser); use BaboonDB; my $user = "user\"; my $name = "kidd"; my $place = "CANCUN%%MEXICO"; my $title = "A test to see the problem"; my $img = "none"; my $msg = "This is a test with a question, how are you?"; my $cat = "probl"; $user = lc($user); if($img =~ /^\s*$/){ $img = "none"; } $place = lc($place); my($state,$country) = split("%%", $place); #split te place $country =~ s/usa/estados unidos/g; $country = "\u$country"; $country =~ s/\s+(\w)/ \u$1/gi; $country =~ s/Ñ/ñ/gi; $state = "\u$state"; $state =~ s/\s+(\w)/ \u$1/gi; my($dbh,$sth,@data,$actual); $dbh = BaboonDB->connect(); my $interval = "12"; #First we take the current date $sth = $dbh->prepare('SELECT CURRENT_DATE') or die("Couldn't prepar +e statement: " . $dbh->errstr); $sth->execute() or die("Couldn't execute statement: " . $sth->errst +r); $actual = $sth->fetchrow_array(); $sth->finish; #Now we add the date for the expiration $sth = $dbh->prepare('SELECT DATE_ADD("?", INTERVAL ? MONTH)') or d +ie("Couldn't prepare statement: " . $dbh->errstr); $sth->execute($actual,$interval) or die("Couldn't execute statement +: " . $sth->errstr); my $expiration = $sth->fetchrow_array(); $sth->finish; # $msg = $dbh->quote($msg); $sth = $dbh->prepare('INSERT INTO anuncios (usuario,titulo,mensaje, +categoria,estado,pais,imagen,image_align,expiracion,firma) VALUES(?,? +,?,?,?,?,?,?,?,?)') or die("Couldn't prepare statement: " . $dbh->err +str); $sth->execute($user,$title,$dbh->quote($msg),$cat,$state,$country,$ +img,'left',$expiration,$name) or die("Couldn't execute statement: " . + $sth->errstr); $sth->finish; $dbh->disconnect; exit(1);

      I get this error:

      DBD::mysqlPP::st execute failed: You have an error in your SQL syntax +near 'probl'''','Cancun','Mexico','none','left','2004-02-13','kidd',? +)' at line 1 at C:\WIN98\TEMP\DZPRLTMP.PL line 56.

      But if a get the ? out of the $msg variable the script runs fine...

      Im using the DBD::mysqlPP module...


        What version of DBD::mysqlPP are you using? There's a bug in versions earlier than 0.04 that might be causing your placeholder problem. See change log for details.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://234452]
Approved by broquaint
and dust plays in a shaft of sunlight...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (2)
As of 2017-04-30 05:41 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (534 votes). Check out past polls.