问题与DBD和mysql在perl

Question

请我有问题跟踪这个代码的问题，我已经尝试了几个小时。它给了我错误DBD::mysql::st fetchrow_hashref失败:fetch()没有execute()在第15行

        sub Split_Into_Words
        {
            #### Connection parameters ############################
            my $dsn =  "dbi:mysql:malware:localhost:3306";
            my $user = 'root';
            my $passwd = 'sxxxs';
            ########################################################
            my $domain ;
            my $countDir = 0 ;
            my $file = shift ;
            my $labelID  =  (split(/[.]/ , $file))[1] ; ### Split and get the middle value since format is temporay.
            #### Query String ############################################################################
             my $InsertIntoHostTable_QS  = "INSERT INTO TB_host(HostName  , UrlID , ExtID) Values (? , ? , ? ) ; ";
             my $InsertIntoDomainTable_QS = "INSERT IGNORE INTO  TB_Domain(Domain) values (?) ;" ;
             my $InsertIntoArgVal_QS = "INSERT INTO TB_Arg_Value(Arg, URL_ID)  VALUES (?  , ? ) ; " ; 
             my $InsertIntoDirectory_QS = "INSERT INTO TB_Directory(DIRNAME , DEPTH , URLID) VALUES (? , ? , ? )" ;
             my $InsertIntoExtension_QS = "INSERT IGNORE INTO TB_Extension (Extension) values ( ? ) ; ";
             my $InsertIntoExtensionNULL_QS =   "INSERT IGNORE INTO TB_Extension (ID , Extension) values (? , ? )  ; ";
             my $SelectString  = " Select URL , ID  from TB_URL where LabelID = '"  .  $labelID."';";
             my $InsertIntoFileName_QS  = "INSERT IGNORE INTO TB_FileName( filename)  VALUES (?) ; " ; 
             ###################################################################################################
             my $DBIConnect = DBI->connect($dsn , $user , $passwd) or die("Cannot connect to datadbase  $DBI::errstrn");   

            print ("Splitting Into Words n");

            ######Initialization of a default DB value #################
            my $sth =  $DBIConnect->prepare( $InsertIntoExtensionNULL_QS);
                    $sth->execute(1 , 'null') or die("Error Executing the Insertion" . $sth->errstr );
                    $sth->finish();
            #############################################################
            $sth =  $DBIConnect ->prepare($SelectString);
            sleep(10);
            open (FH , '<' , $file); # Open file to be read from disk
            my $i = 0;
            $sth->execute() or die("Error Executing the Insertion" . $sth->errstr );
   ->line 15        while(my $hash_ref = $sth->fetchrow_hashref )
            {
                    my $extensionID = "1";
                    my $intialURL =  $hash_ref->{URL} ;
                my $initialID = $hash_ref->{ID};
    }
    }

Answer 1

我不确定这是否是问题，但您可能不需要在插入后完成。来自DBI文档:

表示不再从该语句句柄中获取数据在它被再次执行或销毁之前。几乎可以肯定不需要调用这个方法。

将调用添加到获取所有行的finish after循环中是常见的不要犯错，它会掩盖真正的问题，比如未捕获的fetch错误。

如果这是问题所在，您可能需要为select调用创建第二个语句处理程序。

Answer 2

除了恼人的长SQL变量名，$SelectString应该包含一个"?"，以防$labelID包含可能破坏查询或导致注入的内容。

prepare()并不一定需要一个"?"，但是如果execute有参数，那么在查询字符串中必须有一个匹配数量的"?"。

第一个$sth->finish()是不需要的，因为查询是一个插入，不返回任何行。

第二个'die'应该是"Error doing query"，因为它在执行$SelectString

注:SQL惯例是全部大写，为了更安全，将字段名用反引号括起来。查询不以分号结束。还要注意，"my"变量是大括号之间{}的局部变量，因此while循环中的my变量在之后将不可用。

建议格式如下:

sub Split_Into_Words {
    #### Connection parameters ############################
    my $dsn =  "dbi:mysql:malware:localhost:3306";
    my $user = 'root';
    my $passwd = 'sxxxs';
    ########################################################
    my $domain ;
    my $countDir = 0 ;
    my $file = shift ;
    my $labelID  =  (split(/[.]/ , $file))[1] ; ### Split and get the middle value since format is temporary.
    #### Query String ############################################################################
    my $InsertIntoHostTable_QS    = "INSERT INTO `TB_host` (`HostName`,`UrlID`,`ExtID`) VALUES (?,?,?)";
    my $InsertIntoDomainTable_QS  = "INSERT IGNORE INTO `TB_Domain` (`Domain`) VALUES (?)";
    my $InsertIntoArgVal_QS       = "INSERT INTO `TB_Arg_Value` (`Arg`,`URL_ID`) VALUES (?,?)";.
    my $InsertIntoDirectory_QS    = "INSERT INTO `TB_Directory` (`DIRNAME`,`DEPTH`,`URLID`) VALUES (?,?,?)";
    my $InsertIntoExtension_QS    = "INSERT IGNORE INTO `TB_Extension` (`Extension`) VALUES (?)";
    my $InsertIntoExtensionNULL_QS= "INSERT IGNORE INTO `TB_Extension` (`ID`,`Extension`) VALUES (?,?)";
    my $SelectString              = "SELECT `URL`,`ID` FROM `TB_URL` WHERE `LabelID`=?";
    my $InsertIntoFileName_QS     = "INSERT IGNORE INTO `TB_FileName` (`filename`) VALUES (?)";
    ###################################################################################################
    my $DBIConnect = DBI->connect($dsn , $user , $passwd) or die("Cannot connect to datadbase  $DBI::errstrn");
    print ("Splitting Into Words n");
    ######Initialization of a default DB value #################
    my $sth =  $DBIConnect->prepare( $InsertIntoExtensionNULL_QS);
    $sth->execute(1 , 'null') or die("Error executing the Insertion: " . $sth->errstr );
    # $sth->finish(); # not needed because it's an insert
    #############################################################
    $sth =  $DBIConnect->prepare($SelectString);
    sleep(10);
    open (FH , "<$file"); # Open file to be read from disk
    my $i = 0;
    $sth->execute($labelID) or die("Error executing query: " . $sth->errstr );
    while(my $hash_ref = $sth->fetchrow_hashref ) {
        my $extensionID = "1";
        my $intialURL = $hash_ref->{URL};
        my $initialID = $hash_ref->{ID};
    }