How to secure a sql statment through a prepared statement

Question

Let's say I execute a query like this:

$assignments = $database->getDataAsArray("SELECT * FROM assignments WHERE userId = '$id'");

The function (getDataAsArray) looks like this:

public function getDataAsArray($myQuery){
        $this->connection = mysqli_connect($this->host, $this->dbUsername, $this->dbPassword, 'portal');
        $statement = $this->connection->prepare($myQuery);
        $statement->execute();
        $result = $statement->get_result();
        $results = array();
        while($line = $result->fetch_array()){
            $results[] = $line;
        }
        return $results;
    }

This is unsafe because I do not parameterize the query with items like :ID

Is it safe if I do it like this:

$id = mysqli_real_escape_string($database->getConnection(), $_SESSION['id']);
        $assignments = $database->getDataAsArray("SELECT * FROM assignments WHERE userId = $id AND closed = 0 AND completed = 0");

In case this it not safe how can I parameterize my queries with one function. For instance I have this query:

"SELECT * FROM assignments WHERE userId = $id AND closed = 0 AND completed = 0"

and I have this query:

"SELECT * FROM state WHERE id='$stateId'"

Both have different number of parameters which need to be added to the query. How can I handle this in one function?

`SELECT * FROM assignments WHERE userId = :id AND closed = 0 AND completed = 0` or `SELECT * FROM assignments WHERE userId = ? AND closed = 0 AND completed = 0` — ctwheels, Aug 22 '17 at 17:32
why don't you just use a prepared statement and be done with it? — Funk Forty Niner, Aug 22 '17 at 17:32
http://php.net/manual/en/mysqli.quickstart.prepared-statements.php or use PDO (https://phpdelusions.net/pdo) — waterloomatt, Aug 22 '17 at 19:07

How to secure a sql statment through a prepared statement

0 Answers0