2
\$\begingroup\$

I was looking for a PHP class to handle all database operations (MySQL) and came across the following class. Someone please help me telling if this uses Prepared Statements correctly to make my web app safe from SQL injection.

class db extends PDO {
private $error;
private $sql;
private $bind;
private $errorCallbackFunction;
private $errorMsgFormat;

public function __construct($dsn, $user="", $passwd="") {
    $options = array(
        PDO::ATTR_PERSISTENT => true, 
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    );

    try {
        parent::__construct($dsn, $user, $passwd, $options);
    } catch (PDOException $e) {
        $this->error = $e->getMessage();
    }
}

private function debug() {
    if(!empty($this->errorCallbackFunction)) {
        $error = array("Error" => $this->error);
        if(!empty($this->sql))
            $error["SQL Statement"] = $this->sql;
        if(!empty($this->bind))
            $error["Bind Parameters"] = trim(print_r($this->bind, true));

        $backtrace = debug_backtrace();
        if(!empty($backtrace)) {
            foreach($backtrace as $info) {
                if($info["file"] != __FILE__)
                    $error["Backtrace"] = $info["file"] . " at line " . $info["line"];  
            }       
        }

        $msg = "";
        if($this->errorMsgFormat == "html") {
            if(!empty($error["Bind Parameters"]))
                $error["Bind Parameters"] = "<pre>" . $error["Bind Parameters"] . "</pre>";
            $css = trim(file_get_contents(dirname(__FILE__) . "/error.css"));
            $msg .= '<style type="text/css">' . "\n" . $css . "\n</style>";
            $msg .= "\n" . '<div class="db-error">' . "\n\t<h3>SQL Error</h3>";
            foreach($error as $key => $val)
                $msg .= "\n\t<label>" . $key . ":</label>" . $val;
            $msg .= "\n\t</div>\n</div>";
        }
        elseif($this->errorMsgFormat == "text") {
            $msg .= "SQL Error\n" . str_repeat("-", 50);
            foreach($error as $key => $val)
                $msg .= "\n\n$key:\n$val";
        }

        $func = $this->errorCallbackFunction;
        $func($msg);
    }
}

public function delete($table, $where, $bind="") {
    $sql = "DELETE FROM " . $table . " WHERE " . $where . ";";
    $this->run($sql, $bind);
}

private function filter($table, $info) {
    $driver = $this->getAttribute(PDO::ATTR_DRIVER_NAME);
    if($driver == 'sqlite') {
        $sql = "PRAGMA table_info('" . $table . "');";
        $key = "name";
    }
    elseif($driver == 'mysql') {
        $sql = "DESCRIBE " . $table . ";";
        $key = "Field";
    }
    else {  
        $sql = "SELECT column_name FROM information_schema.columns WHERE table_name = '" . $table . "';";
        $key = "column_name";
    }   

    if(false !== ($list = $this->run($sql))) {
        $fields = array();
        foreach($list as $record)
            $fields[] = $record[$key];
        return array_values(array_intersect($fields, array_keys($info)));
    }
    return array();
}

private function cleanup($bind) {
    if(!is_array($bind)) {
        if(!empty($bind))
            $bind = array($bind);
        else
            $bind = array();
    }
    return $bind;
}

public function insert($table, $info) {
    $fields = $this->filter($table, $info);
    $sql = "INSERT INTO " . $table . " (" . implode($fields, ", ") . ") VALUES (:" . implode($fields, ", :") . ");";
    $bind = array();
    foreach($fields as $field)
        $bind[":$field"] = $info[$field];
    return $this->run($sql, $bind);
}

public function run($sql, $bind="") {
    $this->sql = trim($sql);
    $this->bind = $this->cleanup($bind);
    $this->error = "";

    try {
        $pdostmt = $this->prepare($this->sql);
        if($pdostmt->execute($this->bind) !== false) {
            if(preg_match("/^(" . implode("|", array("select", "describe", "pragma")) . ") /i", $this->sql))
                return $pdostmt->fetchAll(PDO::FETCH_ASSOC);
            elseif(preg_match("/^(" . implode("|", array("delete", "insert", "update")) . ") /i", $this->sql))
                return $pdostmt->rowCount();
        }   
    } catch (PDOException $e) {
        $this->error = $e->getMessage();    
        $this->debug();
        return false;
    }
}

public function select($table, $where="", $bind="", $fields="*") {
    $sql = "SELECT " . $fields . " FROM " . $table;
    if(!empty($where))
        $sql .= " WHERE " . $where;
    $sql .= ";";
    return $this->run($sql, $bind);
}

public function setErrorCallbackFunction($errorCallbackFunction, $errorMsgFormat="html") {
    //Variable functions for won't work with language constructs such as echo and print, so these are replaced with print_r.
    if(in_array(strtolower($errorCallbackFunction), array("echo", "print")))
        $errorCallbackFunction = "print_r";

    if(function_exists($errorCallbackFunction)) {
        $this->errorCallbackFunction = $errorCallbackFunction;  
        if(!in_array(strtolower($errorMsgFormat), array("html", "text")))
            $errorMsgFormat = "html";
        $this->errorMsgFormat = $errorMsgFormat;    
    }   
}

public function update($table, $info, $where, $bind="") {
    $fields = $this->filter($table, $info);
    $fieldSize = sizeof($fields);

    $sql = "UPDATE " . $table . " SET ";
    for($f = 0; $f < $fieldSize; ++$f) {
        if($f > 0)
            $sql .= ", ";
        $sql .= $fields[$f] . " = :update_" . $fields[$f]; 
    }
    $sql .= " WHERE " . $where . ";";

    $bind = $this->cleanup($bind);
    foreach($fields as $field)
        $bind[":update_$field"] = $info[$field];

    return $this->run($sql, $bind);
}
}   

And, using the class as follows:

Using Select:

$search = "J";
$bind = array(
":search" => "%$search"
);
$results = $db->select("mytable", "FName LIKE :search", $bind);

Using Update:

$update = array(
    "Age" => 24
);
$fname = "Jane";
$lname = "Doe";
$bind = array(
               ":fname" => $fname,
               ":lname" => $lname
        );
$db->update("mytable", $update, "FName = :fname AND LName = :lname", $bind);

Using Insert:

$insert = array(
    "FName" => "John",
    "LName" => "Doe",
    "Age" => 26,
    "Gender" => "male"
);
$db->insert("mytable", $insert);

Or if you have some another class that you'd like to recommend, I'd be really glad!

\$\endgroup\$
  • \$\begingroup\$ I am using this as well...i was wondering the same thing.... but I know that the PDO class has a $pdo->quote function that you can use to escape stuff... but the class is good as it uses prepare() function \$\endgroup\$ – user15938 Aug 21 '12 at 14:16
1
\$\begingroup\$

Seams to me safe but it's not easy to use. You can create somthing similar to the insert logic for the update (your are naming twice your parameters). In you constructor there is no reason to use that try-catch block and this is true for your debug mathod it is unnecessary with the try-catch block in the run method. Try to have some solution to avoid the usage of the cleanup method becouse it makes to code messy.

Do not use PDO::ATTR_PERSISTENT => true!

\$\endgroup\$
  • \$\begingroup\$ Well, that was really helpful. Thank you very much! \$\endgroup\$ – tripatheea Aug 22 '12 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.