CODEWITHSUNDEEP
phpcodeignitermysql-error-1064
bart_88
bart_88

Reputation: 488

Codeigniter : semicolon breaking query

I have a SQL file I am trying to import into my CodeIgniter application with the following method : 

public function run_backup($filename) // full extension please
    {
        // read the file in
        // make sure that filename is a string
        $filename = (string) $filename;

        $backup = $this->load->file($filename, true);

        $file_array = explode(';', $backup); // explode on semicolon
        foreach($file_array as $query)
        {
           $this->db->query("SET FOREIGN_KEY_CHECKS = 0");
           $this->db->query($query);
           $this->db->query("SET FOREIGN_KEY_CHECKS = 1");
        }
}

I am getting the following error about half way through the sql file:

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 ''Mistakes with high risk medicines are much more common than with any other medi' at line 1

INSERT INTO quiz_question_content (`quiz_question_content_id`, `quiz_question_id`, `content`, `image_path`, `type`) VALUES (247, 235, 'Mistakes with high risk medicines are much more common than with any other medicines used

I searched for the line and found that there is a semicolon in the text field : 

'Mistakes with high risk medicines are much more common than with any other medicines used; hence the reason they require special safeguards'

The semicolon is terminating the line and create a malformed query as my method is splitting based on ';'

Is there anyway I can ignore this semicolon ? Even an escaped semicolon has a semicolon in it &#59;. The SQL file is quite large and the person has placed unescaped characters into text fields.

Upvotes: 1

Views: 1340

Answers (2)

niraj rahi
niraj rahi

Reputation: 57

There is nothing wrong with semicolon. There must be single/double inverted comma in you data due to which this error occurs.

Try to escape data with mysql_real_escape_string() function. This will help you.

Upvotes: 0

xelber
xelber

Reputation: 4637

How many quires are we talking about? I'd rather use row mysql functions to do multiple quires. For example http://php.net/manual/en/mysqli.multi-query.php

Upvotes: 1

Related Questions