problem inputting data that has a single quote

[Smudly]

Hi, I'm trying to type in a name of a song into an input field, for example:

I'll Be Missing you

 

This field is captured through $_POST and set to a variable $title

 

I then update the table with this new title. Once it is updated, all that is shown in the data is:

 

I

 

The single quote, and anything after it is gone completely.

Here is my query. How can I change this so it includes the single quote and everything after it?

 

$sql = "UPDATE sheets SET artist = '$artist', title = '$title', active = '$activestatus' WHERE id = $value";
        $result = mysql_query($sql) or die(mysql_error().'<br>'.$sql);  

 

If more code is required to understand what I'm talking about, let me know.

[Firemankurt]

You should always run user input through a cleanup function prior to using it in a query.

Here is the one I use:

 

function cleanValues($value)
{
//undo slashes for poorly configured servers
$value = (get_magic_quotes_gpc()) ? (stripslashes($value)) : ($value);

//determine best method based on available extensions
if (function_exists('mysql_real_escape_string')) 
{
$value = mysql_real_escape_string($value);
}
else 
{
$value = mysql_escape_string($value);
}
return $value;
}

 

This should also escape the single quote to prevent th issue you are having.

 

[Smudly]

Hey, i tried echoing the title out, and after trying to type in:

I Don't Care

 

It echoed out:

I Don\'t Care

 

So this part seems to work correctly, however it doesn't show all of this once it updates the database.

It only shows:

 

I Don

 

I know the query updates the rows successfully, however it just doesn't display the single quote or anything after it.

Any other ideas?

 

[kenrbnsn]

Please show us the code you're using to display the data.

 

Ken

[Smudly]

Thanks for all the suggestions everyone. Here is where I am at. The char-set is utf8-unicode. Once i type in the title:

I Don't Care

Inside the database it displays exactly how I typed it.

Displaying it is the issue now. I tried stripslashes around $title, but it displays as:

I Don

 

Here is my code that displays each row from my database.

while($row = mysql_fetch_array($result))
  {
      if(($ibg%2)==0){
        $bgcolor = "#f5f5f5";
    }
    else{
        $bgcolor = "#ccddff";
    }
$hasdownloads = $row['timesdownloaded'];
  $dcolor = "#ffffff";
  $tcolor = "#ffffff";
  $inputbg = "#ffffff";
  $dlsbg = "#ffffff";
  $lastdownloaded = $row['lastdownloaded'];
  if ($hasdownloads>0){
  $dcolor = "#00cc00";
  $dlsbg = "#93db70";
  }
  else{
  $dlsbg = $bgcolor;
  }
  if ($lastdownloaded==$today){
  $tcolor = "#72a4d2";
  $inputbg = "#ccddff";
  }
  else{
    $tcolor = $bgcolor;
    $inputbg = $bgcolor;
  }
  if($row['artist']=="DELETE"||$row['title']=="DELETE"){
    $bgcolor = "#FE6A6A";
    $bgcolor = "#FE6A6A"; 
  }

  echo "<tr>";
  echo "<td align='center' width='40' bgcolor='$bgcolor'><input type='hidden' name='user[".$row['id']."]' value='".$row['id']."' />" .$row['id']. "</td>";
  echo "<td align='center' width='200' bgcolor='$bgcolor'><input type='text' name='artist[".$row['id']."]' value='" .ucwords($row['artist']). "'  size='30' style='border: none; background-color: $bgcolor;'></td>";
  echo "<td align='center' width='130' bgcolor='$bgcolor'><input type='text' name='title[".$row['id']."]' value='" .ucwords($row['title']). "' style='border: none; background-color: $bgcolor;'></td>";
  echo "<td align='center' width='10' bgcolor='$bgcolor'><input type='text' name='timesdownloaded[".$row['id']."]' value='" .$row['timesdownloaded']. "' size='10' class='adminform' style='background-color: $dlsbg; border: none'></td>";
  echo "<td align='center' width='80' bgcolor='$bgcolor'><input type='text' name='url[".$row['id']."]' value='" .$row['url']. "' size='15' style='border: none; background-color: $bgcolor;'></td>";
  echo "<td align='center' width='10' bgcolor='$tcolor'><input type='text' name='todaydownloads[".$row['id']."]' value='" .$row['todaydownloads']. "' size='10' class='adminform' style='background-color: $inputbg; border: none'></td>";
  echo "<td align='center' width='20' bgcolor='$tcolor'><input type='text' name='lastdownloaded[".$row['id']."]' value='" .$row['lastdownloaded']. "' size='8' style='background-color: $inputbg; border: none'></td>";
  echo "<td align='center' width='10' bgcolor='$bgcolor'><a href='editsheets.php?delete=true&id=" .$row['id']. "' onclick='return show_delete()'>Delete</a></td>";



  echo "<td align='center' width='10' bgcolor='$bgcolor'><input type='checkbox' name='check[".$row['id']."]' style='background-color: $bgcolor;'></td>"; 



  echo "</tr>"; 
  $ibg++;
  }

 

[PFMaBiSmAd]

The single-quote is breaking your HTML (if you do a 'view source' in your browser you will see all the data is there.)

 

When you output content (that is not intentionally HTML) on a web page, you need to use htmlentities with the second parameter set to ENT_QUOTES.

[Smudly]

THANKS!

All working.

 

[rwwd]

You should always run user input through a cleanup function prior to using it in a query.

Here is the one I use:

 

function cleanValues($value)
{
//undo slashes for poorly configured servers
$value = (get_magic_quotes_gpc()) ? (stripslashes($value)) : ($value);

//determine best method based on available extensions
if (function_exists('mysql_real_escape_string')) 
{
$value = mysql_real_escape_string($value);
}
else 
{
$value = mysql_escape_string($value);
}
return $value;
}

 

This should also escape the single quote to prevent the issue you are having.

 

Only one issue with this method, the function already exists, so the clause would always return true - AND you need a valid connection handle to pass into the function as it's second parameter; though if there is a connection already established, _escape_string() will 'inherit' the last used connection.

 

Just be wary of that, I have had to sort this error out on other peoples code before now, read the manual and all becomes clear.

 

Rw