Apostrophe problems?

[davygee]

I've got the good old problem with apostrophe's. I have built a website using PHP and linking to a MySQL database and information from a textarea is being sent to the database. When an apostrophe is included in the content of the textarea...nothing is sent to the database at all. I've contacted my Hosting provider and they have recently upgrade the PHP to 5.1.6 and have something called "magic quotes" turned off when it was still activated for other websites I've done. They will not activate the magic quotes for the server that's now hosting the new site as its not a dedicated server and there are other people to consider.

They have said I can use something called addslashes() which apparently does the same...but I don't know how to incorporate it into my coding.

The form I have calls up a content checker file which uses a case statement to determine how to deal with the posted information see below:

case "updatecase": // Update Case Studies
$query = buildQuery($_POST, "UPDATE", "laplace_case");
if( dbit($query)==FALSE ){
sendHeader("../login.php");
}else{
sendHeader("../viewCase.php");
}
break;

This case statement calls up the UPDATE function from another include file that looks like:

case "UPDATE":
$query = "UPDATE $table SET ";
while (list ($key, $value) = each($getArray)) {
$query .= " $key = '$value',";
}
$query = ereg_replace(",$","",$query);
if(isset($getArray[itemID])){
$query .= " WHERE itemID = '$getArray[itemID]'";
}elseif(isset($getArray[id])){
$query .= " WHERE id = '$getArray[id]'";
}elseif(isset($getArray[id])){
$query .= " WHERE id = '$getArray[id]'";
}
break;

Any help would be gratefully appreciated.

Cheers

[Piranha]

I haven't read through all the post, but it seems like you should use [man]mysql_real_escape_string[/man] to solve this problem.

[davygee]

Piranha wrote:

I haven't read through all the post, but it seems like you should use [man]mysql_real_escape_string[/man] to solve this problem.

Yes, I've read up a bit and I reckon [man]mysql_real_escape_string[/man] is the command I need to use. The only problem is that I don't know how it would fit into the coding that I have above?

[mrgrammar]

Addslashes is fairly simple.

addslashes($variable);

I'm fairly new to PHP, so I may be way off base here, but will this work?

$query = "UPDATE $table SET ";
while (list ($key, $value) = each($getArray)) {
	$query .= " $key = ".addslashes($value).",";
}

I would think that mysql_real_escape_string would work the same way.

$query = "UPDATE $table SET ";
while (list ($key, $value) = each($getArray)) {
	$query .= " $key = ".mysql_real_escape_string($value).",";
}

[davygee]

Neither work.

The addslashes does nothing and the real escape string brings up a load of error messages.

[Piranha]

addslashes is a really bad way of doing it since it is not suited to any database. It will cause problems in the database.

I will post an example how you can use it in a few minutes, at the moment I'm kind of busy.

[Will_Poulson]

This goes to the _get array, cleans the values (turns ' into & value, and then initiates the variable with that cleaned value. Great for your M'c Slash/ies

foreach($_GET as $key => $value){
	    //stripslashes(trim($value));
	    $value = preg_replace('!\\\\+|/+!', '', $value);
	if(!isset($$key)){ 
		$$key =htmlspecialchars($value, ENT_QUOTES);
	};
};

You can start here, but you should add an array of permitted values and forbidden values.

the !isset is to avoid overwriting php core values. this can also be modified to handle arrays in the url differently.

Also, you might have trouble outputting to pdf or database dumps later, but I just clean on output

[Piranha]

The easiest way is probably to first set it with mysql_real_escape_string and then insert in the query. Something like this:

case "UPDATE":
$query = "UPDATE $table SET "; // I have no idea where $table comes from, maybe it is needed here as well.
while (list ($key, $value) = each($getArray)) {
$query .= " $key = '$value',";
}
$query = ereg_replace(",$","",$query);
if(isset($getArray['itemID'])){
$itemid = mysql_real_escape_string($getArray['itemID']); // Note the ' signs for the name in the array.
$query .= " WHERE itemID = '$itemid'";
}elseif(isset($getArray['id'])){
$id = mysql_real_escape_string($getArray['id']);
$query .= " WHERE id = '$id'";

You can do it with [man]sprintf[/man] and a few other ways as well. But this is probably the easiest way.

[davygee]

Thanks Piranha, but it doesn't seem to be working. It doesn't update the database at all now.

[Piranha]

Please post the resulting query here within [php ] [/php ] tags (without the spaces) and I'll check if something seems to be strange.

[ahundiak]

Do you have a .htaccess file in your web directory? If so then you can turn magic quotes on just for your application. Better in the long run to just fix your code but a simple setting might be all you need.

[davygee]

UPDATE test_case SET id = '8', title = 'British Waterways'', date = '2007-03-15', description = 'The challenge is to do something simple ', showpublic = '1' WHERE id = ''

It seems to not be able to pass the id across within the query according to the above output so maybe this is where the problem is...but I don't know what to do.

And by the way, I can't use a .htaccess file, I've tried that top initially turn on magic quotes, thanks anyway.

[ahundiak]

Might try something like this

    function update($tableName,$keyName,$data)
    {
        // Build set list
        $set = '';
        foreach($data as $name => $value) {
            if ($name != $keyName) {
                if ($set) $set .= ', ';
                $set .= $name . ' = ' . mysql_real_escape_string($value);
            }
            else $keyValue = mysql_real_escape_string($value);
        }
        $sql = "UPDATE {$tableName}\nSET {$set}\nWHERE {$keyName} = {$keyValue};";
    }

It's a modification of a routine I use. Might need a syntax tweak or two. But all you need is an array of the data to be updated along with the table name and primary key name.

[Piranha]

Do you use mysql_real_escape_string on the title? You should use it on every variable you use in every query to the database. And are you sure that you use the returning value from that in the query. Can you please show that piece of code as well, then we can try to spot any error.

By the way, do not set magic quotes on. They are like addslashed in the scence that it is far to general and messes things up more than it helps.

[davygee]

case "UPDATE":
		$query = "UPDATE $table SET ";
		while (list ($key, $value) = each($getArray)) {
			$query .=  " $key = '$value',";
		}
		$query = ereg_replace(",$","",$query);
		if(isset($getArray['itemID'])){
			$itemid = mysql_real_escape_string($getArray['itemID']);
			$query .= " WHERE itemID = '$itemid'";
			//$query .= " WHERE itemID = '$getArray[itemID]'"; #this is the original coding for this piece instead of the 2 lines above it
		}elseif(isset($getArray['id'])){
			$id = mysql_real_escape_string($getArray['id']);
			$query .= " WHERE id = '$id'";
			//$query .= " WHERE id = '$getArray[id]'"; #this is the original coding for this piece instead of the 2 lines above it
		}
		echo $query; # this brings up the query I demonstrated above
	break;

I have used the coding you have provided above.

[Piranha]

You have to use it in the while-loop as well. And you miss one line. I did not try to do the coding for you, just to show how it could be done.

[davygee]

Piranha wrote:

You have to use it in the while-loop as well. And you miss one line. I did not try to do the coding for you, just to show how it could be done.

Thanks for helping me out this far. I just need that bit more help to get the coding working for me. I'm pretty new to this game as I'm primarily a designer come flash developer, but have been involved in a good few php/mysql content managed sites, although I've been using generic code thus far...and have now come across this problem with magic quotes (which I believe is being scrapped in the next version of PHP).

Any help is gratefully appreciated.