storing fields containing an apostrophe or single quote

Melville

A while back (perhaps a month or so), I tested the PHP functions addslashes() and removeslashes() and found that the add side worked quite well, but the remove side did not work. Any field I had that required slashes got an increasing number of slashes each time I stored it. So I called my hosting service to see if I even needed to use these functions. What I was told at the time is that they have a setting that eliminates the need to use these functions, that they automatically take care of the problem.

So I stripped out all the hundreds of lines of code that implemented slashes throughout my site.

Now I am testing the site on real data, and I find that I have a problem with any fields that contain a single quote or an apostrophe (same character). Specifically, MYSQL sees the single quote embedded within a field as the end of that field (since I enclose all fields in single quotes when I build the INSERT SQL string. As a result, I get an SQL error when I try to execute the INSERT command.

I am at a bit of a loss as to what to do. The books say use the PHP functions addslashes() and removeslashes(), but when I did that, I had problems. When I do not use them, I also have problems. I cannot use double quotes instead of single quotes. That would have the same problem when the string contained a double quote, and PHP would not be able to resolve the field names because it would assume that the variable name was a literal string instead of a variable name.

Any suggestions? I know I am not the first person to have this problem.

Thanks,

Mel

jasonmills58

Hi, I'm not sure if this will fix it, but I've also never had this particular problem. Try putting the SQL statement in a variable like

$query = "INSERT INTO hostingPlans ( planName, planCost ) 
	VALUES( '$planName', '$planCost' )";
if ( ! mysql_query( $query, $link ) ) {
	$dberror = mysql_error();
	print $dberror;
	return false;
}

That works for me, even if $planName or planCost have apostrophes or whatever.

Melville

Hello, Jason. I tried what you suggested, and I still have the problem. I have a field that contains the string "D'Azur". As soon as the SQL parser gets to the apostrophe after the "D", I get the following error:

You have an error in your SQL syntax near 'Azur', '', 'FRA', '', '', 'S', ' at line ...

Any other thoughts?

Thanks,
Mel

Pig

You can use html_special_chars to convert apostrophe's and double quotes to their HTML equivalent.

As for the add slashes issue, if it is not working then there is something wrong with your code. Maybe magic_quotes is activated in your php.ini. Maybe you are applying the remove slashes at the wrong time. Without seeing your code, I couldn't say.

Don't be so quick to abandon a technique because it doesn't work. The reality is that you are doing something wrong. You can try something else, but you won't learn unless you figure out what you were doing wrong. If you cannot fix this problem, you may not be able to fix the next one. I'm not trying to sound dismissive, just trying to help.