如何在SQL查询中使用prepared和bound语句

Question

I have been told that my query below is very susceptible to an sql injection - I should be using bound parameters instead,

class search
{

    public $mysqli = null;

    public function __construct($mysqli,$keyword = null)
    {
       $this->mysqli = $mysqli;
    }

    public function get_result($parameter)
    {
        $sql = "
        SELECT *
        FROM root_contacts_cfm
        WHERE root_contacts_cfm.cnt_id = '".$parameter."'
        ORDER BY cnt_id DESC
        ";

        $item = $this->mysqli->fetch_assoc($sql);
        return $item;
    }
}

can I ask - how can I turn this search class with a prepared and bound statement?

I have read some articles online why we should use prepared statements, article 1 article 2

But I still don't have a clue how to improve my query... I tried with this amendment below,

class search
{

    public $mysqli = null;

    public function __construct($mysqli)
    {
       $this->mysqli = $mysqli;
    }

    public function get_result($parameter)
    {
        $sql = "
        SELECT *
        FROM root_contacts_cfm
        WHERE root_contacts_cfm.cnt_id = '?'
        ORDER BY cnt_id DESC
        ";

        $stmt = $this->mysqli->prepare($sql);

        /* bind parameters for markers */
        $stmt->bind_param("s", $parameter);

        /* execute query */
        $stmt->execute();

         /* fetch value */
        return $stmt->fetch();

    }
}

So when I call the search class as an object,

$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);
print_r($output->get_result('1'));

I will get this error,

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of variables doesn't match number of parameters in prepared statement in C:\wamp\www\xxxl\class_database.php on line 487

line 487 refers to $stmt->bind_param("s", $parameter);

Thanks.

Answer 1

Try removing ', which surrounds your ? placeholder.

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
ORDER BY cnt_id DESC
";

In prepared statements whole thing is about specifying type of param when you bind it, not in SQL query - which you did, when wrote '?' . You've said that has to be string, but is not required, when you bind param as a string. Database engine will now how to insert/escape that value.