I've started to learn PHP classes, interfaces, extends and things related to those.
Before this i've worked only with functions and it started to be quite a mess with hundreads of separate functions.
What i'm trying achieve is the following:
Html table, which has 25 products listed. Each product has its quantity and price fields. Some fields allow price to be input and some allows only amount.
Session cart where the products are updated as the user changes values in html table. (Jquery + AJAX)
Calculations class, which would be used to create different calculation formulas based on product (Some are calculated per person price * amount and some has base prices and other calculations)
Database tools class, which would be used to retrieve needed prices from database.
That would give the basic idea of what i'm doing. The actual code is related to the Database classes, which are used to handle database queries and connections.
Database Connection Class
class DatabaseConnection {
protected $db_user = '';
protected $db_pass = '';
protected $db_dsn = 'mysql:host=localhost;dbname=';
protected $options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING,
);
public function dbconnect(){
try {
$db = new PDO($this->db_dsn, $this->db_user, $this->db_pass, $this->options);
}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
exit;
}
return $db;
}
}
Database tools class
class dbTools {
private $db;
private $verifiedTable;
private $verifiedColumn;
private $verifiedItemId;
function __construct() {
$this->db = new DatabaseConnection();
$this->db = $this->db->dbconnect();
}
public function validateValues($table, $column, $itemId) {
// Verify that table exists
$verifiedTable = $this->verifyTable($table);
$this->verifiedTable = $verifiedTable;
// Verify that column exists
$verifiedColumn = $this->verifyColumn($column, $this->verifiedTable);
$this->verifiedColumn = $verifiedColumn;
// Verify that id exists
$verifiedItemId = $this->verifyItemId($this->verifiedTable, $itemId);
$this->verifiedItemId = $verifiedItemId;
}
private function verifyTable($table) {
$result = $this->db->query("show tables");
$final = $result->fetchAll(PDO::FETCH_COLUMN);
if(in_array($table, $final)) {
$verifiedTable = $table;
} else {
throw new exception('Table doesn´t exists');
}
return $verifiedTable;
}
private function verifyColumn($column, $verifiedTable) {
$result = $this->db->query('SHOW COLUMNS FROM '.$verifiedTable.'');
$columns = $result->fetchAll(PDO::FETCH_COLUMN);
if(in_array($column, $columns)) {
$verifiedColumn = $column;
} else {
throw new exception('Column doesn´t exist');
}
return $verifiedColumn;
}
private function verifyItemId($verifiedTable, $itemId) {
$result = $this->db->query('SELECT id FROM '.$verifiedTable.'');
$ids = $result->fetchAll(PDO::FETCH_COLUMN);
if(in_array($itemId, $ids)) {
$verifiedItemId = $itemId;
} else {
throw new exception('Id doesn´t exist');
}
return $verifiedItemId;
}
public function getItem() {
$statement = 'SELECT '.$this->verifiedColumn.' FROM '.$this->verifiedTable.' WHERE id = :id';
$query = $this->db->prepare($statement);
$query->bindParam(':id', $this->verifiedItemId);
$query->execute();
$row = $query->fetchColumn();
return $row;
}
}
Usage
$dbTools = new dbTools();
$dbTools->validateValues('table','column', 'id');
echo $dbTools->getItem();
I've tried to protect my queries from sql injections. For example. with verifyTable() method i'm first retrieving tables from database and creating array from those. Then i'm testing the unknown value against that array. If the value is found = the unknown value is safe.
Is this correct approach and is the above class safe from sql injections?
I would appreciate all help and comments. Thanks