CI SQL INSERT ERROR

Question

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Aventro'',''bc55825ec921b78c574576fbc6679cc346ee0494'',''8c0cf560bb77c28d6b7dd11' at line 1

INSERT INTO users(username, password, salt, email, user_created) VALUES(''Aventro'',''bc55825ec921b78c574576fbc6679cc346ee0494'',''8c0cf560bb77c28d6b7dd1135a1a94f2'',''[email protected]'', NOW())

Filename: C:\wamp\www\hol\system\database\DB_driver.php

Line Number: 330

createuser model:

public function createuser($username, $password, $salt, $email)
{
    $password = sha1($password . $salt);

    $sql = "INSERT INTO users(username, password, salt, email, user_created) VALUES('".$this->db->escape($username)."','".$this->db->escape($password)."','".$this->db->escape($salt)."','".$this->db->escape($email)."', NOW())";

    if ($this->db->query($sql)) {
        return true;
    } else {
        return false;
    }
}

Controller (model call)

    $result = $this
    ->user_model
    ->createuser(
        $this->input->post('username'),
        $this->input->post('password'),
        $salt,
        $this->input->post('email'));

Answer 1

Your $this->db->escape() returns string enclosed in single quotes. Use this query instead:

$sql = "INSERT INTO users(username, password, salt, email, user_created) VALUES(".$this->db->escape($username).",".$this->db->escape($password).",".$this->db->escape($salt).",".$this->db->escape($email).", NOW())";

Or remove enclosing from $this->db->escape() - it would be better, I think.

Answer 2

What looks like double quotes in your query are really two apostrophes.

Spaces inserted between them to show: ' 'Aventro' '

Probably your $db->escape method returns a quoted string with apostrophes.

Check your escape function to see if it does that and put the data in the query unquoted.

Answer 3

function fetch_all($table_name,$field_name,$order){
    $query = $this->db->query("select * from {PRE}".$table_name." where 1 order by `".$field_name."` ".$order);

    $result['result_num'] = $query->num_rows();
    $result['result_log'] = $query->result();
    return $result;
}



function fetch_editdata($value,$table_name,$idname){
    $query = $this->db->query("select * from {PRE}".$table_name." where `".$idname."` = '".$value."'");

    $result['result_log'] = $query->row();
    return $result;
}


function edit_data($data,$table_name,$id){
    $i = 1;
    $query = "UPDATE {PRE}".$table_name." SET";
    foreach($data as $key=>$value){
        if($i < count($data)){
            $query = $query . "`".$key."` = '".addslashes(trim($value))."',";
        }else{
            $query = $query . "`".$key."` = '".addslashes(trim($value))."'";
        }
        $i++;
    }
    foreach($id as $k=>$val){
        $query = $query . "WHERE `".$k."` = '".$val."'";
        $insert_id = $val;
    }
    $this->db->query($query);
    return $insert_id;
}


function adddata($value,$table_name){
    $query = "INSERT INTO {PRE}".$table_name." SET";
    $i = 1;
    foreach($value as $key => $val){
        if($i < count($value)){
            $query = $query . "`".$key."` = '".addslashes(trim($val))."',";
        }else{
            $query = $query . "`".$key."` = '".addslashes(trim($val))."'";
        }
        $i++;
    }
    $this->db->query($query);
    $insert_id = $this->db->insert_id();

    return $insert_id;
}