CODEWITHSUNDEEP
phpmysqlpdo
Riketh
Riketh

Reputation: 37

Setting PDO::ATTR_EMULATE_PREPARES to false not working

I've tried disabling emulated prepares in PDO but I cannot get it to work. Everything else works. The query is successful. The reason I believe it's not working is because it does not escape quotes and such so I get syntax errors.

I've tried doing it two different ways. 

$this->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

and

$insert = $database->$con->prepare($insert, array(PDO::ATTR_EMULATE_PREPARES => false));

I've also noticed that getAttribute does not work.

By doing this...

    $emul = $database->$con->getAttribute(PDO::ATTR_EMULATE_PREPARES);
    var_dump($emul);

...I get this error

SQLSTATE[IM001]: Driver does not support this function: driver does not support that attribute

And here's my database class where the action happens. (I might have left some unneccessary/stupid code in there while I was testing.)

<?php
class Database
{
    public $dbh;
    public $dbh1;
    public $dbh2;
    private static $instance;

    public $numResults;
    private $result = array();          // Results that are returned from the query

    public function __construct()
    {
        try
        {
            $this->dbh = new PDO(DB_TYPE.':host='.DB_HOST.';dbname='.DB_NAME.';charset=utf8', DB_USER, DB_PASS);
            $this->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            $this->dbh1 = new PDO(DB_TYPE1.':host='.DB_HOST1.';dbname='.DB_NAME1.';charset=utf8', DB_USER1, DB_PASS1);
            $this->dbh1->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $this->dbh1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            $this->dbh2 = new PDO(DB_TYPE2.':host='.DB_HOST2.';dbname='.DB_NAME2.';charset=utf8', DB_USER2, DB_PASS2);
            $this->dbh2->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $this->dbh2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        catch (PDOException $e)
        {
            die("Database Error: ". $e->getMessage() . "<br />");

        }
    }

    public static function getInstance()
    {
        if (!isset(self::$instance))
        {
            $object = __CLASS__;
            self::$instance = new $object;
        }
        return self::$instance;
    }

    private function tableExists($table, $con)
    {
        switch($con)
        {
            case 'dbh':
            $db_name = DB_NAME;
            break;
            case 'dbh1':
            $db_name = DB_NAME1;
            break;
            case 'dbh2':
            $db_name = DB_NAME2;
            break;
        }

        $database = Database::getInstance();

        if(is_array($table))
        {
            for($i = 0; $i < count($table); $i++)
            {
                $tablesInDb = $database->$con->prepare('SHOW TABLES FROM '.$db_name.' LIKE "'.$table[$i].'"');
                $tablesInDb->execute();
                $rowCount = $tablesInDb->rowCount();

                if($tablesInDb)
                {
                    if($rowCount <> 1)
                    {
                        die('Error: Table does not exist'.$table[$i]);
                    }
                }
            }
        }else
        {
            $tablesInDb = $database->$con->prepare('SHOW TABLES FROM '.$db_name.' LIKE "'.$table.'"');
            $tablesInDb->execute();
            $rowCount = $tablesInDb->rowCount();

            if($tablesInDb)
            {
                if($rowCount <> 1)
                {
                    die('Error: Table does not exist'.$table);
                }
            }
        }

        return true;
    }

    public function insert($con, $table, $values, $cols = null)
    {
        if($this->tableExists($table, $con))
        {
            $insert = 'INSERT INTO '.$table;

            if($cols != null)
            {
                $cols = implode(',', $cols);
                $insert.= '('.$cols.')';
            }

            for($i = 0; $i < count($values); $i++)
            {
                if(is_string($values[$i]))
                    $values[$i] = "'".$values[$i]."'";
            }

            $values = implode(',', $values);
            $insert .= ' VALUES ('.$values.')';

            $database = Database::getInstance();
            $insert = $database->$con->prepare($insert, array(PDO::ATTR_EMULATE_PREPARES => false));
            $insert->execute();

            if($insert)
            {
                return true;
            }else
            {
                return false;
            }
        }
    }

    public function getResult()
    {
        return $this->result;
    }
}

?>

Upvotes: 1

Views: 3707

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157872

  1. As manual states, getAttribute() don't support ATTR_EMULATE_PREPARES
  2. There shouldn't be no escaping with native prepares at all.
  3. To check if you are in emulation mode or not you can use LIMIT clause with lazy binding. It will raise an error if emulation is on.
  4. Your main problem is whatever "syntax error" you mentioned and you have to solve it first.
  5. As Álvaro G. Vicario noted in comments, you are not using prepared statements. It is apparently the root of the problem. PDO doesn't "escape" your data by itself. It can do it only if you are using placeholders to represent your data in the query. You can read more here

Upvotes: 1

Related Questions