PHP: how use mysqli_escape_string in MVC?

Question

db.php

class dbconnect{
    public function connect(){
         $connection = mysqli_connect($host,$user,$pass,$db); 
         return $connection;
     }
}

cn.php

include 'db.php';
class dao extends dbconnect {
    private $conn; 
    public function __construct() { 
       $dbcon = new parent(); 
       $this->conn = $dbcon->connect();
    }

    public function select( $table , $where='' , $other='' ){
       ...
    }
   }

index.php

include 'cn.php';

if(isset($_POST['login'])){
    $username = $_POST['user_name']; // HOW ESCAPE THIS LINE ?

    $d = new dao();
    $sel = $d->select("users" , ... ) or die('error from here');
    ...
}

How i can escape $username = $_POST['user_name']; before select from database ?

I want connect once to database and use it for ever

Can't you use `mysqli_escape_string()` in your function just before using the value in your query? Though honestly you shouldn't *need* to use `mysqli_escape_string()`. Binding it as a value to the query doesn't require escaping it. Escaping it implies that you're probably doing something else wrong. — David, Sep 12 '18 at 12:33
Explain what, exactly? If you need to use that function, use it. But you really *shouldn't* need to use it if you're properly binding values to queries instead of trying to execute values as code. Perhaps the problem you're encountering would be more clear if you show a more complete example of the problem. Where do you *try* to use that function and how does it fail? — David, Sep 12 '18 at 12:37
This function is not reliable for securing against SQL injection. For example: https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string You prevent SQL injection by *not executing user-modifiable values as code*, not by trying to turn those values into "safe" code before executing them. SQL injection is simple. Don't execute user input as code. For lots more information, see: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — David, Sep 12 '18 at 12:45
Hi, I recommend you move over to PDO, it works much nicer when using the MVC format than MySQLi! — Achmed Zuzali, Sep 16 '18 at 14:46

score 0 · Accepted Answer · edited Sep 17 '18 at 06:20

Here is a good class I just found! It helps do all the database bits for you and uses MySQLi.

https://www.johnmorrisonline.com/simple-php-class-prepared-statements-mysqli/

<?php
if ( !class_exists( 'DB' ) ) {
    class DB {
        public function __construct($user, $password, $database, $host = 'localhost') {
            $this->user = $user;
            $this->password = $password;
            $this->database = $database;
            $this->host = $host;
        }
        protected function connect() {
            return new mysqli($this->host, $this->user, $this->password, $this->database);
        }
        public function query($query) {
            $db = $this->connect();
            $result = $db->query($query);

            while ( $row = $result->fetch_object() ) {
                $results[] = $row;
            }

            return $results;
        }
        public function insert($table, $data, $format) {
            // Check for $table or $data not set
            if ( empty( $table ) || empty( $data ) ) {
                return false;
            }

            // Connect to the database
            $db = $this->connect();

            // Cast $data and $format to arrays
            $data = (array) $data;
            $format = (array) $format;

            // Build format string
            $format = implode('', $format); 
            $format = str_replace('%', '', $format);

            list( $fields, $placeholders, $values ) = $this->prep_query($data);

            // Prepend $format onto $values
            array_unshift($values, $format); 
            // Prepary our query for binding
            $stmt = $db->prepare("INSERT INTO {$table} ({$fields}) VALUES ({$placeholders})");
            // Dynamically bind values
            call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($values));

            // Execute the query
            $stmt->execute();

            // Check for successful insertion
            if ( $stmt->affected_rows ) {
                return true;
            }

            return false;
        }
        public function update($table, $data, $format, $where, $where_format) {
            // Check for $table or $data not set
            if ( empty( $table ) || empty( $data ) ) {
                return false;
            }

            // Connect to the database
            $db = $this->connect();

            // Cast $data and $format to arrays
            $data = (array) $data;
            $format = (array) $format;

            // Build format array
            $format = implode('', $format); 
            $format = str_replace('%', '', $format);
            $where_format = implode('', $where_format); 
            $where_format = str_replace('%', '', $where_format);
            $format .= $where_format;

            list( $fields, $placeholders, $values ) = $this->prep_query($data, 'update');

            //Format where clause
            $where_clause = '';
            $where_values = '';
            $count = 0;

            foreach ( $where as $field => $value ) {
                if ( $count > 0 ) {
                    $where_clause .= ' AND ';
                }

                $where_clause .= $field . '=?';
                $where_values[] = $value;

                $count++;
            }
            // Prepend $format onto $values
            array_unshift($values, $format);
            $values = array_merge($values, $where_values);
            // Prepary our query for binding
            $stmt = $db->prepare("UPDATE {$table} SET {$placeholders} WHERE {$where_clause}");

            // Dynamically bind values
            call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($values));

            // Execute the query
            $stmt->execute();

            // Check for successful insertion
            if ( $stmt->affected_rows ) {
                return true;
            }

            return false;
        }
        public function select($query, $data, $format) {
            // Connect to the database
            $db = $this->connect();

            //Prepare our query for binding
            $stmt = $db->prepare($query);

            //Normalize format
            $format = implode('', $format); 
            $format = str_replace('%', '', $format);

            // Prepend $format onto $values
            array_unshift($data, $format);

            //Dynamically bind values
            call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($data));

            //Execute the query
            $stmt->execute();

            //Fetch results
            $result = $stmt->get_result();

            //Create results object
            while ($row = $result->fetch_object()) {
                $results[] = $row;
            }
            return $results;
        }
        public function delete($table, $id) {
            // Connect to the database
            $db = $this->connect();

            // Prepary our query for binding
            $stmt = $db->prepare("DELETE FROM {$table} WHERE ID = ?");

            // Dynamically bind values
            $stmt->bind_param('d', $id);

            // Execute the query
            $stmt->execute();

            // Check for successful insertion
            if ( $stmt->affected_rows ) {
                return true;
            }
        }
        private function prep_query($data, $type='insert') {
            // Instantiate $fields and $placeholders for looping
            $fields = '';
            $placeholders = '';
            $values = array();

            // Loop through $data and build $fields, $placeholders, and $values         
            foreach ( $data as $field => $value ) {
                $fields .= "{$field},";
                $values[] = $value;

                if ( $type == 'update') {
                    $placeholders .= $field . '=?,';
                } else {
                    $placeholders .= '?,';
                }

            }

            // Normalize $fields and $placeholders for inserting
            $fields = substr($fields, 0, -1);
            $placeholders = substr($placeholders, 0, -1);

            return array( $fields, $placeholders, $values );
        }
        private function ref_values($array) {
            $refs = array();
            foreach ($array as $key => $value) {
                $refs[$key] = &$array[$key]; 
            }
            return $refs; 
        }
    }
}

Your config.php file:

//Your config.php file:
require 'classes/db.php';
$db = new DB('root', 'password here', 'test'); (host is default localhost)

PHP: how use mysqli_escape_string in MVC?

1 Answers1