CODEWITHSUNDEEP
phpcodeigniterinsertescaping
SheedySheedySheedy
SheedySheedySheedy

Reputation: 536

Codeigniter active records insert query with single quotes

I am trying to add a single record to a database table using codeigniter active records. The original data is parsed from XML and I have tested that the data is stored present and correct in its respective variable names.

The data is passed from the controller to the view like this:

$data['questionId'] = $question['id'];
$data['answerText'] = $question->answer;
$q = $this->mobile_user_model->save_actual_answers($data);

And the model insert function looks like this:

function save_actual_answers($data) {
    $insert = array(
        'answerText' => $data['answerText'],
        'responseId' => $data['responseId'],
        'questionId' => $data['questionId']
    );
    $q = $this->db->insert('actual_answers', $insert);
    return $q;
}

The problem I am having is that codeigniter is not automatically adding single quotes to the string value supplied for answerText. When I run the function, I get this error:

Error Number: 1054
Unknown column 'Dan' in 'field list'
INSERT INTO `actual_answers` (`answerText`, `responseId`, `questionId`)
VALUES (Dan, 12, 7)

This tells me that the value for answerText (in this case the value Dan) is trying to be entered into the table by codeigniter without the single quotes. However, if I hardcode the single quotes myself and append them to the answerText data, like so:

'answerText' => "'" . $data['answerText'] . "'"

the query runs successfully, but the single quotes are added with the string into the table, so the record in the database looks like this:

'Dan'    12    7

Has anybody come across this problem before, or am I doing something drastically wrong which I can't see?

Upvotes: 1

Views: 10157

Answers (2)

SheedySheedySheedy
SheedySheedySheedy

Reputation: 536

Thanks for the answer. The reason that the code is like that is because the id is an attribute of question and the answer is child element of question in the XML. It is correct. I actually fixed it like this:

$insert = array(
        'answerText' => (string)$data['answerText'], // Needed to declare it was a string 
        'responseId' => $data['responseId'],
        'questionId' => $data['questionId']
    );

It seems like a hack to me, but it worked. The thing is, I have used this insert array in other parts of the project for inserting strings without this declaration and it works fine. I am not sure why in this case it didn't.

Upvotes: 3

Jasonw
Jasonw

Reputation: 5064

$data['questionId'] = $question['id'];
$data['answerText'] = $question->answer;

These two lines seem questionable to me. First line, $question is an array and onto the second line, question is an object getting the property answer?! Are you sure you do not get any php error in the log?

Also, in the active record insert documentation, it is stated very clearly Note: All values are escaped automatically producing safer queries. You should really find out if you have did any modification because from the question, there is not much to guess from. I think you should quote using the library escape function rather than quote yourself. Something like

$insert = array(
      'answerText' => $this->db->escape_str(data['answerText']),
      'responseId' => $data['responseId'],
      'questionId' => $data['questionId']
);

Upvotes: 3

Related Questions