Monolith-0 has asked for the wisdom of the Perl Monks concerning the following question:

I don't know why it is when I try to INSERT a piece of text with a '?' in it, it ends up turning to 'NULL' in the DB. I'm not even sure if it's a Perl or MySQL database. I have, however, isolated the problem to a single function:

sub Do_SQL{
    my ( $command )=@_;
    
    my $sth;
    eval{
        $sth = $dbh->prepare($command);
    };

    # Check for errors.
    if ($@){
       $dbh->disconnect;
       print "Content-type: text/html\n\n";
       print "An ERROR occurred! $@\n";
       exit;
    }
    else {
       $sth->execute;
    }
    
    return $sth;
}

[download]

For example sakes, I'll say that $command is something like:
INSERT INTO some_tbl (some_text) VALUES("Hello?")

What ends up in the DB (for the value some_text) is "HelloNULL". If I were to enter the exact same line manually into the DB, then it would work fine, and the question make would remain unchanged. I know the problem must be somewhere in that function, but I have no clue what it is.
Could someone please help me?


Also, is there some way the question mark is used as syntax in Perl? I apparently missed that info if it is true.

- Monolith

Replies are listed 'Best First'.
Re: question mark?
by Trimbach (Curate) on Apr 29, 2001 at 05:16 UTC
    The "?" when used with DBI in a PREPARE statement is a placeholder, letting you do cool things like this: 
    my $sth = $dbh-> prepare ("select * from sometable where id=?");
$sth->execute("12345");

    [download]
    When $sth is executed, any values passed to "execute" replace the question marks in the preprared SQL statement in the order the question marks appear. In this case: "select * from sometable where id="12345" is what gets executed. There's lots of good docs on this in the DBI documentation.

    Anyway, that's what your problem is. If you don't want the questions marks to be interpreted incorrectly, use this: 

    $command = $dbh->quote($command);

    [download]
    ...which should happily quote out all the bad stuff for you (question marks aren't the only characters that'll screw you up) and keep the NULLS from getting inserted.

    Gary Blackburn
    Trained Killer

[reply]
[d/l]
[select]

      Ah, thanks.

      Wait... now none of my SQL commands work if I add it after my ( $command )=@_;
      If that's not where I'm supposed to use it, I can't figure out how then.

      - Monolith

[reply]
        Hmmm... on further examination it looks like you're trying to pass an arbitrary SQL statement to your sub and expect it to be executed. I assume you know this is generally a bad idea (why are you using a script to do this if you can access the DB through a shell? And if you can't access the DB via a shell and want to issue commands to the DB remotely, your technique is incredibly insecure...) but nevertheless it's complicating your "quoting" problems. $dbh->quote() is really designed to correctly escape the values in an SQL statement, not an entire statement at once. As things stand you have no way of picking out the values (the things that need quoting) from the commands (the things that should be passed to the DB just as they are.)

        You may need to consult your DB documentation and resort to a series of REGEX's to seek out and properly quote the funky characters in your arbitrary SQL string. $dbh->quote() is normally smart enough to do this for you, but not when you're trying to process an entire string at once.

        Gary Blackburn
        Trained Killer

[reply]
Re: question mark?
by DrZaius (Monk) on Apr 29, 2001 at 21:05 UTC
    As others have noted, ? is the character for place holders in DBI. I recommend reading about them and using them, they make life much better and a far more efficient, especially if you use $dbi->prepare_cached, which won't recompile your sql statement every time.

    You should change your function to look like this: 

    sub Do_SQL {
  my $dbh = shift; # unless you are using a bad global
  my $statement = shift;
  my @values = shift;

  # don't check for errors here as chances are
  # no runtime errors will occur once you are done
  # debuging your code
  my $sth = $dbh->prepare_cached($statement);

  # interopolates your placeholders
  $sth->execute(@values)
    or error_function($dbh->errstr); # you can do 
                                     # the print here
 
}

    So, with this you could do database work with: 

    Do_SQL(
  $dbh, 
  'INSERT INTO some_tbl (some_text) VALUES(?)',
  'Hello?'
);

    Keep in mind that this is very limiting; you can't check the return values of your statements to find out the number of rows affected and so forth. cheers.

[reply]
Re: question mark?
by sachmet (Scribe) on Apr 29, 2001 at 20:08 UTC
    As Trimbach pointed out, creating a subroutine to handle miscellaneous SQL is not always a good idea unless you know what you're doing with it.

    That said, your real problem is that you're using " and not '. In my testing, I have found that text inside 's is immune to being replaced by bind variables. I could be wrong, though. :-)

    INSERT INTO some_tbl (some_text) VALUES('Hello?') works in Oracle, at least, using your subroutine.
[reply]
[d/l]

Back to Seekers of Perl Wisdom