Is using PDO the safest way to connect to a DB with PHP?

40

I am a beginner in PHP and would like to know if PDO (PHP Data Objects) is the safest way to connect to a database? I also need an example of how to make this connection and insert / select data.

    
asked by Denis Caixeta 09.06.2015 в 15:26
source

1 answer

39

Yes the PDO is one of the new APIs for connecting to the other database is mysqli. One of the advantages of PDO is the support for several banks and prepared statements.

In the PDO builder you will need five pieces of information that are driver , server / host, name base, user and password.

Builder Parameters

Some putative options like error control that can be errors or exceptions, disabling / setting autocommit among others can be defined in the constructor as the last argument. This can also be done using the setAttribute() method. List of available options .

In the example below it is defined that the errors will be treated as exceptions, the default return type is an associative array, and the latter indicates that the array's key names will be case-sensitive.

In the constructor:

$opcoes = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_CASE => PDO::CASE_UPPER);
$db = new PDO('mysql:host=localhost;dbname=catalogo', 'root', 'root');

Or with setAttribute:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_CASE, PDO::CASE_UPPER);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

Prepared statements and mysql _ *

The mysql_ * functions have been deprecated for a long time and do not support prepared statements, to get the result of a select it was necessary three steps, sql definition, execute (transform the string into a sql command and apply to the database) the query and get the result for an insert / update / delete the last step is omitted.

Typical code with mysql _ *

$link = mysql_connect($host, $usuario, $senha);
mysql_select_db($banco);

//1 - passo
$sql = "SELECT * FROM tabela WHERE nome = 'admin' AND senha = '#4skdjf' AND ativo = 1";
//2 - passo
$res = mysql_query($sql) or die(mysql_error());
//3 - passo
while($row = mysql_fetch_assoc($res)){
    echo $row['nome'];
}

With prepared statements we have five steps, sql definition, transform the string into a prepared query, replace the placeholders with the values, execute the query in the database and finally get the result.

Replacing margins ( :valor or ? ) also known as placeholders and placeholders can be done through three methods, bindValue() , bindParam() and no execute() . The difference between bindValue() and bindParam() is that the second accepts only references (variables / constants) so returns of functions / methods cause an error.

//bindParam

$valor = 10;

$stmt = $db->prepare($sql);
$stmt->bindParam(':v1', $valor); //válido
$stmt->bindParam(':v1', 10); //inválido
$stmt->bindParam(':v1', getValor()); //inválido
$stmt->bindParam(':v1', $obj->getValor()); //inválido

//bindValue

$stmt = $db->prepare($sql);
$stmt->bindValue(':v1', 10); //válido
$stmt->bindValue(':v1', getValor()); //válido
$stmt->bindValue(':v1', $obj->getValor()); //válido

The third way is to pass the values in execute() as an array it is more convenient for queries with dynamic parameters.

$stmt = $db->prepare($sql);
$stmt->execute(array(':v1' => '10', ':v2' => 'admin'));

To insert / update / delete

$db = new PDO('mysql:host=localhost;dbname=base', 'usuario', 'senha');

//1 - passo
$sql = 'INSERT INTO tabela (c1,c2,c3) VALUES(?,?,?)';
//2 - passo
$stmt = $db->prepare($sql);
//3 - passo é aqui é o array(...), 4 - passo é chamada de execute()
if($stmt->execute(array('valor1', 'valor2', 3)) === false){
    print_r($stmt->errorInfo());
}else{
    echo 'insert realizado com sucesso';
}

For selects

//1 - passo
$sql = 'SELECT * FROM tabela WHERE c = :v1 AND c2 = :v2';
//2 - passo    
$stmt = $db->prepare($sql);
//3 - passo
$stmt->bindValue(':v1', 10);
$stmt->bindValue(':v2', 'janeiro');
//4 - passo
$stmt->execute();
//5 - passo
$itens = $stmt->fetchAll(PDO::FETCH_ASSOC);

Return on Selects

Some of the top methods for getting a select return are fetch() that returns < strong> only a record and fetchAll() that returns an array as it forms the specified type which in the example was PDO::FETCH_ASSOC (associative array), can also be an array of objects.

Recommended reading:

Why should not we use functions of type mysql_ *?

MySQL vs PDO - Which is the most recommended to use?

SQL LIMIT Parameterized in PHP with PDO

How to group mysql results by foreign keys into a single array through a single query?

PREPARE PDO Method Does Not Work - Dynamic Bind

How to print the SQL statement being sent to the bank?

Insert in Foreign Keyed Tables with PDO - Get Record Id Inserted

    
answered by 09.06.2015 / 15:42
source