cannot use prepared statements

Question

I have a small MVC framework.

In the db Class:

public static function getInstance() {

        if (!self::$instance) {
            //self::$instance = new PDO("mysql:host=".self::$servername.";dbname=".self::$db.", '".self::$user."', '".self::$pass."'");
            // self::$instance-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            self::$instance = new mysqli(self::$servername, self::$user, self::$pass,self::$db);
            if (self::$instance->connect_error) {
                die("Connection failed: " . self::$instance->connect_error);
            }
        }
        return self::$instance;
    }

In my class:

$sql = "SELECT * FROM rego_details ORDER BY ".$order." ASC";
        $db =  $registry->db;
        $result = $db->query($sql);

I am trying to use prepared statements for security reasons and am using this statement to try it out (I know it is of not much use for this statement, but nevertheless I am using this as it is a simple statement to see if prep. statements work)

I have tried changing it to:

if(!($stmt = $db->prepare('SELECT * FROM rego_details ORDER BY ? ASC'))){
    echo 'prepare failed!';
}

if(!($stmt->bind_param("s", $order))){
    echo 'bind failed!';
}

$stmt->execute();
$result = $stmt->get_result();
var_dump($result);

The result is NULL

also using:

$stmt = $db->prepare('SELECT * FROM rego_details ORDER BY id ASC')

without the bind returns NULL

The unprepared statements work fine. $order is defined properly.

After `$stmt->get_result();` you run a FETCH probably in a while loop — RiggsFolly, Jun 14 '20 at 18:09
See [The PHP Manual](https://www.php.net/manual/en/mysqli-stmt.get-result.php) and read the examples — RiggsFolly, Jun 14 '20 at 18:10
Alternative is to use PDO instead of MySQLi prepared statements. I personally it easier. — The Codesee, Jun 14 '20 at 18:11
@RiggsFolly Even without the fetch the dump should still throw a result right? tried with the fetch and result is the same. — stomperDev, Jun 14 '20 at 18:30
@YourCommonSense: even without the parameterization it does not work. — stomperDev, Jun 14 '20 at 18:44
then there is an error in your query [configure error reporting](https://phpdelusions.net/mysqli/error_reporting) in order to learn which one — Your Common Sense, Jun 14 '20 at 18:57
@YourCommonSense the query is fine as it executes fine without prep statements. adding error reporting gives no other results — stomperDev, Jun 14 '20 at 19:18
then you just confused something. a prepared query either gives you a result or an error. — Your Common Sense, Jun 14 '20 at 19:19
confused I am! I just saw when dumping the available methods for $stmt that get_result is not listed ?? — stomperDev, Jun 14 '20 at 19:42
@YourCommonSense I know that, just that I missed that that was what was being done — RiggsFolly, Jun 14 '20 at 19:50
@YourCommonSense: not sure what you mean here. Why is get_result() not listed as a method for $stmt ? — stomperDev, Jun 14 '20 at 19:52
it could be but in this case it wouldn't silently return null, your program would crash with a fatal error — Your Common Sense, Jun 14 '20 at 19:58
Apparently get_result is only available if Mysqli is installed with mysqlnd. In my case I thin it isn't. That is why I am not getting any results! I tried with binding a result (as that method is avaialble) and that seems to work. I do not want to bind the result however. — stomperDev, Jun 14 '20 at 20:13
@YourCommonSense I just noticed you closed my question an hour ago because it was answered in a different post???? that post is about something totally different! — stomperDev, Jun 14 '20 at 20:16
Yes, because you tried to use a placeholder in order by, which is precisely the other answer about. Stack Overflow only allows one question at a time. Regarding mysqlnd, there are many existing answers as well. — Your Common Sense, Jun 14 '20 at 20:18
@YourCommonSense so just because I use a placeholder for order by you are pointing me to a post about order by?? even though that is totally irrelevant! And your comment about mysqlnd is also not relevant! I found out myself that this is what is causing the issue! No one here pointed me to that. Had I known it was the issue I would not have posted the question in the first place! — stomperDev, Jun 14 '20 at 20:49

cannot use prepared statements

0 Answers0