PHP Injection in MySQL works when hardcoded

manpreet Best Answer 2 years ago

I have the following SQL query that I can successfully test in Workbench:

        $interviewInfo = $this->db->fetchAll("
            SELECT 
                c.s,
                c.t,
                i.u,
                i.v,
                qa.w,
                i.x
            FROM
                XXX AS i,
                YYY as ip,
                ZZZ AS qa,
                BBB AS c
            WHERE
                c.s = :CompanyId AND ip.r = i.Id
                    AND i.s = c.s
                    AND ip.t = qa.p
                    AND i.h > 0
            ORDER BY i.q DESC
            LIMIT 3", array("CompanyId"=>$companyId));

But with the associative array dependency injection in my SQL statement, it returns an empty array.

I have tried directly inserting CompanyId in place of :CompanyId and then it works.

I have no idea what is going on here because there is:

1. No error from MySQL.
2. The query is correct and I can verify that.
3. I can also verify CompanyId exists and is a valid integer.

manpreet 2 years ago

This is not how PDOStatement::fetchAll works. You must first prepare your query, then execute it, and then you can call fetchAll to get the results. Something like this should work:

$stmt = $this->db-prepare("SELECT
                c.s,
                c.t,
                i.u,
                i.v,
                qa.w,
                i.x
            FROM
                XXX AS i,
                YYY as ip,
                ZZZ AS qa,
                BBB AS c
            WHERE
                c.s = :CompanyId AND ip.r = i.Id
                    AND i.s = c.s
                    AND ip.t = qa.p
                    AND i.h > 0
            ORDER BY i.q DESC
            LIMIT 3");
$stmt->execute(array("CompanyId"=>$companyId));
$interviewInfo = $stmt->fetchAll();

Note that you should also check that the prepare and execute statements succeeded by checking that $stmt is not false and that execute does not return false.