Prepared statement PHP OOP

Question

Working on a product adding page using PHP OOP and mysqli, at this point i'm working on prepared statement implementation but can't find information on how to add them into such code.

Any guidlines will be apreciated.

Code:

DB connection class:

<?php
class DbConfig {    
    private $_host = 'localhost';
    private $_username = 'root';
    private $_password = 'falcons17';
    private $_database = 'scandiweb';

    protected $connection;

    public function __construct()
    {
      if (!isset($this->connection)) {
        $this->connection = new mysqli($this->_host, $this->_username, $this->_password, $this->_database);
        if (!$this->connection) {
          echo 'Cannot connect to database server';
          exit;
        }           
      } 

      return $this->connection;
    }
}
?>

Execute function:

public function execute($query) {
  $result = $this->connection->query($query);
  if ($result == false) {
    echo mysqli_error($this->connection); /*'Error: cannot execute the command'*/
    return false;
  } else {
    return true;
  }
}

Validating and adding procedure :

 <?php
//including the database connection file
include_once("classes/Crud.php");
include_once("classes/Validation.php");

$crud = new Crud();
$validation = new Validation();    
if(isset($_POST['Submit'])) {   
    $sku = $crud->prepare_string($_POST['sku']);
    $name = $crud->prepare_string($_POST['name']);
    $price = $crud->prepare_string($_POST['price']);
    $products = $crud->prepare_string($_POST['products']);
    $weight = $crud->prepare_string($_POST['weight']);
    $capacity = $crud->prepare_string($_POST['capacity']);
    $height = $crud->prepare_string($_POST['height']);
    $width = $crud->prepare_string($_POST['width']);
    $length = $crud->prepare_string($_POST['length']);

    $check_int = $validation->is_int($_POST, array('price','weight','capacity','height','width','length'));    
    if ($check_int != null){
      echo $check_int;
    }else {
      $result = $crud->execute("INSERT INTO products(sku,name,price,product_type,weight,capacity,height,width,length) VALUES('$sku','$name','$price','$products','$weight','$capacity','$height','$width','$length')");

      //display success message
      echo "<font color='green'>Data added successfully.";
      echo "<br/><a href='index.php'>View Result</a>";
    }
  }
?>

Have you had a look at the [documentation](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php)? Which of these parts are unclear? — Nico Haase, Jun 22 '18 at 12:18
return in __construct is useless and __construct shouldnt be used to get connection outside when its protected — Eakethet, Jun 22 '18 at 12:18
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Jun 22 '18 at 12:21
It would help if you described exactly what you issue/error is — RiggsFolly, Jun 22 '18 at 12:22
`new mysqli(...` returns an object, not a `boolean`. Try checking it first - http://php.net/manual/en/mysqli.construct.php#refsect1-mysqli.construct-examples — waterloomatt, Jun 22 '18 at 12:22
What is `prepare_string`? If it's escaping data then I strongly suggest you stop doing that and instead use prepared/parameterized queries. If you want a db class approach you might prefer using PDO over Mysqli so I suggest giving it a spin — JimL, Jun 22 '18 at 12:24
Listen to @JimL. Very specifically, constructing a string to make a query, as you have done, is a major security issue. Please read about SQL injection attacks, and use parameterised queries. — Dragonthoughts, Jun 22 '18 at 12:57

score 0 · Accepted Answer · answered Jun 22 '18 at 13:45

Here is a class i often use. It gets the login from a separate .ini-file. Feel free to change for your needs. Keep in mind that it sets associative arrays as standard.

class CC_DBV {

private static $mysqlhost; 
private static $mysqluser; 
private static $mysqlpwd; 
public static $mysqldb;
private static $db;
private static $mysqlport;


function __construct() {


    // ini File einlesen
    $globSettings = parse_ini_file(_ROOTV_.'cfg/main.ini',true);

    // Datenbankverbindung
    self::$mysqlhost = $globSettings ['db_settings']['host'];                   // MySQL-Host aus Config Datei
    self::$mysqluser = $globSettings ['db_settings']['db_user'];        // MySQL-User aus Config Datei
    self::$mysqlpwd = $globSettings ['db_settings']['db_pswd'];         // Passwort aus Config Datei
    self::$mysqldb = $globSettings ['db_settings']['db'];                       // Datenbank aus Config Datei
    self::$mysqlport = $globSettings ['db_settings']['port'];                   // Datenbank aus Config Datei

}

public function getInstance( ) { 
        if(!self::$db) { 

            try{

                self::$db = new \PDO( 
                        'mysql:host='.self::$mysqlhost.';dbname='.self::$mysqldb.';port='.self::$mysqlport, 
                        self::$mysqluser, 
                        self::$mysqlpwd, 
                        array( 
                                \PDO::ATTR_PERSISTENT => true, 
                                \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION ,
                                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                                \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"
                        ) 
                ); 
                //self::$db->exec("SET NAMES utf8"); 

                return self::$db; 

            }catch (\PDOException $e){
                echo 'Connection failed: ' . $e->getMessage();
            }

        }else{
            return self::$db;
        }

}

}

To use it you do something like this:

$conn = new CC_DBV(); // you can use this connection multiple times, it's persistant.
$inst = $conn->getInstance(); // create or get the instance of the opened connection
$stmnt = $inst->prepare("SELECT * FROM tablename WHERE xyz = :VAR1"); // prepare statement with placeholder(s)
$v = array(':VAR1' => 'aValue'); // set up an array with corresponding values
$r1 = $stmnt->execute($v); // execute statement with values
if(!$r1){ echo "PANICMODE";}else{ var_dump($stmnt->fetchAll()));

This is PDO, not mysqli. Granted, PDO has my preference as well. — Xorifelse, Jun 22 '18 at 14:18
yeah that's right. But to be honest - it's 2018. Mysqli should be gone, even with OOP :) — C4pt4inC4nn4bis, Jun 22 '18 at 15:52
Sadly it's not how SO works. Question ~> resolving answer. This might have been tagged as the answer but trust me that this is an exception. 'Knoop dat tussen je oren' , as we say in Dutch as it will help you in the future. — Xorifelse, Jun 22 '18 at 20:53
I admit - missread his question as an "how can i use prepared statements & OOP". Sorry & Bedankt voor je advies! — C4pt4inC4nn4bis, Jun 23 '18 at 08:45

Prepared statement PHP OOP

1 Answers1