PDO MySQL `prepared statements` to use or not to use placeholders
<p>I'm on a Web Development class. They teach us to connect to MySQL database with PDO then make some methods within a class to access the PDO connection.</p>

<p>db_model.php</p>

<pre><code>&lt;?php
class DB {
    protected $db;
    function __construct() {  
        $this-&gt;db = new PDO("mysql:host=localhost;dbname=blog", "root", "");
    }  

    function executeQuery($query) {
        $statement = $this-&gt;db-&gt;prepare($query);    
        $statement-&gt;execute();
        return $statement;
    }
}
</code></pre>

<p>articles_model.php</p>

<pre><code>&lt;?php
require_once "db_model.php";

class ArticlesModel extends DB {
    function getAll() {
        $statement = $this-&gt;executeQuery("SELECT * FROM articles");
        return $statement-&gt;fetchAll(PDO::FETCH_ASSOC);
    }

    function getArticle($id) {
        $statement = $this-&gt;executeQuery("SELECT * FROM articles WHERE id = " . $id);
        return $statement-&gt;fetchAll(PDO::FETCH_ASSOC);
    }

    function insertArticle($article) {
        $this-&gt;executeQuery("INSERT into articles (title, body, image) values ('".$article["title"]."', '".$article["body"]."', '".$article["file"]."');");
        return $this-&gt;db-&gt;lastInsertId();
    }

    function updateArticle($article) {
        $statement = $this-&gt;executeQuery("UPDATE articles SET title ='".$article["title"]."',body = '".$article["body"]."' WHERE id =".$article["id"]);
        return $statement-&gt;rowCount();    
    }

    function deleteArticle($article) {
        $statement = $this-&gt;executeQuery("DELETE FROM articles WHERE id =".$article["id"]);
        return $statement-&gt;rowCount(); 
    }
}
</code></pre>

<p>I'm far from being an advanced PHP programmer but as much as I know is that the good practice is to use real prepared statements with placeholders not just concatenated PHP variables in SQL statements, so I came with this:</p>

<p>db_model.php</p>

<pre><code>&lt;?php

define('DB_NAME', 'blog');
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_CHAR', 'utf8');

class DB {
        protected $db;
        function __construct() {
                $opt  = array(
                        PDO::ATTR_ERRMODE            =&gt; PDO::ERRMODE_EXCEPTION,
                        PDO::ATTR_DEFAULT_FETCH_MODE =&gt; PDO::FETCH_ASSOC,
                        PDO::ATTR_EMULATE_PREPARES   =&gt; TRUE
                );
                $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHAR;
                $this-&gt;db = new PDO($dsn, DB_USER, DB_PASS, $opt);
        }

        function executeQuery($query) {
                $statement = $this-&gt;db-&gt;prepare($query);
                $statement-&gt;execute();
                return $statement;
        }
}
</code></pre>

<p>articles_model.php</p>

<pre><code>&lt;?php

require_once 'db_model.php';

class ArticlesModel extends DB {
        function getAll() {
                $statement = $this-&gt;executeQuery('SELECT * FROM articles');
                return $statement-&gt;fetchAll(PDO::FETCH_ASSOC);
        }

        function getArticle($id) {
                $statement = $this-&gt;executeQuery('SELECT * FROM articles WHERE id = :id');
                $statement-&gt;bindParam(':id', $id, PDO::PARAM_INT);
                return $statement-&gt;fetchAll(PDO::FETCH_ASSOC);
        }

        function insertArticle($article) {
                $statement = $this-&gt;executeQuery('INSERT into articles (title, body, image) values (:title, :body, :image)');
                $statement-&gt;bindParam(':title', $article['title'], PDO::PARAM_VAR);
                $statement-&gt;bindParam(':body', $article['body'], PDO::PARAM_VAR);
                $statement-&gt;bindParam(':image', $article['file'], PDO::PARAM_VAR);
                return $statement-&gt;lastInsertId();
        }

        function updateArticle($article) {
                $statement = $this-&gt;executeQuery('UPDATE articles SET title = :title, body = :body WHERE id = :id');
                $statement-&gt;bindParam(':title', $article['title'], PDO::PARAM_VAR);
                $statement-&gt;bindParam(':body', $article['body'], PDO::PARAM_VAR);
                $statement-&gt;bindParam(':id', $article['id'], PDO::PARAM_INT);
                return $statement-&gt;fetchColumn();
        }

        function deleteArticle($article) {
                $statement = $this-&gt;executeQuery('DELETE FROM articles WHERE id = :id');
                $statement-&gt;bindParam(':id', $article['id'], PDO::PARAM_INT);
                return $statement-&gt;fetchColumn();
        }
}
</code></pre>

<p>Which method is safer/more correct to use? Am I wrong? BTW, I could have used cleaner code but wanted to keep my code as close as I could to teacher's code. (e.g. not use bindparam() everywhere but <code>execute(array())</code>)</p>

<p>EDIT:</p>

<p>I think properly would be:</p>

<pre><code>&lt;?php

require_once 'db_model.php';

class ArticlesModel extends DB {
        function getAll() {
                $statement = $this-&gt;executeQuery('SELECT * FROM articles');
                return $statement-&gt;fetchAll(PDO::FETCH_ASSOC);
        }

        function getArticle($id) {
                $statement = $this-&gt;executeQuery('SELECT * FROM articles WHERE id = :id');
                $statement-&gt;bindParam(':id', $id, PDO::PARAM_INT);
                return $statement-&gt;fetchAll(PDO::FETCH_ASSOC);
        }

        function insertArticle($article) {
                $params = [
                        ':title' =&gt; $article['title'],
                        ':body' =&gt; $article['body'],
                        ':image' =&gt; $article['file']
                ];
                $statement = $this-&gt;executeQuery('INSERT into articles (title, body, image) values (:title, :body, :image)', $params);
                return $statement-&gt;lastInsertId();
        }

        function updateArticle($article) {
                $params = [
                        ':title' =&gt; $article['title'],
                        ':body' =&gt; $article['body'],
                        ':id' =&gt; $article['id']
                ];
                $statement = $this-&gt;executeQuery('UPDATE articles SET title = :title, body = :body WHERE id = :id', $params);
                return $statement-&gt;fetchColumn();
        }

        function deleteArticle($article) {
                $params = [':id' =&gt; $article['id']];
                $statement = $this-&gt;executeQuery('DELETE FROM articles WHERE id = :id', $params);
                return $statement-&gt;fetchColumn();
        }
}
</code></pre>