addslashes, mysql_real_escape_string, etc not working

[pedalpete]

I am finding this very strange and frustrating, but I've got some data being entered into a mysql database, and when the data contains an apostrophe for example the word we're, it shows up in the database as
we’re.

weird.

I've used addslashes & mysql_real_escape_string, but nothing seems to fix it, and I can see when I 'echo' the input, that the string is not being escaped.


I don't get any errors, but nothing happens.

    preg_match ("/<input type=\"hidden\" name=\"Title\" value=\"([^`]*?)\">/", $match, $temp);
    $Title = $temp['1'];
    $Title = trim($Title);
    $Title =addslashes($Title); 
 
 echo $Title.'<br />';
 

[hitokiri]

I am finding this very strange and frustrating, but I've got some data being entered into a mysql database, and when the data contains an apostrophe for example the word we're, it shows up in the database as
we’re.

weird.

I've used addslashes & mysql_real_escape_string, but nothing seems to fix it, and I can see when I 'echo' the input, that the string is not being escaped.


I don't get any errors, but nothing happens.

    preg_match ("/<input type=\"hidden\" name=\"Title\" value=\"([^`]*?)\">/", $match, $temp);
    $Title = $temp['1'];
    $Title = trim($Title);
    $Title =addslashes($Title); 
 
 echo $Title.'<br />';
 

I'm not that sure, but I do have cases like that in another programming language. Turns out that my page is not encoded in proper/desired format.

How does it appear when you view the source of the web page? There is a chance that the source is correct but it's just that the output is not what you wanted it to be.

[pedalpete]

I'm not that sure, but I do have cases like that in another programming language. Turns out that my page is not encoded in proper/desired format.

How does it appear when you view the source of the web page? There is a chance that the source is correct but it's just that the output is not what you wanted it to be.

the source looks good (as I'm making it for testing purposes).

I have the encoding of the page set to utf-8, and when I do any sort of 'output', echo, etc I get the same value as the source, without slashes (even after trying to add slashes), so 'we're' comes out 'we're' , and not 'we\'re'.

Then of course when the goes into the database, it comes out 'we’re'.

[Atli]

Hi.

This does indeed appear to be a problem with your charsets.
Just try forcing your browser to render this page using the UTF-8 charset and view your first post... notice that the incorrect output will display as you initially wanted it to be.

Are you passing the text through any of PHP's functions before adding it to the database?
Like for example the htmlentites function?

If so, you may need to specify that you want it to use the UTF-8 charset or it may return it in the default (ISO-8859-1) charset.

[pedalpete]

Hi Atli,

I am forcing the character set with this line right at the top of my page. is there another way to do it?

header("Content-type: text/html; charset=utf-8");
 

I am passing the data through 'addslashes()' before adding it to the database (thinking that slashes are what helps it get into the db cleanly). I have also tried mysql_real_escape_string, but neither worked.

[pedalpete]

So I am decoding this puzzle of why 'addslashes' doesn't seem to be working, and it seems their may be a problem with the way I have written my inset query.

I've always used quotes around the variables in the insert statement, but if I remove the quotes I get a mysql error where the slashes should be. If I have quotes in the mysql stament, the insert occurs, but the statement gets inserted with 'special characters' instead of slashes.

here's what I mean re: mysql

...VALUES('$address', '$title')
vs
...VALUES($address, $title)
 

Does this clue anybody in to what my error is?

Now I can get a mysql error, but that doesn't solve why addslashes() isn't working in the first place.

more code

 
$eventAddress = "$eventStreet, $eventCity, $eventState, $eventZip";
$eventAddress = addslashes($eventAddress);
// echo $eventAddress.'<br />';
 
list($date, $time) = explode(' ', $eventDateTime);
list($month, $day, $year) = split('-', $date);
$date = date('Y-m-d', mktime(0,0,0,$day,$month,$year));
// echo $date.'<br />';
// echo $time.'<br />';
 
$insertShow = "INSERT INTO shows ( address, date, time) VALUES ( '$eventAddress', '$date', '$time')";
 mysql_query($insertShow)or die(mysql_error()); 
 
 

[dlite922]

So I am decoding this puzzle of why 'addslashes' doesn't seem to be working, and it seems their may be a problem with the way I have written my inset query.

I've always used quotes around the variables in the insert statement, but if I remove the quotes I get a mysql error where the slashes should be. If I have quotes in the mysql stament, the insert occurs, but the statement gets inserted with 'special characters' instead of slashes.

here's what I mean re: mysql

...VALUES('$address', '$title')
vs
...VALUES($address, $title)
 

Does this clue anybody in to what my error is?

Now I can get a mysql error, but that doesn't solve why addslashes() isn't working in the first place.

more code

 
$eventAddress = "$eventStreet, $eventCity, $eventState, $eventZip";
$eventAddress = addslashes($eventAddress);
// echo $eventAddress.'<br />';
 
list($date, $time) = explode(' ', $eventDateTime);
list($month, $day, $year) = split('-', $date);
$date = date('Y-m-d', mktime(0,0,0,$day,$month,$year));
// echo $date.'<br />';
// echo $time.'<br />';
 
$insertShow = "INSERT INTO shows ( address, date, time) VALUES ( '$eventAddress', '$date', '$time')";
 mysql_query($insertShow)or die(mysql_error()); 
 
 

Can you add a die() statement just before mysql_query();

die($insertShow);

That should halt programming and exit and print the SQL given to MySQL. I just want to see what that looks like.

Thanks,

Oh and post the output of the page source here.


DM

[Atli]

You should always put strings inside single-quote marks in your SQL statements. Otherwise MySQL will try to parse them as columns or extra clauses or something along those lines.

You can use the <meta> tags to *set* the charset on you pages. Something like:

<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<!-- etc -->
</html>
 

Don't know if that will change anything tho. The header you showed us should do pretty much the same thing.

[pedalpete]

thanks d-lite and Atli, you are definately correct

I went around and around in circles with character encoding until I came with what I think is the simple solution.

I can call htmlentities() and encode it all before putting it into the db. The stuff I'm dealing with is canadian, so i figure that is easier than dealing with all the accents on french characters with slashes etc.

I can't share the source file as it's a local file that I can't share.

Atli, I don't know how I missed your comment about setting forcing the character set in my browser, but now I can see more clearly. unfortunately, i still don't see how I can fix that.

Is there any reason I shouldn't store htmlentities in my database?
I'll just convert the string before I pass it into a query (which will mostly be in numeric columns and id's anyway).