Gravando arquivos em BD usando PDO do PHP

Artigo que explica como gravar arquivos em colunas de tabelas de bancos de dados de forma segura através do PDO do PHP.

escrevendo no HD
Introdução

Existem duas formas mais convencionais de se armazenar arquivos em sistemas de informação: gravando em um diretório reservado ou gravando no banco de dados. Gravando em um diretório, existem alguns riscos como o arquivo poder ser acessado por usuários do servidor, ou o arquivo ser acessado por qualquer pessoa da Web se ficar em um diretório visível na Web. Guardando o arquivo em BD, existe o inconveniente do BD poder ficar com um tamanho muito grande, embora ofereça uma segurança adicional. Neste artigo veremos como gravar e recuperar arquivos do BD usando PDO. Para mais detalhes sobre PDO, leia antes o artigo PDO.


Criação da Tabela do BD

Antes de tudo, é preciso criar uma tabela no BD que terá um ou mais campos (colunas) que armazenarão o arquivo propriamente dito. O campo mais indicado para armazenar este tipo de informação é chamado "BLOB". No MySQL, por exemplo, existem as variações: TINYBLOB, BLOB, MEDIUMBLOB e LARGEBLOB (cada um com uma capacidade diferente, aproximadamente, 256B, 64KB, 16MB e 4GB respectivamente). Outra informação importante de ser armazenada no BD é o tipo de arquivo, de preferencia o mime-type. Opcionalmente, guarda-se também um nome de arquivo. Exemplo:

CREATE TABLE arquivos (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(128) NOT NULL DEFAULT '',
  mimetype VARCHAR(60) NOT NULL DEFAULT 'application/octet-stream',
  conteudo BLOB NOT NULL
);

Inserindo o arquivo no BD

Para inserir o arquivo no BD, ele precisa ter vindo de algum lugar. Pode ser via Upload, pode ser um arquivo que estava no servidor, pode ser um arquivo gerado dinamicamente e armazenado em uma variável, ou pode ser um arquivo que está em uma variável. Em todos os casos, será necessário gerar um handle do arquivo, ou seja, ele precisa estar no servidor para gerar um ponteiro de arquivo (com fopen).

Se o arquivo veio por Upload sem erros, ele pode está numa pasta temporária do servidor, e o handle de arquivo pode ser criado assim:

$handle = fopen($_FILE['arquivo']['tmp_name'], 'rb');

E o mimetype e o nome do arquivo podem ser obtidos assim:

$mimetype = $_FILE['arquivo']['type'];
$nome = basename($_FILE['arquivo']['name']);

Se o arquivo já estava no servidor, basta abrir o hanle passando o caminho ao arquivo:

$caminho = '/caminho/ate/o/arquivo';
$handle = fopen($caminho, 'rb');

E se o arquivo está em uma variável, pode ser gerado um handle assim:

$variavel = 'exemplo';     // conteudo do arquivo
$mimetype = 'text/plain';  // mime-type do arquivo

$handle = tmpfile();
fwrite($handle, $variavel);

Com o handle montado, basta montar a SQL de insert e executá-la:

// Montando o handle do arquivo
$handle = ...
$mimetype = ...
$nome = ...

// Preparando o INSERT
$pdo = new PDO(...);
$stmt = $pdo->prepare('INSERT INTO arquivos (nome, mimetype, conteudo) VALUES (:nome, :mimetype, :conteudo)');

$stmt->bindParam(':nome', $nome);
$stmt->bindParam(':mimetype', $mimetype);
$stmt->bindParam(':conteudo', $handle, PDO::PARAM_LOB);

// Inserindo o arquivo no BD
$pdo->beginTransaction();
$stmt->execute();
$pdo->commit();

Consultando um arquivo do BD

Assim como foi usado um handle de arquivo para armazenar o arquivo no BD, quando ele é consultado também é devolvido um hanle de arquivo.

// Definindo qual arquivo sera consultado
$id = ...

// Consultando o arquivo
$pdo = new PDO(...);
$stmt = $pdo->prepare('SELECT nome, mimetype, conteudo FROM arquivos WHERE id=:id LIMIT 1');
$stmt->execute(array(':id' => $id));

// Atribuindo o resultado a variaveis
$stmt->bindColumn(1, $nome, PDO::PARAM_STR);
$stmt->bindColumn(2, $mimetype, PDO::PARAM_STR);
$stmt->bindColumn(3, $handle, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);

Com o handle em mãos novamente, você é capaz de enviar o arquivo para o usuário:

header('Content-Type: '.$mimetype);
header('Content-Disposition: inline; filename="'.$nome.'"');
header('Cache-Control: no-cache, no-store, must-revalidate');
header('Pragma: no-cache');
header('Expires: 0');
fpassthru($handle);
exit(0);

Para forçar o download, basta trocar "inline" por "attachement".

26 comentários

João Paulo Novais disse...

Rubens, parabéns pelo post.

Seguindo suas orientações, fiz um insert assim:

static function inserir(Model $escola) {

try {

$conn = Conexao::obterConexao();

$sql = $conn->prepare("insert into lcd_escola (nom_escola, nom_razao_social,
num_cnpj, des_endereco,
des_cidade, des_bairro,
sig_uf, cep_escola,
txt_observacao,med_logo)
values (?,?,?,?,?,?,?,?,?,?)");
$sql->bindParam(1,$escola->get("nom_escola"));
$sql->bindParam(2,$escola->get("nom_razao_social"));
$sql->bindParam(3,$escola->get("num_cnpj"));
$sql->bindParam(4,$escola->get("des_endereco"));
$sql->bindParam(5,$escola->get("des_cidade"));
$sql->bindParam(6,$escola->get("des_bairro"));
$sql->bindParam(7,$escola->get("sig_uf"));
$sql->bindParam(8,$escola->get("cep_escola"));
$sql->bindParam(9,$escola->get("txt_observacao"));
$sql->bindParam(10,$escola->get("med_logo"),PDO::PARAM_LOB);

$sql->execute();

$cod_escola = $conn->lastInsertId();

mysql_close($conn);

return $cod_escola;
} catch (Exception $e) {
$conn->rollback();
mysql_close($conn);
$e->getMessage();
}
}

Mas ele não executa. O que pode estar errado?

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, João Paulo

O exemplo que passei, foi utilizando a classe PDO, que não é compatível com as funções do MySQL. Acho que por isso seu código não esteja funcionando. Suponho que sua classe Conexao abra a conexão com mysql_connect, que devolve um resource, e não um objeto da classe PDO.

Para conhecer mais o PDO, recomendo a leitura deste outro artigo: http://rubsphp.blogspot.com/2010/09/pdo.html

Anônimo disse...

Rubens, nos últimos projetos estou utilizando esse recurso de salvar arquivos no banco de dados (upload de imagens), só que estou tendo problemas de performance acredito que seja na hora de exibir a imagem no navegador pois alem de buscar eu faço um redimensionamento quando necessário a media para carregar uma imagem de 638x375 é de 3s, o que posso fazer para resolver esse problema?

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Anônimo

Uma imagem de 638x375 não me parece tão grande para custar 3 segundos, mesmo com redimensionamento.

Sugiro que, antes de qualquer coisa, avalie o tempo gasto em cada operação (quanto demorou a consulta e quanto demorou o redimensionamento). Para isso, use o microtime assim:

$inicio = microtime(true);
alguma_operacao_aqui();
$termino = microtime(true);
$tempo = $termino - $inicio;

Neste caso, não vale a pena atacar o problema sem conhecer exatamente sua origem.

Raphael Davis disse...

Cara estou trabalhando em um projeto e você me ajudou com essa parte de gerenciamento de dados dinâmico com PDO, só que estou com um problema.

Na parte de inserção funciona quase tudo normal, só que o dado que tento inserir na base, ele não insere. Estou tentando inserir um objeto que ele cria com a biblioteca FPDF, ou seja, salvar um pdf no banco.

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Raphael
Como você está tentando salvar o PDF no banco? Eu sugiro que, primeiro, salve o arquivo em algum diretório temporário no servidor e siga os passos propostos pelo artigo, depois apague o arquivo do diretório.

Notei que escrevi "$handle" errado no artigo, agora já corrigi. Estava "hanle". Caso tenha copiado código, verifique isso também.

Gustavo Silveira disse...

Rubens, tudo joia?
Com base nas suas dicas de PDO eu montei um sisteminha simples para livros e inseri um contador de downloads...
Existem 2 tabelas: "books" e "cont_downloads". Para o contador criei uma tabela "cont_downloads" e sempre que o usuário clicar no link de download, ela vai armazenar "id, ip, title, autor e genero". O "title", "autor" e "genero" eu puxo da tabela "books". Porém, estou com problema no while...Sempre é armazenado o PRIMEIRO ou o ÚLTIMO item da lista de downloads na tabela "cont_downloads". De acordo com a linha onde eu fecho o while, ele armazena o primeiro nome da lista ou o último nome, mas nunca armazena realmente o link que eu acabei de baixar.... Não sei mais o que fazer para corrigir... Você poderia me ajudar a descobrir o problema? Lembrando que eu quero armazenar os downloads, um a um, na tabela.

Abaixo segue o código:

Gustavo Silveira disse...

$ip = $_SERVER['REMOTE_ADDR']; //Pega o ip do usuário

if(isset($_GET['arquivo'])){
require 'conexao.php';
$sql = "SELECT * FROM books";
$stmt = $pdo->query($sql);//O método query ira armazenar na variável $stmt todos os dados referentes a consulta do banco

while($row = $stmt->fetch(PDO::FETCH_OBJ)){
$arquivo = $row->caminho;
$titulo = $row->title;
$autor = $row->author;
$genero = $row->genero;
}

$caminho = 'uploads/'.$arquivo;
header('Location:'.$caminho);

$sql ="INSERT INTO cont_downloads (ip, title, author, genero) VALUES (?, ?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(1, $ip, PDO::PARAM_STR);
$stmt->bindParam(2, $titulo, PDO::PARAM_STR);
$stmt->bindParam(3, $autor, PDO::PARAM_STR);
$stmt->bindParam(4, $genero, PDO::PARAM_STR);

$pdo->beginTransaction();
$stmt->execute();
$pdo->commit();
exit();
}
else{
echo "Por favor, selecione um arquivo na pagina de downloads.";
}

Desde já, obrigado pela ajuda! Abs, Gustavo!

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Gustavo
O problema é que você fez um while para percorrer os livros selecionados, só que fez o insert fora do while. Deste jeito, o $row é sobrescrito a cada iteração do loop e só o valor da última iteração é considerado pro insert.
O correto seria:
- iniciar transação;
- percorrer os livros que consultou;
- preparar e executar o insert;
- depois que terminar de percorrer os livros, termina a transação.

Gustavo Silveira disse...

Rubens, não sei se fiz da forma correta, mas agora, ao invés de inserir o último item da lista na tabela, ele insere apenas o primeiro.

$ip = $_SERVER['REMOTE_ADDR']; //Pega o ip do usuário

if(isset($_GET['arquivo'])){
require 'conexao.php';
$sql = "SELECT * FROM books";
$stmt = $pdo->query($sql);//O método query ira armazenar na variável $stmt todos os dados referentes a consulta do banco
$pdo->beginTransaction();
while($row = $stmt->fetch(PDO::FETCH_OBJ)){
$arquivo = $row->caminho;
$titulo = $row->title;
$autor = $row->author;
$genero = $row->genero;

$sql ="INSERT INTO cont_downloads (ip, title, author, genero) VALUES (?, ?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(1, $ip, PDO::PARAM_STR);
$stmt->bindParam(2, $titulo, PDO::PARAM_STR);
$stmt->bindParam(3, $autor, PDO::PARAM_STR);
$stmt->bindParam(4, $genero, PDO::PARAM_STR);
$stmt->execute();
$pdo->commit();
}
$caminho = 'uploads/'.$arquivo;
header('Location:'.$caminho);
exit();
}
else{
echo "Por favor, selecione um arquivo na pagina de downloads.";
}

Gustavo Silveira disse...

Rubens, desculpe a amolação, mas mesmo colocando o commit fora do while, continua a dar o mesmo erro amigo... Será que pode estar ocorrendo algum tipo de conflito?

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Gustavo
Não sei o que pode ser. Revisei o seu código e troquei o bindParam por bindValue. Ajustei o ponto onde foi feito o prepare e o commit. Veja se funciona.

Segue o código sugerido:
$ip = $_SERVER['REMOTE_ADDR']; //Pega o ip do usuário

if (isset($_GET['arquivo'])) {
require 'conexao.php';
$sql = 'SELECT * FROM books';
$stmt = $pdo->query($sql);

$pdo->beginTransaction();
$sql = 'INSERT INTO cont_downloads (ip, title, author, genero) VALUES (:ip, :title, :author, :genero)';
$stmtInsert = $pdo->prepare($sql);

while ($book = $stmt->fetchObject()) {
$stmtInsert->bindValue(':ip', $ip);
$stmtInsert->bindValue(':title', $book->title);
$stmtInsert->bindValue(':author', $book->autor);
$stmtInsert->bindValue(':genero', $book->genero);
$stmtInsert->execute();
}
$pdo->commit();

header('Location: uploads/' . $book->caminho);
exit();
} else {
echo "Por favor, selecione um arquivo na pagina de downloads.";
}

Gustavo Silveira disse...

Rubens, obrigado pela força amigo. Você deixou o código mais enxuto, ficou bem melhor...Porém, agora ele está listando todos os arquivos da pasta "uploads/" ao invés de efetuar o download do arquivo no qual eu cliquei.

Deixe eu esclarecer a logica do meu código, talvez assim você consiga me ajudar: Eu tenho 2 tabelas, 'books' e 'cont_downloads'. A tabela 'books' armazena os dados do livro e possui os campos: "title", "author", "genero" e "caminho" (O caminho é o NomeUnix do arquivo salvo dentro da pasta Uploads). A tabela 'cont_downloads' armazena os downloads efetuados e possui os campos: "ip", "title", "author", "genero" e "data".

Através do SELECT * FROM books e do While eu consulto o campo "caminho" dentro da tabela 'books' e salvo o caminho ( que é o NomeUnix do arquivo dentro da pasta uploads) na variável $arquivo (a qual você chamou de $books), depois consulto o campo "title" e salvo na variável $titulo, consulto o campo "author" e salvo na variável $autor e consulto e campo "genero" e salvo na variável $genero.

Feito isso, eu insiro os dados contidos nas variáveis $titulo, $autor, $genero e $ip (a variável $ip eu crio logo no início do script para pegar o IP do usuário) na tabela 'cont_downloads'. Assim eu tenho o registro do download por IP e data, com o titulo, autor e genero do livro baixado.

No final da sugestão de código que você me ofereceu, quando você concatena o $book->caminho ao uploads/, o caminho está sendo resgatado do campo "caminho" lá do SELECT * FROM books que você fez no início do código?

Abs,


Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Gustavo
Pelo que entendi, você deveria inserir apenas um registro na tabela count_downloads, quando faz download de um arquivo (que é informado via GET). Neste caso, por que você está consultando todos os livros? Não deveria consultar apenas o livro que passou o caminho por GET? Neste caso, não precisaria de while, nem de transação. Veja se é isso que quer:

$ip = $_SERVER['REMOTE_ADDR'];

if (isset($_GET['arquivo'])) {
require 'conexao.php';
$sql = 'SELECT * FROM books WHERE caminho = :caminho';
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':caminho', $_GET['arquivo']);
$stmt->execute();

if ($book = $stmt->fetchObject()) {
$sql = 'INSERT INTO cont_downloads (ip, title, author, genero) VALUES (:ip, :title, :author, :genero)';
$stmtInsert = $pdo->prepare($sql);
$stmtInsert->bindValue(':ip', $ip);
$stmtInsert->bindValue(':title', $book->title);
$stmtInsert->bindValue(':author', $book->autor);
$stmtInsert->bindValue(':genero', $book->genero);
$stmtInsert->execute();

header('Location: uploads/' . $book->caminho);
exit();
} else {
echo "Arquivo invalido.";
}
} else {
echo "Por favor, selecione um arquivo na pagina de downloads.";
}

Gustavo Silveira disse...

Continua dando problema. Ele cai diretamente no "Arquivo Inválido". Não sei, talvez o problema aconteça pois não estamos passando valor algum no $_GET['arquivo']. Na realidade o $_GET['arquivo'] não carrega nada... Ele apenas inicia o código via if(Isset. Inclusive, agora vejo que não tem necessidade de existir esse $_GET['arquivo']...

Por isso eu fazia um While. O While pega a informação title, author, genero e caminho da tabela books. E o Insert às insere na tabela cont_downloads, junto com o IP e com a Data.

Abaixo segue a pagina onde eu listo os downloads ATIVOS, veja como acontecia (REMOVI ALGUMAS COISAS POIS O BLOG NÃO PERMITE HTML):

require 'conexao.php';
$sql = "SELECT * FROM books WHERE active=1";
$stmt = $pdo->query($sql);
$id = 1;

?>
table class="table table-striped"
tr
th ID /th
th Título /th
th Autor /th
th Gênero /th
th Dwnload /th
/tr
fetch(PDO::FETCH_OBJ)){
echo "
tr
td".$id++." /td
td {$row->title} /td
td {$row->author} /td
td {$row->genero} /td
td a href=download.php?arquivo=action>Download do Arquivo /a /td
/tr";
}
?>

/tr
/table

Como você pode ver, eu passo o "arquivo" junto com o link do download, porém, agora vejo que é completamente desnecessário. Eu havia feito isso para evitar que o usuário ativasse o conteúdo da pagina download.php acessando-a diretamente pelo navegador. Porém, se o usuário digitar download.php?arquivo=action vai ativar do mesmo jeito... Então, talvez seja melhor remove-lo e simplesmente bloquear o acesso à pagina via .htaccess.

Gustavo Silveira disse...

Rubens, resolvi os problemas. O site todo está funcionando perfeitamente agora. Mudei algumas coisas no código, removi a pagina "download.php" e a contagem de downloads eu estou fazendo via Event Track do GA. Muito obrigado pela ajuda!

Anônimo disse...

Bom dia professor. Parabéns pelo artigo.
Tenho uma variável que contem o binário de uma imagem, tem como eu gravar o conteúdo dessa variável no banco de dados mysql em um campo tipo blob?

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Anônimo
Se você já possui uma variável com o conteúdo binário do arquivo, pode passá-la no método bindValue e salvá-la normalmente. A vantagem de usar o file handle é que o conteúdo a ser salvo não precisa ficar na memória, já que o file handle é só um ponteiro para uma posição do arquivo.

Unknown disse...

Amigo teria como enviar ou postar algum exemplo prático do mesmo, estou iniciando com pdo, mas precisso muito já utilizar do mesmo

Unknown disse...

Amigo, já utilizo do pdo em minhas aplicações e vi seu post, estou tendo um grande problema na hora de fazer o download ou na exibição do arquivo, pois guardo no banco em formato blod, entretanto quando chamo o arquivo novamente da sempre corrompido

Rubens Takiguti Ribeiro (autor do blog) disse...

Oi, Alberto
O artigo já apresenta exemplos bem práticos. Só precisa ajustar os pontos em que você abre a conexão com o banco, ou abre o arquivo para leitura.

Experimente com um arquivo texto pequeno e veja se o conteúdo é exibido corretamente.

Talvez o problema seja com o tamanho limite do upload no seu servidor.