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?

If you're going to do a re-write and you're concerned about basic things like this, why not just use an MVC framework? — minboost, Nov 14 '13 at 17:41
Which MySQL API are you planning to use, PDO or MySQLi? If you use PDO, a general-purpose library should be pretty easy to write, using the `bindValue()` method. — Barmar, Nov 14 '13 at 17:44
Was planning to use MySQLi, but PDO sounds like a better option. — PeteSE4, Nov 15 '13 at 16:37

OlivierH · Answer 1 · 2013-11-14T18:04:01.077

1

You should use PDO.

To make a parametrized query :

$prep = $db->prepare("SELECT * FROM `users` WHERE userid = :id");
$prep->execute(array(":id" => $userid);

It handles all kind of queries possible (insert, select, update statements, even stored procedures calls). Have a look at this page

edited Nov 14 '13 at 18:04

answered Nov 14 '13 at 17:45

OlivierH

3,875
1
19
32

This `userid = ':id'` should be `userid = :id` quoting doesn't need to be done in PDO – Funk Forty Niner Nov 14 '13 at 18:03
I copied/pasted this from another post, I didn't check it. Sorry for that, I edited. – OlivierH Nov 14 '13 at 18:04
Where's the post that you took it from? Curious. – Funk Forty Niner Nov 14 '13 at 18:06
If anything, it should be `WHERE userid =?` that doesn't make sense and how that answer got any upvotes to start with. As per PDO manual http://us2.php.net/manual/en/book.pdo.php – Funk Forty Niner Nov 14 '13 at 18:18
Named parameters (like `:id`) are much more clear to me than question mark parameters. And you don't have to deal with the order of the parameters you give. Have a look at the link i put in my answer. – OlivierH Nov 14 '13 at 18:22

score 0 · Accepted Answer · answered Nov 14 '13 at 17:55

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();
    }
}

}

Parameterised MySQL in PHP as a function

2 Answers2