Safe way to use PDO?

Question

I’m starting with the class PDO and I have insecurity in working with it, is that I am working in the safest way (when we speak of data security)?

I know there are hundreds of parameters on PDO, as bind, etc. But this way that I am working is safe?

To connect:

try
{
    $conn = new PDO("mysql:host={$dbhost};dbname={$dbname}", $dbuser, $dbpass);
    $conn ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $conn;
}
catch (PDOException $ex)
{
    echo "<br>Erro de conexão: " . $ex->getMessage();
    return false;
}

To consult:

if($result = $conn->query($sql))
{
    $rows = $result->fetchAll(PDO::FETCH_ASSOC);
    if(empty($rows))
        echo "<br>Sem resultados!<br>";
    $conn = null;
    return $rows;
}
else
{
    echo "<br>Sem resultados!<br>";
    return false;
}  

To delete:

if($result = $conn->query($sql))
{
    $stmt = $conn->prepare( $sql );
    if($result = $stmt->execute())
            echo "<br>Deletado!<br>";
    else
        echo "<br>Query inválida!<br>";

    $conn = null;

    return true;
}
else
{
    echo "<br>Query inválida!<br>";
    return false;
}

To insert and update:

if($stmt = $conn->prepare( $sql ))
{
    if($result = $stmt->execute())
            echo "<br>Inserido!<br>";
    else
        echo "<br>Query inválida!<br>";

    $conn = null;
    return true;
}
else
{
    echo "<br>Query inválida!<br>";
    return false;
} 

Maybe with bind could be safer, but how to use when passing parameters to function? That way is already safe? And there’s some unused line in my code?

Answer 1

In addition to what I said in the comments, I would like to add that writing scripts in procedural form using the PDO, It can be a little more complicated if you’re using related functions. In the examples you put in your question, you omitted the first part in all the examples, leaving the problem vague.

Another thing is the problem. Like you said yourself, you’re concatenating parts to get a queryString end that is executed without any problems.

Though you did quote «I’m picking up function parameters (column, Where, order, etc.)», was unclear about what you were referring to, and at this point I still don’t know if spine or Where are individual functions or arguments of a single function. At least you can see that you are not using OOP, but it’s still unclear if you’re writing code by blocks grouped into functions or blocks separated by different files.

Anyway I will answer the question with an example, although I have made use of functions and have broken the code in different parts to increase readability as much as possible, I hope that it complies with the intended.

A priority factor is the handling of errors, launched through exceptions by PDO, however I only dealt with the errors while connecting to the server. For this we made something simple, we made use of a closure, and also set 2 variables crucial to the script.

connect

$prepare = $rows = "";
$pdo = function ()
{
    try{
        $pdo = new PDO('mysql:host=localhost;dbname=example;charset=utf8;', 'root', '');
        return $pdo;    
        } catch(PDOException $e){
        die($e->getMessage());  
    }   
};

The second thing to be done was to create a function that could execute any query SQL provided to you, and returns true, if such consultation is carried out.

execute any query

function query($sql, $parametros)
{
    global $pdo;
    global $prepare;    
    if($prepare = $pdo()->prepare($sql))
    {   
        foreach($parametros as $key=> &$valor){
            $prepare->bindParam(":{$key}", $valor);
        }

        //return $prepare->debugDumpParams();

        if($prepare->execute()){
            return true;    
        }   
    }
    return false;
}

So far everything is normal and working, but still, the idea remains vast, both for different types of consultation - select, delete, Insert, update - as for the various clauses and other functions that can be executed in the final query through this function query. What to do is obvious, distribute tasks, creating third parties capable of providing queryString in a state where the query function will only have to execute, regardless of what comes through it.

run queries of type select and delete

function accao($accao, $tabela, $argumentos = array())
{
    if(!empty($accao) && !empty($tabela) && !empty($argumentos)){
        $campo = $argumentos[0];
        $valor = strtoupper($argumentos[1]) === "LIKE" ?  array($campo=>"%{$argumentos[2]}%") : array($campo=>$argumentos[2]);
        $operador = $argumentos[1];
        $order = isset($argumentos[3]) ? order($accao, $argumentos[3]) : order($accao);
        $marcador = strtoupper($operador) === "IN" ? '(' . parametros($valor, 'param', true) . ')' : parametros(array($campo=>$valor), 'param', true);
        $sql = "{$accao} FROM {$tabela} WHERE {$campo} {$operador} {$marcador} {$order}";
        if(query($sql, $valor)){
            return true;    
        }   
    }
    return false;   
}

This function receives a command accao - select, Insert, delete, update - and analyses the remaining parameters for the queryString based on that action, and puts them as part of it, so you always get a queryString valid.

As part of this function we also have the function order, which only resolves the question of order, and works only if the consultation concerns the SELECT, otherwise no additional parameter is returned - «empty string ».

ordain

function order($args, $set = null)
{
    if(strtoupper($args) === "SELECT" || "SELECT *"){
        if(isset($set) && !is_numeric($set)){
            if(!empty($set)){
                $order = "ORDER BY {$set} ASC"; 
            } else {
                $order = "ORDER BY {$set} DESC";    
            }   
        } else {
            $order = "";    
        }
        return $order;  
    }   
    return false;   
}

INSERT

function insert($tabela, $campos=array())
{
    if(!empty($tabela)){
        if(!empty($campos) && is_array($campos)){
            $valores = parametros($campos, 'param', true);  
            $colunas = parametros($campos, 'coluna');
            $sql = "INSERT INTO {$tabela} ({$colunas}) VALUES ({$valores})";
            if(query($sql, $campos)){
                return true;    
            }
        }   
    }   
    return false;   
}

UPDATE

function update($tabela, $id, $campos)
{
    if(!empty($tabela) && !empty($id)){
        if(!empty($campos) && is_array($campos)){
            $campos['id'] = $id;    
            $set = parametros($campos, 'update', true);
            $equals = parametros(['id'=>$id], 'update', true);
            $sql = "UPDATE {$tabela} SET {$set} WHERE {$equals}";
            if(query($sql, $campos)){
                return true;    
            }   
        }   
    }   
    return false;   
}

Both the function Insert as the update depend on the function parameters, responsible for providing the parameters :named/(?), and also some additional parameters.

parameters

function parametros($args = array(), $tipo=null, $named = false)
{
    $coluna = isset($args['colunas']) ? $args['colunas'] : array_keys($args);
    $i=1;
    $param='';
    foreach($args as $key=>$arg){
        if(!empty($tipo) && $tipo === 'update'){
            if($named){
                $param .= "{$key} = :{$key}";       
            } else {
                $param .= "{$key} = ?";     
            }   
        } else {
            if($named){
                $param .= ":{$key}";    
            } else {
                $param .= "?";  
            }       
        }
        if($i < count($args)){
            $param .= ', '; 
        }
        $i++;
    }
    if(!empty($tipo)){
        switch($tipo){
            case 'coluna':
                $retorno = implode(', ', $coluna);
                break;  
            case 'param':
                $retorno = $param;
                break;  
            default:
                $retorno = ['colunas'=>implode(', ', $coluna), 'values'=>$param];   
                break;  
        }
        return $retorno;    
    }
    return ['colunas'=>implode(', ', $coluna), 'values'=>$param];   
}

The function select and delete depend solely on the function accao that does much of the work for them.

SELECT

function select($tabela, $campos=array())
{
    global $prepare;    
    if(accao("SELECT *", $tabela, $campos)){
        $all = $prepare->fetchAll(PDO::FETCH_OBJ);
        return $all;
    }   
    return false;
}

DELETE

function delete($tabela, $campos=array())
{
    global $prepare;
    global $rows;
    if(accao("DELETE", $tabela, $campos)){
        $rows = $prepare->rowCount();   
        return true;
    }
    return false;   
}

To execute any kind of query, it would be like this:

query('SELECT * FROM games WHERE name = ? ', ['name'=>'Edilson', 'surname'=>'Samuel']));

To perform an Insert:

var_dump(Insert('games', ['name'=>'Something Awful', 'console'=>'XBOX']));

To perform an update:

var_dump(update('games', 17, ['name'=>'Something Creepy', 'console'=>'XBOX']));

To perform a select:

var_dump(select('games', ['id','>', '10']));

To perform a delete:

var_dump(delete('game', ['id','=', '18']));

In consultation with the delete the variable was used $rows to store the number of queries removed, and this value can be read normally.

if(!empty($rows)){
    print "Removidos:" . $rows; 
}

Despite being an extensive example, one can see how various types of query are formed, through various parameters that may or may not vary depending on the type of query requested. All queries end the function query, where the values/variables are linked(as) to the respective markers and subsequently executed.

This is an old example, with superficial modifications, and works similarly to the methods that the framework uses, although more complex and improved have a similar function. One queryString can be built through simpler processes, the use of functions/methods simply makes these blocks reusable, and also organizes them.