Tag Archives: mysql

PHP PDO MySQL Blob

Posted on by
Reply

Read PHP MySQL BLOB today to learn how to work with BLOBs. Basically:

public function insertBlob($filePath,$mime){
    $blob = fopen($filePath,'rb');
 
    $sql = "INSERT INTO files(mime,data) VALUES(:mime,:data)";
    $stmt = $this->conn->prepare($sql);
 
    $stmt->bindParam(':mime',$mime);
    $stmt->bindParam(':data',$blob,PDO::PARAM_LOB);
 
    return $stmt->execute();
}

function updateBlob($id,$filePath,$mime) {
 
    $blob = fopen($filePath,'rb');
 
    $sql = "UPDATE files
            SET mime = :mime,
            data = :data
            WHERE id = :id";
 
    $stmt = $this->conn->prepare($sql);
 
    $stmt->bindParam(':mime',$mime);
    $stmt->bindParam(':data',$blob,PDO::PARAM_LOB);
    $stmt->bindParam(':id',$id);
 
    return $stmt->execute();
 
}

public function selectBlob($id) {
 
    $sql = "SELECT mime,
            data
        FROM files
        WHERE id = :id";
 
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(array(":id" => $id));
    $stmt->bindColumn(1, $mime);
    $stmt->bindColumn(2, $data, PDO::PARAM_LOB);
 
    $stmt->fetch(PDO::FETCH_BOUND);
 
    return array("mime" => $mime,
             "data" => $data);
 
}

Escaping SQL values in PHP without using mysql_real_escape_string

Posted on by
Reply

Found this article which has a handy ‘escape’ function:

// replace any non-ascii character with its hex code.
function escape($value) {
    $return = '';
    for($i = 0; $i < strlen($value); ++$i) {
        $char = $value[$i];
        $ord = ord($char);
        if($char !== "'" && $char !== "\"" && $char !== '\\' && $ord >= 32 && $ord <= 126)
            $return .= $char;
        else
            $return .= '\\x' . dechex($ord);
    }
    return $return;
}