How to properly store and retrieve strings with Apostrophes?

Greetings,

I am getting confused on how to properly store and retrieve data with apostrophes. I will be storing category names like:

O’Brien
It’s nice out
Day/Night

I have to use something like this as I am rawurlencoding data, moving data through AJAX, rawurldecoding data, storing it into the database and this seems to be working:

addslashes(mysql_real_escape_string($_POST[‘categoryname’]))

When I manually look at my database through phpMyAdmin, it gets stored in the database like this:

O\'Brien
It\'s nice out
Day/Night

When I retrieve the data to display it on the website, I have to do something like this:

stripslashes($categoryname);

The problem I’m having is this gets a little buggy when I try calling up data using MySQL SELECT functions, searches and etc.

Then I have to do this before the query in order for it to search properly:
mysql_real_escape_string($categoryname);

I know I’m doing something wrong here. Please let me know what is the best and safest way to store this data, what it should look like when viewing the database manually and what is the best way to retrieve/display the data.

Thanks

My advice – get out of the PHP 4/2004 mode of thinking and stop using the old/outdated mysql_ functions. We now thanks to mySQLi and PDO have these things called “prepared queries” See, the object based mysqli or PDO when doing prepare/exec auto-sanitize your inputs, so you don’t even have to THINK about handling the single quotes that way… in fact it almost makes you SQL injection bulletproof.

Personally, of the two I prefer PDO – it’s very attractive to have the option of targeting more than just mySQL.

Honestly, I’m a little surprised anyone still uses the mysql_ functions given all the hassles, headaches, etc…

The addslashes() is what is causing the problems with what you are currently using as all it does is to escape what has already been escaped by mysql_real_escape_string in a way that doesn’t automatically get resolved by the database.

So getting rid of that would be a quick fix while you work on implementing it properly using a prepared query.

Thanks for the responses.

When I remove addslashes(), Data like O’brien gets stored into the database and looks like this when viewing it in phpMyAdmin:

O’Brien

There is no slashes. Is this what it’s supposed to look like? Another problem is when I query the list of names and try to retrieve and display it, the script stops when it reaches that apostrophe.

Also when storing data, is there going to be a different between these two?:

mysql_query("INSERT INTO table (categoryname) VALUES ('$categoryname')");
mysql_query("INSERT INTO table (categoryname) VALUES ('".$categoryname."')");

and for retrieving data:

mysql_query("SELECT categoryname FROM table WHERE categoryname = '$categoryname'");
mysql_query("SELECT categoryname FROM table WHERE categoryname = '".$categoryname."'");

Another bit of progress:

I’ve come up with something like this:
$categoryname = $row[‘categoryname’]; // pulls name like O’Brien out of database.

echo $categoryname; // prints out O’Brien with no slashes.

Now when I have to query this name with the apostrophe in it to get additional data, I have to do something like this (applying mysql_real_escape_string() to my variable with the apostrophe) :

mysql_query("SELECT * FROM table WHERE categoryname = '".mysql_real_escape_string($categoryname)."'");

I’m not sure if this is the standard way of doing it or not. I’m also not sure how efficient this is or how much additional resources this is going to use. Please provide some feedback on this.

Thanks

Some people are not as fortunate as yourself and have to manage out dated systems. Though I agree, if your starting a new project or have the opportunity to rebuild/update an old one prepared statements are ideal.

I just went digging through my old code to try and remember how I did it before I switched to object based… and found my old sanitize function.


function sanitize($str){
	if (get_magic_quotes_gpc()) $str=stripslashes($str);
	if (function_exists('mysql_real_escape_string')) {
		return mysql_real_escape_string($str);
	} else return addslashes($str);
}

Which handles most of the oddball problems that can crop up with old PHP 4 installs, as well as the proper modern approach. The big trick was to see if php was auto-adding them via “magic quotes” and if so strip those before sending it to the other functions. _real_escape for “modern”, addslashes for older versions of PHP.

If you’re stuck working old-school, you’ll probably not do much better than that.

that makes me shiver, burrrr, lol