Parameterised MySQL in PHP as a function

Question

I'm embarking on a complete re-write of an old site I manage and using the opportunity to do as much best practice / tidying of the code as possible. With that in mind I'm keen to move the database calls out of the page rendering code and into a library of common functions that I can reuse - a quasi-MVC model, if you like. However, one of the goals of the re-write is to keep the security as tight as possible, and I suspect the best way to achieve that is with parameterised/parameterized queries.

So assuming what my code wants back is generally going to be a recordset array, is there a way a function could be written to be flexible enough to handle all sorts of incoming SQL queries but still be parameterised?

Answer 1

use this class written by me . Its helpful

class Database {

public $hostname, $dbname, $username, $password, $conn;

function __construct() {
    $this->host_name = "HOST_NAME";
    $this->dbname = "DBNAME";
    $this->username = "USERNAME";
    $this->password = "PASSWORD";
    try {

        $this->conn = new PDO("mysql:host=$this->host_name;dbname=$this->dbname", $this->username, $this->password);
        $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo 'Error: ' . $e->getMessage();
    }
}

function customSelect($sql) {
    try {
         $stmt = $this->conn->prepare($sql);
        $result = $stmt->execute();
        $rows = $stmt->fetchAll(); // assuming $result == true
        return $rows;
    } catch (PDOException $e) {
        echo 'Error: ' . $e->getMessage();
    }
}

function select($tbl, $cond='') {
    $sql = "SELECT * FROM $tbl";
    if ($cond!='') {
        $sql .= " WHERE $cond ";
    }

    try {
         $stmt = $this->conn->prepare($sql);
        $result = $stmt->execute();
        $rows = $stmt->fetchAll(); // assuming $result == true
        return $rows;
    } catch (PDOException $e) {
        echo 'Error: ' . $e->getMessage();
    }
}
function num_rows($rows){
     $n = count($rows);
     return $n;
}

function delete($tbl, $cond='') {
    $sql = "DELETE FROM `$tbl`";
    if ($cond!='') {
        $sql .= " WHERE $cond ";
    }

    try {
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount(); // 1
    } catch (PDOException $e) {
        return 'Error: ' . $e->getMessage();
    }
}

function insert($tbl, $arr) {
    $sql = "INSERT INTO $tbl (`";
    $key = array_keys($arr);
    $val = array_values($arr);
    $sql .= implode("`, `", $key);
    $sql .= "`) VALUES ('";
    $sql .= implode("', '", $val);
    $sql .= "')";

    $sql1="SELECT MAX( id ) FROM  `$tbl`";
    try {

        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $stmt2 = $this->conn->prepare($sql1);
        $stmt2->execute();
        $rows = $stmt2->fetchAll(); // assuming $result == true
        return $rows[0][0];
    } catch (PDOException $e) {
        return 'Error: ' . $e->getMessage();
    }
}

function update($tbl, $arr, $cond) {
    $sql = "UPDATE `$tbl` SET ";
    $fld = array();
    foreach ($arr as $k => $v) {
        $fld[] = "`$k` = '$v'";
    }
    $sql .= implode(", ", $fld);
    $sql .= " WHERE " . $cond;

    try {
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount(); // 1
    } catch (PDOException $e) {
        return 'Error: ' . $e->getMessage();
    }
}

}