Escaping the trademark symbol with MySQL

[eluser]TWP Marketing[/eluser]
I'm stumped. I want to input the trademark symbol (&trade as part of a textarea input string:

Code:

"This is my trademark: TWP Marketing™ or TWP Marketing™."

however I can only write this to the db one time.

I'm using htmlentities () to encode to the db and html_entity_decode() on extraction from the db. This works on such HTML as the paragraph symbols and less-than/more than, etc. but fails for the trademark symbol.

On the first db operation (insert), the text is stored, correctly escaped (phpMyAdmin shows it to be so). When I read that record back into the same textarea input field, the actual trademark symbol is displayed:

"This is my trademark: TWP Marketing™ or TWP Marketing™."

The generated HTML still shows the properly escaped code for the symbol.

If I update that record, the text stored in the db is truncated just before the first trademark symbol:

"This is my trademark TWP Marketing

Can anyone suggest what is happening here?

[eluser]TheFuzzy0ne[/eluser]
How are you updating the database? Can you show some code?

[eluser]Colin Williams[/eluser]
Could be an encoding problem. Are both your database and HTML using UTF-8 encoding?

[eluser]TWP Marketing[/eluser]
Ok, here's my code, I've obviously stripped some lines to compress this... I hope it's enough, I'll expand it if needed. The "Entry" field is the text that is misbehaving.
Please note that I am NOT using form validation.
The controller:

Code:

case 'update_section':
     // read the POSTed fields, encode if needed
     $ds = array(
      'ID'           => $this->input->post('dsID'),
      'Heading'      => htmlentities( $this->input->post('Heading'),ENT_QUOTES ),
      'Entry'        => htmlentities( $this->input->post('Entry'),ENT_QUOTES ),
      'SectionOrder' => $this->input->post('SectionOrder')
     );
     $query = $this->Ugmmodel->update_ds_record(&$ds);  // update single record
     $data['dsID'] = $this->input->post('dsID');        // set ID for get
     $this->Ugmmodel->get_ds_record(&$data);            // get the record
     $this->Ugmmodel->build_view_content(&$data);     // build the CONTENT portion of view
     $this->load->view('ugmdocshell',&$data);           // display view
     break;

The model:

Code:

function update_ds_record(&$ds)
{
  $this->db->where('ID', $ds['ID']);
  $query = $this->db->update('prj_docsections',&$ds);
  return $query;
}

function get_ds_record(&$data)
{
  $this->db->where('ID',$data['dsID']);            // ID matches single records
  $query = $this->db->get('prj_docsections');      // get records meeting Where criteria
  $data['dsCount'] = $query->num_rows();           // get number of records found
  if ( $data['dsCount'] > 0)
  {
   $row = $query->row();
    $data['dsdocID']        = $row->docID;
    $data['dsHeading']      = html_entity_decode( $row->Heading );
    $data['dsEntry']        = html_entity_decode( $row->Entry );
    $data['dsSectionOrder'] = $row->SectionOrder;
   $query->free_result();
   return TRUE;
  }
}

The view:

Code:

<?php
$fdsHeading = array(
'name'      => 'Heading',
'id'        => 'Heading',
'value'     => set_value('Heading',$dsHeading),
'maxlength' => 100,
'size'      => 40
);
$fdsEntry = array(
'name'  => 'Entry',
'id'    => 'Entry',
'value' => set_value('Entry',$dsEntry),
'rows'  => '10',
'cols'  => '60'
);
$fdsSectionOrder = array(
'name'      => 'SectionOrder',
'id'        => 'SectionOrder',
'value'     => set_value('SectionOrder',$dsSectionOrder),
'maxlength' => 3,
'size'      => 3
);
echo form_open('ugm/prjedit'.'/'.$prjID.'/'.$docID.'/'.$dsID );
echo form_hidden('action','update_section');
echo form_hidden('prjID',$prjID);                                  // POST prjID
echo form_hidden('docID',$docID);                                  // POST docID
echo form_hidden('dsID',$dsID);                                    // POST dsID
echo form_hidden('oldSectionOrder',$fdsSectionOrder['value']);     // POST original SectionOrder for comparison
echo 'Section Record ID:';
echo $dsID;
echo 'Display Order:';
echo form_input($fdsSectionOrder);
echo 'Heading:';
echo form_input($fdsHeading);
echo 'HTML Content:';
echo form_textarea($fdsEntry);
echo form_submit('dschangesubmit', 'Update this Section');
echo form_close();
?>

[eluser]TWP Marketing[/eluser]
Colin,Yes
my HTML header is:

Code:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

The DB and all relevant fields use UTF8-unicode-ci collation.

[eluser]Colin Williams[/eluser]
Well, you could start with debugging. (Shocker!) Echo the values at different points and see where the breakdown happens.

[eluser]TWP Marketing[/eluser]
You mean just spraying my keyboard with Raid ® wasn't enough? Ok, I'll dig deeper, might take a while, thanks.

[eluser]Colin Williams[/eluser]
No. The Raid was absolutely necessary.

[eluser]TheFuzzy0ne[/eluser]
I know this is probably me just being over-scrupulous, but is there really any need for this?

Code:

$query = $this->db->update('prj_docsections',&$ds); // <-- dereferencing $ds

I've also noticed this with a few other variables, such as the $data variable you're passing into the view. In theory this shouldn't have any impact, but you never know...

Just out of interest. What happens when you store update the database without first encoding the input?

I doubt this is the cause of your problem, but you never know. Failing that, I think it must be an encoding issue as Colin suggested.

[eluser]Colin Williams[/eluser]
It might just be a case of faulty logic. Something is happening when/where it shouldn't.