Concatenate in the PDO query

Question

I’ve heard that when concatenating into a query using PDO can cause slowdowns and even leave the system vulnerable, this is true?

I want to concatenate into one query and I’m afraid to take certain risks.

public static function read($table, $where = null) {
    $sql = "SELECT * FROM `" . self::$_prefix . "{$table}` {$where}";
}

Answer 1

I use a class to do generic database reading and have never had a performance problem. The biggest problem is that you need to know exactly what is being passed in the parameters to do the query, if not, can be performed sql Injection in your database, which can lead to problems.

I created a class in a course I did on the Internet and since then I use it to do generic readings in the database. It is quite complete, does the treatment of the information passed by parameter, in order to try to minimize the risk of sql Injection. If you want to use it, the operation is very simple:

index php.

    <?php

    include('Config.inc.php');

    // para consultas simples 
    $read = new Read;
    $read->ExeRead('tabela', 'WHERE campo = :valor', 'valor=1');

    if($read->getResult()){

        var_dump($read->getResult());
    } else {

        var_dump($read->getError());

    }

    // para consultas complexas
    $read2 = new Read;
    $read2->FullRead('SELECT tabela.id, tabela2.campo FROM tabela LEFT JOIN tabela2 ON tabela.id = tabela2.id WHERE tabela.id = :id', 'id=1');

    if($read2->getResult()){

        var_dump($read2->getResult());
    } else {

        var_dump($read2->getError());

    }

?>

Config.inc.php

<?php

// CONFIGRAÇÕES DO BANCO ####################
define('HOST', 'localhost');
define('USER', 'USUARIO');
define('PASS', 'SENHA');
define('DBSA', 'DATABASE');


// AUTO LOAD DE CLASSES ####################
function __autoload($Class) {

    $cDir = array('Conn');
    $iDir = null;

    foreach ($cDir as $dirName):
        if (!$iDir && file_exists(__DIR__ . DIRECTORY_SEPARATOR . $dirName . DIRECTORY_SEPARATOR . $Class . '.class.php') && !is_dir(__DIR__ . DIRECTORY_SEPARATOR . $dirName . DIRECTORY_SEPARATOR . $Class . '.class.php')):
            include_once (__DIR__ . DIRECTORY_SEPARATOR . $dirName . DIRECTORY_SEPARATOR . $Class . '.class.php');
            $iDir = true;
        endif;
    endforeach;

    if (!$iDir):
        trigger_error("Não foi possível incluir {$Class}.class.php", E_USER_ERROR);
        die;
    endif;

}

// TRATAMENTO DE ERROS #####################
//CSS constantes :: Mensagens de Erro
define('AW_ACCEPT', 'accept');
define('AW_INFOR', 'infor');
define('AW_ALERT', 'alert');
define('AW_ERROR', 'error');

//AWErro :: Exibe erros lançados :: Front
function AWErro($ErrMsg, $ErrNo, $ErrDie = null) {

    $CssClass = ($ErrNo == E_USER_NOTICE ? WS_INFOR : ($ErrNo == E_USER_WARNING ? WS_ALERT : ($ErrNo == E_USER_ERROR ? WS_ERROR : $ErrNo)));
    echo "<p class=\"trigger {$CssClass}\">{$ErrMsg}<span class=\"ajax_close\"></span></p>";

    if ($ErrDie):
        die;
    endif;
}

//PHPErro :: personaliza o gatilho do PHP
function PHPErro($ErrNo, $ErrMsg, $ErrFile, $ErrLine) {
    $CssClass = ($ErrNo == E_USER_NOTICE ? WS_INFOR : ($ErrNo == E_USER_WARNING ? WS_ALERT : ($ErrNo == E_USER_ERROR ? WS_ERROR : $ErrNo)));
    echo "<p class=\"trigger {$CssClass}\">";
    echo "<b>Erro na Linha: #{$ErrLine} ::</b> {$ErrMsg}<br>";
    echo "<small>{$ErrFile}</small>";
    echo "<span class=\"ajax_close\"></span></p>";

    if ($ErrNo == E_USER_ERROR):
        die;
    endif;
}

set_error_handler('PHPErro');
?>

/Conn/Conn.class.php

<?php

    abstract class Conn {

        private static $Host = HOST;
        private static $User = USER;
        private static $Pass = PASS;
        private static $Dbsa = DBSA;

        /** @var PDO */
        private static $Connect = null;

        /**
         * Conecta com o banco de dados com o pattern singleton.
         * Retorna um objeto PDO!
         */
        private static function Conectar() {
            try {
                if (self::$Connect == null):
                    $dsn = 'mysql:host=' . self::$Host . ';dbname=' . self::$Dbsa;
                    $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8');
                    self::$Connect = new PDO($dsn, self::$User, self::$Pass, $options);
                endif;
            } catch (PDOException $e) {
                PHPErro($e->getCode(), $e->getMessage(), $e->getFile(), $e->getLine());
                die;
            }

            self::$Connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return self::$Connect;
        }

        /** Retorna um objeto PDO Singleton Pattern. */
        protected static function getConn() {
            return self::Conectar();
        }

    }

    ?>

/Conn/Read.class.php

<?php

class Read extends Conn {

    private $Select;
    private $Places;
    private $Result;

    /** @var PDOStatement */
    private $Read;

    /** @var PDO */
    private $Conn;

    /**
     * <b>Exe Read:</b> Executa uma leitura simplificada com Prepared Statments. Basta informar o nome da tabela,
     * os termos da seleção e uma analize em cadeia (ParseString) para executar.
     * @param STRING $Tabela = Nome da tabela
     * @param STRING $Termos = WHERE | ORDER | LIMIT :limit | OFFSET :offset
     * @param STRING $ParseString = link={$link}&link2={$link2}
     */
    public function ExeRead($Tabela, $Termos = null, $ParseString = null) {
        if (!empty($ParseString)):
            parse_str($ParseString, $this->Places);
        endif;

        $this->Select = "SELECT * FROM {$Tabela} {$Termos}";
        $this->Execute();
    }

    /**
     * <b>Obter resultado:</b> Retorna um array com todos os resultados obtidos. Envelope primário númérico. Para obter
     * um resultado chame o índice getResult()[0]!
     * @return ARRAY $this = Array ResultSet
     */
    public function getResult() {
        return $this->Result;
    }

    /**
     * <b>Contar Registros: </b> Retorna o número de registros encontrados pelo select!
     * @return INT $Var = Quantidade de registros encontrados
     */
    public function getRowCount() {
        return $this->Read->rowCount();
    }

    public function FullRead($Query, $ParseString = null) {
        $this->Select = (string) $Query;
        if (!empty($ParseString)):
            parse_str($ParseString, $this->Places);
        endif;
        $this->Execute();
    }

    /**
     * <b>Full Read:</b> Executa leitura de dados via query que deve ser montada manualmente para possibilitar
     * seleção de multiplas tabelas em uma única query!
     * @param STRING $Query = Query Select Syntax
     * @param STRING $ParseString = link={$link}&link2={$link2}
     */
    public function setPlaces($ParseString) {
        parse_str($ParseString, $this->Places);
        $this->Execute();
    }

    /**
     * ****************************************
     * *********** PRIVATE METHODS ************
     * ****************************************
     */
    //Obtém o PDO e Prepara a query
    private function Connect() {
        $this->Conn = parent::getConn();
        $this->Read = $this->Conn->prepare($this->Select);
        $this->Read->setFetchMode(PDO::FETCH_ASSOC);
    }

    //Cria a sintaxe da query para Prepared Statements
    private function getSyntax() {
        if ($this->Places):
            foreach ($this->Places as $Vinculo => $Valor):
                if ($Vinculo == 'limit' || $Vinculo == 'offset'):
                    $Valor = (int) $Valor;
                endif;
                $this->Read->bindValue(":{$Vinculo}", $Valor, ( is_int($Valor) ? PDO::PARAM_INT : PDO::PARAM_STR));
            endforeach;
        endif;
    }

    //Obtém a Conexão e a Syntax, executa a query!
    private function Execute() {
        $this->Connect();
        try {
            $this->getSyntax();
            $this->Read->execute();
            $this->Result = $this->Read->fetchAll();
        } catch (PDOException $e) {
            $this->Result = null;
        }
    }

}
?>