php - cs50 pset7 php查询函数where子句中的未知列

Question

不知道为什么会说“YHOO”是 where 子句中的未知列。我的投资组合表中有一个名为“符号”的列

$id = 6;
$symbol = "YHOO";
$shares_query = query("SELECT shares FROM portfolio WHERE id = $id AND symbol = $symbol");

查询功能由cs50工作人员定义

function query(/* $sql [, ... ] */)
{
    // SQL statement
    $sql = func_get_arg(0);

    // parameters, if any
    $parameters = array_slice(func_get_args(), 1);

    // try to connect to database
    static $handle;
    if (!isset($handle))
    {
        try
        {
            // connect to database
            $handle = new PDO("mysql:dbname=" . DATABASE . ";host=" . SERVER, USERNAME, PASSWORD);

            // ensure that PDO::prepare returns false when passed invalid SQL
            $handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 
        }
        catch (Exception $e)
        {
            // trigger (big, orange) error
            trigger_error($e->getMessage(), E_USER_ERROR);
            exit;
        }
    }

    // prepare SQL statement
    $statement = $handle->prepare($sql);
    if ($statement === false)
    {
        // trigger (big, orange) error
        trigger_error($handle->errorInfo()[2], E_USER_ERROR);
        exit;
    }

    // execute SQL statement
    $results = $statement->execute($parameters);

    // return result set's rows, if any
    if ($results !== false)
    {
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }
    else
    {
        return false;
    }
}

像我期望的那样进行查询解析转储 SELECT Shares FROM Portfolio WHERE id = 6 AND symbol = YHOO

Answer 1

您应该更改代码，以便转义输入，正如评论中已经提到的那样。

要使代码正常工作，您需要在输入周围添加 '，如下所示:

$id = 6;
$symbol = "'YHOO'";
$shares_query = query("SELECT shares FROM portfolio WHERE id = $id AND symbol = $symbol");

编辑:检查您的查询函数后，我认为这是解决问题的正确方法:

$id = 6;
$symbol = "YHOO";
$shares_query = query("SELECT shares FROM portfolio WHERE id = ? AND symbol = ?", $id, $symbol);