无效的参数编号:未绑定任何参数 [英] Invalid parameter number: no parameters were bound

问题描述

我有一个使用php和mysql的静态聊天应用程序，这是在聊天框中显示消息的代码

I have a static chat application using php and mysql, here's the code to display the messages in the chat box

$sql="SELECT id,msg,time,msg.from,msg.to from msg WHERE (msg.from='".$_SESSION["username"]."' OR msg.from='".$_SESSION["tousermessage"]."') AND (msg.to='".$_SESSION["tousermessage"]."' OR msg.to='".$_SESSION["username"]."') ORDER BY time";
$ex=$conn->prepare($sql);
$ex->execute();

echo "<div class='text-wrap'>";
while($result=$ex->fetch(PDO::FETCH_ASSOC))
{
    if ($result['from']==$_SESSION["username"])
    {
        echo "<div class='message-view' >"; 
        echo "<b class='name'>".$_SESSION["username"]."</b></br>";
        echo "<p class='subject'>".$result["msg"]."</p><p class='time'>".$result["time"]."</p>";
        echo "</div>";
    }
    else
    {
        echo "<div class='message-view' style='background-color: rgb(216, 236, 244);'>";    
        echo "<b class='name'>".$_SESSION["tousermessage"]."</b><br>";
        echo "<p class='subject'>".$result["msg"]."</p><p class='time'>".$result["time"]."</p>";
        echo "</div>";
    }
    if($result['to']==$_SESSION['username'])
    {
        $sqlupdate="UPDATE msg SET readmsg=1 WHERE id=".$result['id']." and msg='".$result["msg"]."'";      
        $ex1=$conn->prepare($sqlupdate);
        $ex1->execute();
    }
}
echo "</div>";

有时会显示异常:

SQLSTATE [HY093]:无效的参数编号:未绑定任何参数.

SQLSTATE[HY093]: Invalid parameter number: no parameters were bound.

消息被插入到味精表中，但是在显示消息时发生错误.错误在发送方或接收方或双方随机发生.我找不到模式或出现这种模式的原因！

The message gets inserted in the msg table but the error occurs during displaying the message. The error occurs randomly at the sender's side or the receiver side or both sides. I cannot find a pattern or the reason why it is occurring!

推荐答案

按照准备好的语句范例，您没有使用bind_param.

You're not using bind_param, as per the prepared statement paradigm.

根据您的选择:

$sql = "SELECT id,msg,time,msg.from,msg.to 
        FROM msg 
        WHERE msg.from IN (?, ?) 
            AND msg.to IN (?, ?)
        ORDER BY time";

$ex = $conn->prepare($sql);
$ex->bind_param("s", $_SESSION["username"]);
$ex->bind_param("s", $_SESSION["tousermessage"]);
$ex->bind_param("s", $_SESSION["username"]);
$ex->bind_param("s", $_SESSION["tousermessage"]);
$ex->execute();

在您的更新中:

$sql = "UPDATE msg 
        SET readmsg=1 
        WHERE id = ? 
            AND msg = ?";

$ex1 = $conn->prepare($sql);
$ex1->bind_param("i", $result['id']);
$ex1->bind_param("s", $result["msg"]);
$ex1->execute();

上面的代码允许您准备好的语句接受参数化字符串格式的参数(使用?"表示参数)，并通过bind_param()方法接受具有类型信息的参数.

The above allows your prepared statement to accept parameters in the parameterized string format (using "?" to represent a param), and to accept params with type information, via the bind_param() method.

这允许数据库引擎在执行查询之前正确地转换和转义参数.

This allows the DB engine to properly cast and escape params prior to executing your query.

如果您不绑定参数，则没有必要使用准备好的语句，这可能就是您收到该警告的原因.

There's no point using prepared statements if you're not binding params, which is probably why you're getting that warning.

顺便提一句，查询的串联(就像您在上面所做的一样)是一个非常不好的习惯-它使您容易了解 SQL注入

On a side note, concatenation of queries (as you're doing above) is a very bad habit - it opens you up to SQL Injection

有关准备好的语句的更多信息，请参阅文档:

See the docs for more info on prepared statements:

http://php.net/manual/en/mysqli-stmt. prepare.php

这篇关于无效的参数编号:未绑定任何参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！