-1

I am creating a class for connecting to my database but I keep getting an message saying my sql syntax has an error, when echoing the query i get SELECT id, username from :table where :row = :value and it seems fine with no errors.

<?php 
    class db{
        protected $datab;
        public function __construct($username, $password, $host, $dbname, $options){
            try { 
                $this->datab = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); 
            } 
            catch(PDOException $ex) { 
                die("Failed to connect to the database: " . $ex->getMessage()); 
            }
            $this->datab->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
            $this->datab->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); 
        }
        public function select($array, $table, $row, $value){
            $query = "SELECT";
            foreach($array as $val) {

                if ($val === end($array)){
                    $query.= " ".$val;
                    }else{
                    $query.= " ".$val.",";
                }
            }
            $query.=" FROM :table WHERE :row = :value";
            $query_params = array(
            ':table' => $table,
            ':row' => $row,
            ':value' => $value
            ); 
            echo $query; // SELECT id, username from :table where :row = :value
            try{ 
                $stmt = $this->datab->prepare($query); 
                $result = $stmt->execute($query_params); 
            } 
            catch(PDOException $ex) { 
                die("Failed to run query: " . $ex->getMessage()); 
            }
            $row = $stmt->fetch(); 
            return $row;
        }
    }
    $kit = new db("root", "", "localhost", "kit", array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
    $kit->select(array("id", "username"), "user", "username", "yusaf");

?>

Error message:

Failed to run query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''user' where 'username' = 'yusaf'' at line 1

EDIT

I got a downvote please comment why you have given me this. I guess I should have mentioned this is only for learning purposes I'm not actually going to use this class in an application it's pointless.

Yusaf Khaliq
  • 3,333
  • 11
  • 42
  • 82

3 Answers3

1

You cannot use :table or :row as a substitution value, you have to know the name of the tables and columns.

SELECT id, username from :table where :row = :value

Should be

SELECT id, username from tablename where columnname = :value

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
0

This select() function of yours is utterly useless.

Look, you are trying to save yourself TWO words, FROM and WHERE. In exchange you are losing both flexibility and readability of SQL. Too much a price for a questionable syntax sugar. What if you need order by? join? complex where? Not to mention you are actually obfuscating plain and readable SQL. Having such a unction is a mere nonsense.

What it have to be:

   public function getRow($query, $params){
        $stmt = $this->datab->prepare($query); 
        return $stmt->execute($params)->fetch(); 
    }

So it can be used as

$row = $kit->getRow("SELECT id, username FROM user WHERE username =?", array("yusaf"));
Community
  • 1
  • 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
-1

The substitution replaces :table and :row with quoted values, resulting in :

SELECT id, username FROM 'user' WHERE 'username' = 'yusaf'

when it should be

SELECT id, username FROM user WHERE username = 'yusaf'

Change this line :

$query.=" FROM :table WHERE :row = :value";

to :

$query.=" FROM `$table` WHERE `$row` = :value";
Kethryweryn
  • 639
  • 4
  • 11
  • 1
    This solution is vulnerable to SQL injection – Your Common Sense Aug 02 '13 at 08:46
  • PDO takes care of SQL injection for values, you have to sanitize your tables and columns names yourself if you want to use dynamic data. Obviously if the table names of columns come from client data, this code would be vulnerable if you didn't sanitize the variables, but there is no indication here that it is the case. – Kethryweryn Aug 02 '13 at 09:14
  • A solution is either invulnerable or not. Yours is not. That's all. Excuses are good for talking, not for protection. – Your Common Sense Aug 02 '13 at 10:35
  • As mentionned in http://stackoverflow.com/questions/13405392/pdo-bindparam-issue/13406590#13406590 , you have to make sure your data is sanitized if you want to use dynamic table and column names. There is no wrapper doing that for you in PDO. Please don't vote if you don't understand PHP, thank you. – Kethryweryn Aug 07 '13 at 08:47