Is using PDO the safest way to connect to a PHP BD?

Question

I’m 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 enter/select data.

Answer 1

Yes PDO is one of the new Apis for connection to the database another is mysqli. One of the advantages of PDO is support for multiple banks and Prepared statements (emulated).

In the PDO Controller five information is required which are, driver, server/host, base name, user and password.

Parameters of the constructor

Some interesting options such as error control that can be errors or exceptions, des/enable autocommit among others can be defined in the constructor as the last argument. This can also be done through the method setAttribute(). 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 name of the array keys will be uppercase.

In the builder:

$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 were already discontinued a long time ago and did not support Prepared statements, to get the result of a select it took three steps, sql definition, run(turn the string into an sql command and apply in the database) 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, turn the string into a prepared query, replace placeholders with values, run the query in the database and finally get the result.

The replacement of tides(:valor or ?) also known named placeholders and placeholders can be made through three methods, bindValue(), bindParam() and in the 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 the execute() as an array it is more practical 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';
}

Select

//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 in selects

Some of the main methods to get the return of a select are fetch() returning only one record and fetchAll() that returns an array as the specified type in the example PDO::FETCH_ASSOC(associative array), can also be object array.

Recommended reading:

Why should we not use mysql type functions_*?

Mysqli vs PDO - which is the most recommended to use?

SQL LIMIT parameterized in PHP with PDO

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

PDO PREPARE method does not work - dynamic bind

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

Insert into foreign key tables with PDO - get entered record id