Coming back for another look at my project after I've reworked a few of the key points made in my previous post here.
So far, I've rewritten most of my code to address SQL Injection. I've created a class called DBManager to connect to my database and then create prepared statements and execute them.
Right now there is some duplication as I will be adding in some error logging in the near future.
The focus here should be on Security of my code. That being said, I'm not opposed to anything you have to say.
DBManager
private $db;
//create database connection - find constructive way to log errors without database if connect fails
public function __construct() {
$this->db = new PDO('mysql:dbname='.getenv('DB_DATABASE').';host='.getenv('DB_HOST'),getenv('DB_USERNAME'), getenv('DB_PASSWORD'));
date_default_timezone_set('America/New_York');
$this->connectErrors();
}
//custom query generator - calls queryErrors to execute
//helper function to only expose what's necessary
public function query($sql,$bindings,$queryEnum) {
$result = $this->queryErrors($sql,$bindings,$queryEnum);
return $result;
}
//calls createPreparedStatement to execute, logs to database based on result
//delete and update not converted, but shows echo logging for some of the data that would be logged
private function queryErrors($sql,$bindings,$queryEnum) {
if($queryEnum == 1) {
//select
$result = $this->createPreparedStatement($sql,$bindings);
}
else if($queryEnum == 2) {
//insert
$result = $this->createPreparedStatement($sql,$bindings);
}
else if($queryEnum == 3) {
//delete
if(!$result = $this->db->query($sql)) {
echo "Sorry, the website is experiencing technical difficulties.";
echo "Error: Our query failed to execute and here is why: \n";
echo "Delete Query: " . $sql . "\n";
echo "Errno: " . $this->db->errno . "\n";
echo "Error: " . $this->db->error . "\n";
return null;
}
}
else if($queryEnum == 4) {
//update
if(!$result = $this->db->query($sql)) {
echo "Sorry, the website is experiencing technical difficulties.";
echo "Error: Our query failed to execute and here is why: \n";
echo "Update Query: " . $sql . "\n";
echo "Errno: " . $this->db->errno . "\n";
echo "Error: " . $this->db->error . "\n";
return null;
}
}
else {
//unknown
$result = null;
}
return $result;
}
//connect errors currently echo to show that something went wrong
//likely return to error view to contact someone for assistance
private function connectErrors() {
if (mysqli_connect_errno()) {
//insert into errors table
echo 'Error: Could not connect to the database.';
echo "Errno: " . $this->db->errorCode() . "\n";
echo "Error: " . array_values($this->db->errorInfo()) . "\n";
}
}
//dynamic binding of parameters
private function createPreparedStatement($sql,$bindings) {
$stmt = $this->db->prepare($sql);
if($stmt === false) {
echo 'Wrong SQL: invalid prepare statement';
}
$params = array();
for($i = 0; $i < count($bindings); $i++) {
$params[] = & $bindings[$i];
}
$result = $stmt->execute($params);
if($result === false) {
return null;
}
return $stmt->fetchAll();
}
This class is then connected in my PhishingController where I have a few methods (still a few redundancies) written to track the email or website connection.
PhishingController
//coming from the route, checks for valid user and calls the webbug
public function webbugEmailRedirect($id) {
$urlId = substr($id,0,15);
$db = new DBManager();
$sql = "SELECT USR_Username FROM gaig_users.users WHERE USR_UniqueURLId=?;";
$bindings = array($urlId);
$bindingTypes = array('s');
$result = $db->query($sql,$bindings,QueryEnum::SELECT);
print_r(array_values($result));
$username = $result[0]['USR_Username'];
//check if user exists
$this->webbugExecutionEmail($urlId,$username);
}
//base execution where duplicate methods get called
private function webbugRootExecution($urlId,$strLocation) {
$db = new DBManager();
if(!empty($_SERVER['REMOTE_ADDR'])) {
$ip = $_SERVER['REMOTE_ADDR'];
$host = gethostbyaddr($_SERVER['REMOTE_ADDR']);
$reqpath = $_SERVER['REQUEST_URI'];
$projectID = substr($reqpath, $strLocation);
$projectID = ltrim($projectID, '0');
$projectID = rtrim($projectID, '.');
$projectID = intval(strval($projectID));
$sql = "SELECT PRJ_ProjectName FROM gaig_users.projects WHERE PRJ_ProjectId=?;";
$bindings = array($projectID);
$result = $db->query($sql,$bindings,QueryEnum::SELECT);
print_r(array_values($result));
$projectName = $result[0]['PRJ_ProjectName'];
$date = date("Y-m-d");
$time = date("H:i:s");
return array($ip,$host,$projectName,$date,$time);
}
return null;
}
//email specific execution
private function webbugExecutionEmail($urlId,$username) {
$db = new DBManager();
$data = $this->webbugRootExecution($urlId,29);
print_r(array_values($data));
if(!is_null($data)) {
$sql = "INSERT INTO gaig_users.email_tracking (EML_Id,EML_Ip,EML_Host,EML_Username,EML_ProjectName,
EML_AccessDate,EML_AccessTime) VALUES (null,?,?,?,?,?,?);";
$bindings = array($data[0],$data[1],$username,$data[2],$data[3],$data[4]);
$db->query($sql,$bindings,QueryEnum::INSERT);
}
}
As a reminder, I'm still testing most of this so I have echos and print_rs seeded throughout my methods to make sure that data is being passed and presented as it should be.
Employee.objects.filter(<sql query filters>)All lazily evaluated too. Python is much easier to write and PHP is prone to security problems by nature. \$\endgroup\$