db class: how can I avoid sql injection?

Question

I am new to OOP in PHP and started using a DB class. I am wondering how can I avoid SQL injection. In procedural PHP I always do $db->real_escape_string, which does not work here.

DB.class.php

class DB {    
    protected $db_name = '';
    protected $db_user = '';
    protected $db_pass = '';
    protected $db_host = '';
    protected $connection;

    public function connect() {
        $this->connection = mysqli_connect($this->db_host, $this->db_user, $this->db_pass);
        mysqli_select_db($this->connection, $this->db_name);

        return true;
    }

    public function processRowSet($rowSet, $singleRow = false) {
        $resultArray = array();
        while ($row = mysqli_fetch_assoc($rowSet)) {
            array_push($resultArray, $row);
        }

        if ($singleRow === true)
            return $resultArray[0];

        return $resultArray;
    }

    public function insert($data, $table) {
        $columns = "";
        $values = "";

        foreach ($data as $column => $value) {
            $columns .= ($columns == "") ? "" : ", ";
            $columns .= $column;
            $values .= ($values == "") ? "" : ", ";
            $values .= $value;
        }

        $sql = "insert into $table ($columns) values ($values)";

        mysqli_query($this->connection, $sql) or die(mysqli_error($this->connection));

        //return the ID of the user in the database.
        return mysqli_insert_id($this->connection);
    }
}

One example of usage comes here:

insert-entry.php

require_once 'db.php';

$headline = $_POST['headline'];
$description = $_POST['description'];

$data = array(
        'headline' => $headline,
        'description' => $description
    );

$db->insert($data, 'entries');´

What adjustment do I need to make where in order to be sure that SQL injection is avoided?

Ever heard of 'mysqli_escape_string', if not. Google it, if you did, use it. ^^ — Tosfera, Mar 12 '15 at 10:10
to avoid sql injections, you should use prepared statements. — AbcAeffchen, Mar 12 '15 at 10:27
@Tosfera the OP already stated he has used `real_escape_string` . Prepared statements are the way to go here, `real_escape_string` is at best a stopgap measure and can be overcome relatively easily. — Martin, Mar 12 '15 at 10:52
I'm blind, I guess. Didn't see that he used the escape before. But yet, I have to agree. prepared statements are the best way to solve these problems. — Tosfera, Mar 12 '15 at 11:22

Martin · Accepted Answer · 2015-03-12T11:01:27.687

Doro, you need to use prepared statements, which means you do not directly enter data into the SQL string, instead looking something like:

MySQLi

UPDATE table SET value1 = ? , value2 = ? WHERE value3 = ? LIMIT 1

PDO

 UPDATE table SET value1 = :val1 , value2 = :val2 WHERE value3 = :strVal3 LIMIT 1

You then pass the values in order, in a bind_param function (with MySQli, the syntax is slightly different with PDO) which cleans the SQL injection risks from the values before inserting them. The values replace the ? placeholders. PDO uses slightly different referenced placeholders of the style :referencedString rather than MySQLi's simple ?. There are two types of Prepared statements you can use, MySQLi and PDO, MySQLi is easier to migrate to, PDO is slightly more logical (and short winded) in its syntax.

Research MySQL Prepared Statements as well as reading the link provided by Clive.

db class: how can I avoid sql injection?

1 Answers1