0

Controller file

public function update()
    {
    $id= $this->input->post('id');
        $data = array(
                  'dept_name' => $this->input->post('dept_name'),
                  'dept_descr' => $this->input->post('desc')
                );
      $udata = $this->Department_model->update($id,$data);
      //print_r($udata);
        if($udata !== 0)
        {
        echo 'update';
        exit;
        }
        else
        {
         echo 'failed';
         exit;
        }
    }

model file

I am able to pass values from model to stored procedure.

public function update($id,$data)
  {
    $userlevel = $this->db->query("CALL sp_insert_dept('tbl_Department','dept_name,dept_descr,id,meta_create_usr, ','''".$data['dept_name']."'',''".$data['dept_descr']."'',".$id.",1','update',@msg)");
    return $userlevel;
  }

Stored procedure

Using this stored procedure I am able to do insert operation but I am unable to do update operation.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_dept`(IN `tablename` LONGTEXT, IN `column_names` LONGTEXT, IN `col_values` LONGTEXT, IN `action_type` LONGTEXT, IN `col_id` LONGTEXT, OUT `msg` LONGTEXT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered';
    if action_type='insert'
    THEN 
    set @s=CONCAT('insert into ' ,tablename, '(',column_names,') values (',col_values,')');
    PREPARE stmt1 FROM @s;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    set msg='success';
    END IF;

    if action_type='update'
    THEN 
    set @j=CONCAT('update ' ,tablename, 'set ' ,column_names=col_values, ' where id=',col_id);
    PREPARE stmt1 FROM @j;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    set msg='success';
    END IF; 

END$$
DELIMITER ;
  • 3
    Typo. There's no whitespace between your table name and the keyword `set`, resulting in a syntax error in your SQL. I also really question what `column_names=col_values` produces. How have you validated the SQL code that this generates? – David Jan 29 '19 at 19:10
  • 2
    **WARNING**: This code has some severe [SQL injection holes](http://bobby-tables.com/) since none of the values are properly escaped. [CodeIgniter](https://stackoverflow.com/questions/14156421/how-can-i-use-prepared-statements-in-codeigniter) supports placeholder values which remove the need for manual escaping. Combining SQL in a prepared statement is likewise **extremely risky** and should be avoided. – tadman Jan 29 '19 at 19:13
  • 6
    How does this code benefit at all from having stored procedures? In practice these are way more trouble than they're worth for simple insert/update wrappers. Use the CodeIgniter ORM instead. – tadman Jan 29 '19 at 19:14

0 Answers0