A potential mysql server issue?

Question

A PHP function displays the variables, fetching rows from the database. An if rule catches a GET request and would insert values on the table, but it's not what is happening. Which's the problem with the code below? Thanks.

<a href='index.php?add_cart=$pro_id'>
Add to cart
</a>




//rule
if(isset($_GET['add_cart'])){

    global $conn;
    $ip=getIp();
    $pro_id = $_GET['add_cart'];
    $check_pro = "select * from cart where ip_add='$ip' && p_id='$pro_id'";

    $run_check = mysqli_query($conn,$check_pro);

    if(mysqli_num_rows($run_check)>0){
        $insert_pro = "insert into cart (p_id,ip_add,units,size) values ('$pro_id','$ip','1','NULL')";
        $run_pro = mysqli_query($conn,$insert_pro);
        echo "<script>window.open('index.php','_self')</script>";       
    }
    else{
        $insert_pro = "insert into cart (p_id,ip_add,units,size) values ('$pro_id','$ip','1','NULL')";
        $run_pro = mysqli_query($conn,$insert_pro);

        echo "<script>window.open('index.php','_self')</script>";
    }
}

}

Answer 1

You can check for the following code in associate to mysqli prepare and binding parameters.

if (isset($_GET['add_cart'])) {

    $conn = new mysqli('server', 'user', 'password', 'database');

    if (mysqli_connect_errno()) {
        echo 'connection failure: '.mysqli_connect_errno(); 
        exit;
    }

    try {
        // ... 

        /* prepare query statement
           check for existence id + ip at first.
         */
        $query = $conn->prepare(
            'insert into cart (id, ip, units, size) '.
            'select * from (select ?, ?, ?, ?) t '.
            'where (select id from cart where id = ? and ip = ? limit 1) is null'
        );

        /* Binding parameters:
           i : integer type
           s : string type
           d : double type  
         */
        $query->bind_param('issdis', $id, $ip, $units, $size, $id, $ip);

        /* execute query statement and close after execution */

        try {
            $query->execute();
            echo 'Row created: '. $query->affected_rows;

            /* when no row created and want to update the existing row */
            /* if ($query->affected_rows === 0) {
                $query->close();

                $query = $conn->prepare(
                    'update cart '.
                    'set unit = ?, size = ? '.
                    'where id = ? and ip = ?'
                );

               $query->bind_param('sdis', $units, $size, $id, $ip);

               $query->execute();
               echo 'Row updated: '. $query->affected_rows;
            } */

        } catch (\Exception $e) {
            echo $e->getMessage();

        } finally {
            $query->close();
        }

    } finally {
        /* make sure to close the database connection */
        $conn->close();
    }
}

//...

And this is safeMySqli class extends from mysqli class.

class safeMySqli extend mysqli {

    protected static $instances = null;

    public static function create($server, $user,  $password, $database) {
        $class = get_called_class();

        if (is_null(static::$instances[$class])) {
            static::$instances = new $class($server, $user, $password, $database);
        }
       return static::$instances;
    }

    /* execute query and return statement.
        arguments:
            - $query = sql query
            - any query parameters
            eg: $db.execute('select * from gen where id = ? and type = ?', 1, 'OO7');
     */

    public function execute($query) {
        $stmt = $this->prepare($query);

        $args = func_get_args();
        $argsLength = count($args);

        if ($argsLength > 1) {
            $params = [''];

            for ($i = 1; $i < $argsLength; $i++) {
                $val = $args[$i];
                $params[0] = $param[0] . (is_int($val) ? 'i' : (is_double($val) ? 'd' : (is_string($val) ? 's' : 'b')));
                $params[] = &$args[$i];
            }

            call_user_func_array([$stmt, 'bind_param'], $params);
        }

        $stmt->execute();

        return $stmt;
    }
}

Usage:

$db = new safeMySqli('server', 'database', 'user', 'password', 'database');
// or...
$db = safeMySqli::create('server', 'database', 'user', 'password', 'database');

// query execution...
$stmt = $db.execute('select * from gen where id = ? and type = ?', 1, 'OO7');

// get query result
$result = $stmt->get_result();
$rows = $result->fetch_all();

// close resources
$result->close();
$stmt->close();
$db->close();

Edit Correction for correct parameter type checking.