php - How to use variables in a SQL query -
i wrote class database.php:
class database { private $host; private $dbusername; private $dbpassword; private $connection; private $iv; public function __construct($host, $dbusername, $dbpassword, $iv) { $this->dbpassword = $dbpassword; $this->dbusername = $dbusername; $this->host = $host; $this->iv = $iv; } public function createdatabase($dbname){ $this->connection = mysqli_connect($this->host, $this->dbusername, $this->dbpassword); $query = "create database if not exists $dbname"; if(!$this->connection){ var_dump("connection failed"); } else { $this->connection->prepare($query)->execute(); } $this->connection->close(); } public function createtable($query, $dbname){ $this->connection = mysqli_connect($this->host, $this->dbusername, $this->dbpassword, $dbname); if(!$this->connection){ var_dump("connection failed"); } else { $this->connection->prepare($query)->execute(); } $this->connection->close(); } public function getconnection(){ $this->connection = mysqli_connect($this->host, $this->dbusername, $this->dbpassword); return $this->connection; } public function executequery($dbname, $query){ $this->connection = mysqli_connect($this->host, $this->dbusername, $this->dbpassword, $dbname); if(!$this->connection){ var_dump("connection failed"); return false; } else{ $this->connection->prepare($query)->execute(); $this->connection->close(); return true; } } public function deletefromtable($dbname, $query){ $this->connection = mysqli_connect($this->host, $this->dbusername, $this->dbpassword, $dbname); if(!$this->connection){ var_dump("connection failed"); return false; } else{ $this->connection->prepare($query)->execute(); $this->connection->close(); return true; } } public function check($query){ $this->connection = mysqli_connect($this->host, $this->dbusername, $this->dbpassword, "portal");; $statement = $this->connection->prepare($query); $statement->execute(); $statement->store_result(); if($statement->num_rows != 0){ $this->connection->close(); return true; } else { $this->connection->close(); return false; } } public function getid($username){ $this->connection = mysqli_connect($this->host, $this->dbusername, $this->dbpassword, 'portal'); $id = mysqli_fetch_all(mysqli_query($this->connection, "select id users username='$username'")); $this->connection->close(); return $id[0][0]; } public function getdata($query, $name = null){ $this->connection = mysqli_connect($this->host, $this->dbusername, $this->dbpassword, 'portal'); $statement = $this->connection->prepare($query); $statement->execute(); $data = $statement->get_result()->fetch_array(); if($name != null) { return $data[$name]; } else{ return $data; } } public function getdataasarray($myquery){ $this->connection = mysqli_connect($this->host, $this->dbusername, $this->dbpassword, 'portal'); $query = mysqli_query($this->connection, $myquery); $results = array(); while($line = mysqli_fetch_array($query)){ $results[] = $line; } return $results; } public function encryptssl($data){ $encryptionmethod = "aes-256-cbc"; $secrethash = ""; $encryptedmessage = openssl_encrypt($data, $encryptionmethod, $secrethash, 0, $this->iv); return $encryptedmessage . '||' . $this->iv; } public function decryptssl($data, $iv){ $encryptionmethod = "aes-256-cbc"; $secrethash = ""; $decryptedmessage = openssl_decrypt($data, $encryptionmethod, $secrethash, 0, $iv); return $decryptedmessage; } } and i'm using following in code select, update, delete entries database:
$customerinfo = $database->getdata("select * customers id='$id'"); $database->executequery('portal', "insert messages (userid, message, customerid, messageread, messagetrash, messagedeleted, time_added, subject) values( '$id', '$message', '$customerid', 0, 0, 0, '$time_date', '$messagesubject')"); but many probally know not safe sql injection. binding parameters :id possiblity don't know how can within class. if want have 1 function multiple different querys example: 1 query 1 variable or 1 query multiple variables above 2 queries
can me out issue?
it's never idea use variables directly in queries without first escaping/treating them. if do, use php's 'bif' mysqli_real_escape_string($var) escape them.
in code can like:
$customerinfo = $database->getdata(sprintf("select * customers id='%d'", mysqli_real_escape_string($id))); $database->executequery('portal', sprintf("insert messages (userid, message, customerid, messageread, messagetrash, messagedeleted, time_added, subject) values('%d', '%s', '%s', 0, 0, 0, '%s', '%s')", mysqli_real_escape_string($id), mysqli_real_escape_string($message), mysqli_real_escape_string($customerid), mysqli_real_escape_string($time_date), mysqli_real_escape_string($messagesubject))); here's way of doing using strtr:
$placeholders = array( ':id' => mysqli_real_escape_string($id), ':message' => mysqli_real_escape_string($message), ':customerid' => mysqli_real_escape_string($customerid), ':time_date' => mysqli_real_escape_string($time_date), ':messagesubject' => mysqli_real_escape_string($messagesubject), ); $database->executequery('portal', strtr("insert messages (userid, message, customerid, messageread, messagetrash, messagedeleted, time_added, subject) values(':id', ':message', ':customerid', 0, 0, 0, ':time_date', ':messagesubject')", $placeholders));
Comments
Post a Comment