请教perl与mysql连接问题

truemyself

我想将一个文件中的内容添加到数据库中，其中文件格式如下：

1. 
   
2. ......
   
3. 
   
4. body DEAR_SOMETHING                /\bDear (?:IT\W|Internet|candidate|sirs?|madam|investor|travell?er|car shopper|web)\b/i
   
5. describe DEAR_SOMETHING                Contains 'Dear (something)'
   
6. score DEAR_SOMETHING 1.483
   
7. 
   
8. header DATE_IN_PAST_24_48        　　　　　eval:check_for_shifted_date('-48', '-24')
   
9. describe DATE_IN_PAST_24_48        Date: is 24 to 48 hours before Received: date
   
10. score DATE_IN_PAST_24_48 1.955
    
11. 
    
12. ......
    

复制代码

代码如下：

1. 
   
2.     open FH,$rulefile or die "Cant open the rule file";
   
3. 
   
4.     my %formscore,%formregex,%formdescribe,%formtype;
   
5.    
   
6.        while($line=<FH>){   
   
7.         $line=~s/^#.*$//;
   
8.         $line=~s/^\s+//;
   
9.         $line=~s/\s+$//;
   
10.         next unless($line);
    
11.         ($type,$name,$detail)=split (/[\s\t]+/,$line,3);
    
12.         if($type eq "score"){
    
13.             $formscore{$name}=$detail;
    
14.         }elsif($type eq "describe"){
    
15.             $formdescrgibe{$name}=$detail;
    
16.         }else{
    
17.             $formtype{$name}=$type;
    
18.             $formregex{$name}=$detail;
    
19.         }
    
20.     }
    
21.     close FH;
    
22. 
    
23.     @number=keys(%formscore);  
    
24. 
    
25. 
    
26.     foreach my $temp (@number){
    
27.         $sth1 = $dbh->prepare("select count(name) from rule where name = '$temp'") or die "cant prepare!!!!\n";
    
28.         $sth1->execute or die "cant execute!!!!\n";
    
29. 
    
30.         $check = $sth1->fetchrow_array();
    
31. 
    
32.         if($check== 0){  ###insert if not exist
    
33. 
    
34.             $sth2 = $dbh->prepare("insert into rule values($temp,$formtype{$temp},$formdescribe{$temp},'$formregex{$temp}',$formscore{$temp})") or die "cant prepare!!!!\n";
    
35.             $sth2->execute or die "cant execute!!!!\n";
    
36. 
    
37.         }
    
38.     }
    

复制代码

错误信息如下：

1. 
   
2. [Sun Apr 22 03:51:43 2007] [error] [client 202.115.30.166] DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/\\bDear (NULL:IT\\W|Internet|candidate|sirsNULL|madam|investor|travellNULLer|car ' at line 1 at /usr/local/apache/cgi-bin/aone/test.cgi line 94., referer: http://202.115.30.241/cgi-bin/aone/rule.cgi
   
3. [Sun Apr 22 03:51:43 2007] [error] [client 202.115.30.166] cant execute!!!!, referer: http://202.115.30.241/cgi-bin/aone/rule.cgi
   
4. [Sun Apr 22 03:51:43 2007] [error] [client 202.115.30.166] Premature end of script headers: test.cgi, referer: http://202.115.30.241/cgi-bin/aone/rule.cgi
   

复制代码

请朋友们多多指教。

flw

values( ) 里面的东西要带单引号的。

truemyself

感谢flw。

可是还是有点问题

1. 
   
2. [Sun Apr 22 04:28:56 2007] [error] [client 202.115.30.166] DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max_shouting','3','4')',0)' at line 1 at /usr/local/apache/cgi-bin/aone/test.cgi line 95., referer: http://202.115.30.241/cgi-bin/aone/rule.cgi
   
3. [Sun Apr 22 04:28:56 2007] [error] [client 202.115.30.166] cant execute!!!!, referer: http://202.115.30.241/cgi-bin/aone/rule.cgi
   
4. [Sun Apr 22 04:28:56 2007] [error] [client 202.115.30.166] Premature end of script headers: test.cgi, referer: http://202.115.30.241/cgi-bin/aone/rule.cgi
   

复制代码

这个是因为文件中存在eval：

1. 
   
2. body HTML_SHOUTING3                eval:html_range('max_shouting','2','3')
   
3. describe HTML_SHOUTING3                HTML has very strong "shouting" markup
   
4. score HTML_SHOUTING3 0.250
   

复制代码

总是表达式部分出问题，不晓得怎么回事，感觉应该是存在转义字符，我只是想简单的把各字段的值存到数据库中而已。

福瑞哈哥

你先把sql语句打印出来，然后把这个sql语句放到mysql里执行一次看出什么错？然后再做相应修改。

truemyself

感谢福瑞哈哥。

1. 
   
2. $type="body";
   
3. $name="dddd";
   
4. $describ="testdddd";
   
5. $regex="eval:html_range('obfuscation_ratio','.9','1.0')";
   
6. $score=1.251;
   
7. 
   
8. $sentence="insert into rule values('$type','$name','$describ','$regex',$score)";
   
9. print $sentence;
   
10. 
    
11. my $sth = $dbh->prepare(qq{$sentence}) or die "cant prepare!!!!\n";
    
12. $sth->execute or die "cant execute!!!!\n";
    

复制代码

输出如下：

1. 
   
2. DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'obfuscation_ratio','.9','1.0')',1.251)' at line 1 at ./testdb.pl line 55.
   
3. cant execute!!!!
   
4. insert into rule values('body','dddd','testdddd','eval:html_range('obfuscation_ratio','.9','1.0')',1.251)
   

复制代码

对于转义字符再应该怎么办呢？

truemyself

呵呵
知道了
用DBI->quote()来进行转义。



结帖