无法从perl脚本运行MySQL存储过程

I am trying to access MySQL stored procedures from a perl script but I receive this error:

我试图从perl脚本访问MySQL存储过程但我收到此错误：

"can't return a result set in the given context"

“无法在给定的上下文中返回结果集”

As you can see, the procedure below returns two tables of results. Initially, I thought that the problem was a result of this, but the same error occurs if I call stored procs that have only one select statement.

如您所见，下面的过程返回两个结果表。最初，我认为问题是由此导致的，但如果我调用只有一个select语句的存储过程，则会出现同样的错误。

Here is the proc:

这是proc：

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPictureDataForSendMsg`(memIdVal VARCHAR(4))
begin
   select val, size3
   from members
   where memid=memIdVal;

   select id, filename, picname, picsize
   from pictures
   where memid=memIdVal order by id asc; 
end

Which is called by:

这叫做：

$input = 'ABC1';
$dbh = DBI->connect("DBI:mysql:$db:$server;mysql_multi_statements=true", "$user", "$password");
my $result = $dbh->prepare('CALL GetPictureDataForSendMsg($input)') or error($q, "Problem with database call");
$result->execute or error($q, "Problem with database call");

The error is linked to the execute statement. I have the necessary privileges and the newest version of MySQL; the procedure runs perfectly in the command line.

该错误与execute语句相关联。我有必要的权限和MySQL的最新版本;该过程在命令行中运行完美。

What's wrong? Thanks for your help.

怎么了？谢谢你的帮助。

1 个解决方案

#1

You have single quotes around the CALL... statement. Variables are only interpolated in double-quoted strings, not single-quotes strings. Change those to double-quotes like this:

你在CALL ...语句周围有单引号。变量仅以双引号字符串进行插值，而不是单引号字符串。将这些更改为双引号，如下所示：

my $result = $dbh->prepare("CALL GetPictureDataForSendMsg($input)") or error($q, "Problem with database call");

This will interpolate the variable into the string, so you're now sending "CALL GetPictureDataForSendMsg(ABC1)" to the database. That won't work either. The database is going to put whatever you give it as a parameter directly into the SQL string. If you quote ABC1 like this 'ABC1' then it will see that as a string; otherwise it will think that ABC1 is the name of a column - which it isn't.

这会将变量插入到字符串中，因此您现在将“CALL GetPictureDataForSendMsg（ABC1）”发送到数据库。这也行不通。数据库将把你提供的任何内容作为参数直接放入SQL字符串中。如果你引用ABC1这样的'ABC1'那么它会看到它作为一个字符串;否则它会认为ABC1是列的名称 - 它不是。

So, this line will work:

所以，这一行将起作用：

my $result = $dbh->prepare("CALL GetPictureDataForSendMsg('$input')") or error($q, "Problem with database call");

But there's a problem with this line too. What if the string represented by $input contains a single-quote? It will break. This is how SQL injection attacks happen. Instead you should use the DBI quote function, like this:

但这条线路也存在问题。如果$ input表示的字符串包含单引号怎么办？它会破裂。这就是SQL注入攻击的发生方式。相反，你应该使用DBI引用函数，如下所示：

my $result = $dbh->prepare('CALL GetPictureDataForSendMsg('.$dbh->quote($input).')') or error($q, "Problem with database call");

I tested this on my MySQL instance (Server version: 5.1.49-1ubuntu8.1 (Ubuntu)) and it worked.

我在我的MySQL实例（服务器版本：5.1.49-1ubuntu8.1（Ubuntu））上测试了这个并且它工作了。

#1