0

I am still on the process of learning php/mysql. just trying a piece of code to see how it works. the save function that will check if the record exist, if it does not exist, it will do INSERT, but if it do exist, it will do UPDATE. But, when I run the code, it only do INSERT when the record does not exist, but does no do UPDATE when the record do exist. I want someone to examine my code and tell me what is wrong in the code.

function save_inventory(){
        extract($_POST);
        $data = "";
        foreach($_POST as $k =>$v){
            if(!in_array($k,array('id'))){
                if(!empty($data)) $data .=",";
                $v = htmlspecialchars($this->conn->real_escape_string($v));
                $data .= " `{$k}`='{$v}' ";
            }
        }
        $check = $this->conn->query("SELECT * FROM `product_list` where `code` = '{$code}' and delete_flag = 0 ".(!empty($id) ? " and id != {$id} " : "")." ")->num_rows;
        if($this->capture_err())
            return $this->capture_err();
        if($check > 0){
            $resp['status'] = 'failed';
            $resp['msg'] = "Product Code already exists. Code must be unique";
            return json_encode($resp);
            exit;
        }
        if(empty($id)){
            $sql = "INSERT INTO `product_list` set {$data} ";
        }else if(!empty($id)){
            $sql = "UPDATE `product_list` set {$data} where  id = '{$id}' ";
        }
            $save = $this->conn->query($sql);
        if($save){
            $cid = !empty($id) ? $id : $this->conn->insert_id;
            $resp['cid'] = $cid;
            $resp['status'] = 'success';
            if(empty($id))
                $resp['msg'] = "New Inventory successfully saved.";
            else
                $resp['msg'] = " Inventory successfully updated.";
        }else{
            $resp['status'] = 'failed';
            $resp['err'] = $this->conn->error."[{$sql}]";
        }
        // if($resp['status'] == 'success')
        //  $this->settings->set_flashdata('success',$resp['msg']);
            return json_encode($resp);
    }

Code in the mysql query stand for product Id.

Emeka Okoye
  • 23
  • 2
  • Do you know the SQL error? You can use https://www.php.net/manual/en/pdo.errorinfo.php to find out. – JMP Jul 02 '23 at 10:29
  • `I am still on the process of learning php/mysql`...you must be learning from some terrible, out-of-date, poor-quality material then, judging by the state of this code. – ADyson Jul 04 '23 at 11:41
  • **Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unparameterised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. mysqli_real_escape_string is obsolete and doesn't guard against everything – ADyson Jul 04 '23 at 11:41
  • https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use that resource again. – ADyson Jul 04 '23 at 11:41
  • Your usage of `extract` is potentially insecure and even the PHP manual recommends that you don't use it to extract arrays generated by user input - see https://www.php.net/manual/en/function.extract.php . And it should be unnecessary anyway...you can just access the items from $_POST directly whenever you need them, e.g. `$_POST["id"]` etc. – ADyson Jul 04 '23 at 11:43
  • And please bring your error handling into the 21st century. Add `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` before your `mysqli_connect()` (or `new mysqli()`) command, and this will ensure that errors with your SQL queries are reported correctly to PHP automatically. That way you don't need to clutter your script with repetitive code to keep checking errors after every mysqli command. And you should never be echoing error data deliberately - it can easily reveal sensitive info to attackers by accident. – ADyson Jul 04 '23 at 11:43

0 Answers0