mysql

PHP + MySQL + jQuery,Tutoriais

Trabalhando com o banco de dados MySQL (Segurança/Novidades)

26 mar , 2014  

Nesse post vamos apresentar recursos de segurança como o SQL Injection, mas também a atualização dos comandos MySQL, como o MySQLi e também vamos falar sobre a biblioteca PDO do PHP.

Segue os links dos tutoriais relacionados ao MySQL:

1 – MySQL – Básico (Criação e utilização no PHP)
2 – MySQL – Intermediário (INNER JOIN, LEFT JOIN, RIGHT JOIN, GROUP BY, HAVING, UNION e Aliases)
3 – MySQL – Avançado (Relacionamentos de tabelas)
4 – MySQL – Backup (Realizar backup do banco de dados)
5 – MySQL – Segurança (Métodos de manter a segurança de informação)

SQL Injection

O SQL Injection é uma vulnerabilidade de comandos SQL, podendo manipula-los para retornar informações do banco de dados, como registros ou estrutura. Mas há alguns recursos de como evitar essa manipulação, conforme função abaixo que esta bem completa, você pode alterar para sua necessidade.

<?php
//criamos aqui uma função para ser utilizada em qualquer parte do nosso projeto
function retira_sql_injection($variavel,$numerico){
	//aqui verifica se você informou se a variável é numérica ou não
	if($numerico == true){
                //realizamos a primeira verificação se a variável realmente é numérica
		if(is_numeric($variavael)){
                        //segunda verificação se a variável é numérica
			if(!preg_match('/[^0-9]/',$variavael))
				return false;
		}
	}else{
                //verificamos aqui se a variável consta apenas letras
		if(!preg_match('/[^A-Za-z]/',$variavael))
			return false;
	}
	
        //adicionamos a um vetor palavras especificas do mysql
	$valores_mysql = array("select ","update "," where "," left "," not "," like "," drop "," alter ","insert ","delete "," join "," inner "," in ","truncate","create ","delimiter"," limit "," order "," by "," desc"," asc","case"," or "," and "," from "," table ","show ","tables"," union "," all","version"," hex ","unhex"," from "," null ","having","database","user","*","/*","\"\"","''"," ",";","=","<",">","!","--","#","//","\\");
        //e aqui verificamos se na variável consta alguma palavra do nosso vetor
	for($i=0;$i<sizeof($valores_mysql);$i++){
		if(stristr($variavel, $valores_mysql[$i])){
			return false;
		}
	}
	
        //retira os espaços no inicio e final da variavel
	$variavel = trim($variavel);
        //retiramos todas as tags html e php
	$variavel = strip_tags($variavel);
        //escapa os caracteres especial mysql
	$variavel = mysql_real_escape_string($variavel);
        //adiciona barras invertidas em caracteres que precisam ser escapados, como `, ", \.
	$variavel = addslashes($variavel);
	
	return $variavel;
}
?>

MySQLi

É uma extensão do MySQL, que foi desenvolvida para aproveitar os recursos mais avançados do MySQL, podendo utilizar a forma de orientação a objetos.
Antes de começar, verifique no seu Apache se você tem habilitado o MySQLi, pode ver pelo phpinfo().

<?php
    phpinfo();
?>

11
Já que estamos no phpinfo(), aproveite para verificas as versões do PHP e MySQL. Devem ser respectivamente 5 ou superior e 4.1.3 ou superior.
Fazendo a conexão com o banco de dados:

<?php   
    $db = new mysqli('localhost', 'seu_usuario', 'sua_senha', 'seu_banco');
    
    if (mysqli_connect_errno()) {
         die('Não conectou ao banco de dados: '.mysqli_connect_error());
         exit();
    }
?>

Selecionando dados:

<?php   
    $result = $db->query("SELECT `nome`, `cidade` FROM `clientes`");
    while($dados = $result->mysqli_fetch_array()){
	echo "Nome:".$dados['nome'];
	echo "Cidade:".$dados['cidade'];
    }
    echo "Quantidade de clientes: ".$result->num_rows;
?>

É interessante também, você liberar um resultado quando você terminar de utiliza-lo. Liberando os recursos do sistema.
Use a seguinte forma, após o loop:

<?php 
    $result->free();
?>

Agora vamos inserir um registro no banco de dados:

<?php 
    $result = $db->query("INSERT INTO `exemplo` (`nome`,`email`) VALUES ('Rodrigo','rodrigo@teste.com.br')");
    echo 'Registros adicionados: '.$result->affected_rows;
?>

Um ponto interessante de citar é referente ao escape das suas variáveis, fazendo da seguinte forma:

<?php
     $nome = 'Rodrigo';
     $nome = $db->escape_string($nome);
     $result = $db->query("INSERT INTO `exemplo` (`nome`) VALUES ('".$nome."')");
     echo 'Registros adicionados: '.$result->affected_rows;
?>

Não se esqueça de fechar a conexão no final:

<?php 
   $db->close();
?>

Para se ter mais segurança ainda com SQL Injection, não se incomodando tanto com os escapes, existe as instruções preparadas:

Basicamente você substitui a variavel na sua query, por um ‘?’. Veja exemplos abaixo:

Primeiro você prepara uma consulta SQL:

<?php
    $result = $db->prepare("SELECT `id`, `nome`, `cidade` FROM `clientes` WHERE `id` <= ?");
?>

Agora para você selecionar os itens dessa consulta, você utiliza:

<?php
    $id = 1;
    $result->bind_param('i', $id);
?>

Onde a ‘?’ na nossa consulta, será o id que enviamos pelo bind_param. Outro ponto a citar também é referente aos tipos de variáveis (string, int, double ou blob), que você deve mencionar na função, no nosso caso foi o inteiro, então ‘i’.

Os tipos de valores do MySQLi são:

i – Integer
s – String
d – Double
b – blob

Você pode enviar mais que um tipo de variável em uma mesma função:

<?php
    $result->bind_param('sdi', $nome, $largura, $idade);
?>

Agora temos que executar a função, da seguinte forma:

<?php
    $result->execute();
?>

Para retornar os dados da nossa consulta:

<?php
    $result->bind_result($id, $nome, $cidade);
    while ($result->fetch()) {
	echo 'id: '.$id;
	echo 'nome: '.$nome;
	echo 'cidade: '.$cidade;
    }
?>

Para saber a quantidade de dados retornados:

<?php
    $result->num_rows;
?>

E também temos que liberar os resultados:

<?php
    $result->free_result();
?>

Vamos fechar a nossa consulta:

<?php
    $result->close();
?>

Vamos falar também um pouco sobre as transações do MySQLi:

Uma transação é um conjunto de consultas que executam, mas não salvam no banco de dados. Por exemplo, se você precisa inserir dados dependentes um dos outros, como em um relacionamento e uma das inserções falhar, você pode cancelar as inserções com o rollback().

Lembrando que o tipo do seu banco de dados deve dar suporte ao commit, no caso, pode ser usado o ‘InnoDB’.

Primeiramente vamos desativar o autocommit do MySQL:

<?php
    $db->autocommit(FALSE);
?>

Agora você pode fazer os seus querys..

E depois enviar os dados para o MySQL:

<?php
    $db->commit();
?>

Caso ocorra algo errado ou você queira cancelar o commit, apenas dê um Rollback:

<?php
    $db->rollback();
?>

Biblioteca PDO

PDO (PHP Data Objects) é uma biblioteca padronizada para trabalhar com banco de dados. Mas que trabalha com diversos tipos de bancos de dados, como SQL Server, MySQL, Oracle, PostgreSQL e muitos outros.

Verifique também se o PDO esta habilitado no seu Apache pelo phpinfo():

12

Vamos iniciar com a conexão ao banco de dados:

<?php
    $db = new PDO("mysql:host=localhost;dbname=nome_do_banco", "seu_usuario", "sua_senha"); 
    if(!$db){
       die('Erro ao conectar!');
   }
?>

Vamos inserir um dado:

<?php   
   $insert = $pdo->query("INSERT INTO cliente(idcliente, nome) VALUES ('1', 'Rafael')");
   if($insert)
      echo 'Inserção realizada!';
   else
      print_r($db->errorInfo());  
?>

Vamos consultar os dados:

<?php    
   $result = $db->query("SELECT id, nome FROM cliente");
   while ($item = $result->fetch(PDO::FETCH_ASSOC)) {
      echo $item['id'];
      echo $item['nome'];
   }
?>

Agora vamos mostrar um pouco como funciona o PDO com instruções preparadas:

Para você inserir dados na tabela:

<?php
    $result = $db->prepare("INSERT INTO cliente(id, nome) VALUES(?, ?)");
    $result->bindParam(1, 1, PDO::PARAM_INT);
    $result->bindParam(2, "Rodrigo", PDO::PARAM_STR);
    $result->execute();
?>

Retornando dados de uma consulta:

<?php
    $result = $db->prepare("SELEC id, nome FROM cliente");
    $result->execute();
    while($item = $result->fetch(PDO::FETCH_OBJ)){
	echo $item->id;
	echo $item->nome;
    }
?>

Atualizando dados:

<?php
    $insert = $db->prepare("UPDATE cliente SET nome = ? WHERE id = ?");    
    $insert->bindParam(1, "Rodrigo", PDO::PARAM_STR);
    $insert->bindParam(2, 1, PDO::PARAM_INT);
    $insert->execute();
?>

Uma ideia interessante é você utilizar o try catch, para excessões do PDO:

<?php
   $id = 1;
   $nome = "Rodrigo";
   try{
       $result = $db->prepare("INSERT INTO cliente(id, nome) VALUES (?, ?)");
       $result->bindParam(1, $id, PDO::PARAM_INT);
       $result->bindParam(2, $nome, PDO::PARAM_STR);
       
       if($result->execute()){
           echo 'Inserção realizada!';
       }else{
           echo 'Falha na Inserção';
       }
   }catch(PDOException $e){
      echo $e->getMessage();
   }
?>

Também podemos trabalhar com commits:

Iniciamos da seguinte forma:

<?php
   $db->beginTransaction();
?>

Realize sua transação, já com uma validação:

<?php
    $insert = $db->prepare("UPDATE cliente SET nome = ? WHERE id = ?");    
    $insert->bindParam(1, "Rodrigo", PDO::PARAM_STR);
    $insert->bindParam(2, 1, PDO::PARAM_INT);
    
    if(!$insert->execute()){
        $db->rollBack();
        die('Erro ao atualizar saldo');
    }else{
        $db->commit();
    }

?>

Fica a critério e a gosto de cada um utilizar uma dessas formas, lembrando também que é importante a validação em Javascript e em PHP. Assim você mantém a segurança de informação no seu projeto.

Espero que esse tutorial tenha sido útil. Continue postando seus comentários.

Abraço.

, , , , ,


Comments are closed.