mysqli prepared statement without binding parameters still secure?

Question

it's my first post here and I'm also relatively new to the world of programming. To make it short, while concluding my ecommerce site, developed with php, mysql and javascript/jquery, I've found out that the way I was building all my queries (with mysql_connect) could lead to mysql injections and a lot of other nasty things.

Trying to 'convert' everything to the more secure mysqli-prepared statements way, I've written a class containing all the methods I needed to retrieve informations from my database, but the way I'm not sure about the way I'm making the queries.

Some code: The class constructor

class Database {

    private $DBH;

    public function __construct() {
        $this->DBH = new mysqli(WEB_SERVER, WEB_USER, WEB_PASS, WEB_NAME);
        if ($this->DBH->connect_errno) {
            return "Failed to connect to MySQL: (" . $this->DBH->connect_errno . ") " . $this->DBH->connect_error;
            exit();
        }
    }
    ...
}

...and a query example:

public function get_record_by_param($record, $param, $value) {
    $stmt = $this->getDBH()->stmt_init();

    $query = "SELECT * ";
    $query .= "FROM {$record} ";
    $query .= "WHERE {$param} = {$value} LIMIT 1";

    if($stmt->prepare($query)){
        return $this->execute_simpleAssoc($stmt);
    } else {
    return "Prepare failed: (" . $this->getDBH()->errno . ") " . $this->getDBH()->error;
    }
}

Using a query like this is still secure? Or do I necessarily need to use the bind_param method?

Hoping I've not been confusing, thank you for any advice.

Answer 1

Nope, it's not secure. mysqli can not parse what in your query string is (potentially vulnerable) data, and what is not.

If you want your data escaped, you will need to bind each parameter separately.

Bound parameters work for data only, however. For the column name, you will need to apply whitelisting as pointed out by @Michael in his comment. There is no way to automatically escape column/table names.

This applies to all database layers, including PDO. If you feed the layer a full query string, the data in it will not be magically escaped.