Problem with pound (£) in MySQL AR insert statements

[eluser]Unknown[/eluser]
Using the following line of code, when $strFile includes the British pound symbol (£), I receive the error listed below. Has anyone else experienced this issue?

Code:

Code:

$this->db->insert('names', array('Name' => $strFile));

Result:

Code:

ERROR - 2008-04-09 01:01:34 --> Query error: Incorrect string value: ‘\xA3.java’ for column ‘Name’ at row 1

[eluser]Tom Glover[/eluser]
As far as I know MySQL is not a fan of the £ sign so if you need to store it in there it will need to be in it's html code form, and not the symbol.

[eluser]Unknown[/eluser]
Well, I manually executed

INSERT INTO table SET field='blahblah£.java'

and it worked. Apparently it doesn't like the way CodeIgniter is escaping it with the \xA3.

[eluser]Tom Glover[/eluser]
OK try using the html version of the £ with the CI method and see if that works.

[eluser]_asdf[/eluser]
[quote author="WackyWebs.net" date="1208016706"]OK try using the html version of the £ with the CI method and see if that works.[/quote]
that's not a solution, its a hack. And its based on the presumption that the intended output is an HTML (or SGML) renderer.

(fwiw, I'll try and replicate the issue at hand later to confirm its a CI escape/convert bug)

[eluser]xwero[/eluser]
try

Code:

$this->db->set('Name',$strFile,false);
$this->db->insert('names')
// or
$this->db->insert('names', 'Name='.$strFile);

Both leave the $strFile value untouched.

[eluser]Derek Jones[/eluser]
What is your database character set and collation, and what version of MySQL? I don't have any problems inserting £.

[eluser]_asdf[/eluser]
Alrighty, I've tested against a default Codeigniter 1.6.1 (stable) install, replacing the welcome controller and connecting to a database with the table format:

Code:

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `vc` varchar(255) collate latin1_general_ci default NULL,
  `txt` text collate latin1_general_ci,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

While the DB structure there specifies latin1 general, I've also tested it as utf8 general.

Welcome controller method:

Code:

function index()
    {
        $this->output->enable_profiler(true);
        $this->load->database();

        // how I'd do it
        $this->db->set('vc', '1. blahblah£.java varchar');
        $this->db->set('txt', '1. blahblah£.java blob');
        $this->db->insert('test');

        // Original poster's method
        $this->db->insert('test', array('vc' => '2. blahblah£.java varchar', 'txt' => '2. blahblah£.java blob'));
    }

In both instances, GBP symbol was inserted correctly. In both latin1 and utf-8. with the file saved as both ANSI and utf8 (although the profiler returns the GBP as a questionmark, typical of encoding problems, the data is inserted correctly)

So we need more details: Database collation, the database.php specified collation. The data you're inserting, and any validation/filtering its going through. Is XSS clean enabled? etc.

XML output of the table:

Code:

<char_test>
  &lt;!-- Table test --&gt;
    <test>
        <id>1</id>
        <vc>1. blahblah£.java varchar</vc>
        <txt>1. blahblah£.java blob</txt>
    </test>
    <test>
        <id>2</id>
        <vc>2. blahblah£.java varchar</vc>
        <txt>2. blahblah£.java blob</txt>
    </test>
    <test>
        <id>3</id>
        <vc>1. blahblah£.java varchar</vc>
        <txt>1. blahblah£.java blob</txt>
    </test>
    <test>
        <id>4</id>
        <vc>2. blahblah£.java varchar</vc>
        <txt>2. blahblah£.java blob</txt>
    </test>
</char_test>

[eluser]Max_Power[/eluser]
OK, I am having a similar problem in CI 1.7, too. It doesn't seem to be just putting the the GBP in the database that's an issue:

Code:

CREATE TABLE IF NOT EXISTS `options` (
  `opt` varchar(63) NOT NULL,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY  (`option`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

In codeigniter:

Code:

$data = array(
               'opt' => 'currency' ,
               'value' => '£'
            );

$this->db->insert('options',$data);

This puts in a '?' instead of a '£', even though the last_query() function displays the pound symbol correctly.

When the '£' symbol is retrieved, the active record set causes problems as it turns the '£' into '£'. Very strange.

mySQL version: 5.0.51a-3ubuntu5.4

[eluser]Max_Power[/eluser]
Even stranger - if I submit the value as £, then it is stored in mySQL as £...