costas has asked for the wisdom of the Perl Monks concerning the following question:

i am writing FTP errors to a database but am aencountering problems if the ftp error contains a quote. 
$message="Couldn't connect: Net::FTP: Timeout ";

my $dbh = db_connect();
    
    my $sql = qq{ INSERT INTO tblErrorLog (clientID, streamID, errorDe
+tails) VALUES ($clientID, $streamID, '$message')};
    #db_execute_query($dbh, $sql);
        
    my $sth = $dbh->prepare( $sql );
    $sth->execute();
    $sth->finish();
            
    db_disconnect($dbh);

[download]
As you can see the $message contains a "'". I have used the qq() operator but I still get an error unless i remove the single quote from within. The error i get is 
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]COUNT
+ field incorrect or syntax error (SQL-07001)(DBD: st_execute/SQLExecu
+te err=-1) at wmsdb.pl line 73. INSERT INTO tblErrorLog (clientID, st
+reamID, errorDetails) VALUES (1, 8, 'Couldn't connect: Net::FTP: Time
+out ')

[download]
Can anyone help?

Title edit by tye

Replies are listed 'Best First'.
Re: trouble using the qq operator
by derby (Abbot) on Apr 08, 2002 at 12:48 UTC
    use the DBI quote method: 

    my $message = $dbh->quote( "Couldn't connect: Net::FTP: Timeout" );
...

    [download]

    -derby

[reply]
[d/l]
Re: trouble using the qq operator
by mattriff (Chaplain) on Apr 08, 2002 at 12:55 UTC
    ++ to derby.

    In the spirit of TIMTOWTDI, here's another way to do the same thing: 

    my $sql = "INSERT INTO tblErrorLog (clientID, streamID, errorDetails) 
+VALUES (?,?,?)";
         
my $sth = $dbh->prepare($sql);
$sth->execute($clientID,$streamID,$message);

    [download]

    When you use placeholders in this manner, all the values are automatically quoted by DBI.

    - Matt Riffle

[reply]
[d/l]
Re: trouble using the qq operator
by perlplexer (Hermit) on Apr 08, 2002 at 12:56 UTC
    I'd let DBI take care of the quoting 
    my $message = "Couldn't connect: Net::FTP: Timeout ";
my $dbh = db_connect();
my $sql = qq{ INSERT INTO tblErrorLog (clientID, streamID, errorDetail
+s) VALUES (?, ?, ?)};
my $sth = $dbh->prepare( $sql );
$sth->execute($clientID, $streamID, $message);
$sth->finish();
db_disconnect($dbh);

    [download]

    --perlplexer
[reply]
[d/l]
Re: trouble using the qq operator
by Rich36 (Chaplain) on Apr 08, 2002 at 13:25 UTC

    Just to throw out another option, you could also use the quotemeta function on your string. 

    quotemeta EXPR 
quotemeta

Returns the value of EXPR with all non-"word" characters backslashed. 
+(That is, all characters not matching /[A-Za-z_0-9]/ will be preceded
+ by a backslash in the returned string, regardless of any locale sett
+ings.) This is the internal function implementing the \Q escape in do
+uble-quoted strings.

If EXPR is omitted, uses $_.

    [download]

    Rich36
    There's more than one way to screw it up...
[reply]
[d/l]
      It's probably good to encourage use of quote function from DBI, because quotemeta function does not know anything about a particular database server requirements for quoting, while DBI::quote is an encapsulation and is able to quote just what needs to be quoted.
[reply]
        Very good point... That could definitely cause problems with certain databases.
        Rich36
        There's more than one way to screw it up...
[reply]

Back to Seekers of Perl Wisdom