PHP Problem updating text fields containing ' symbol

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jtonsfeldt
Forum Newbie
Posts: 3
Joined: Mon Nov 01, 2004 2:30 pm

PHP Problem updating text fields containing ' symbol

Post by jtonsfeldt »

Hi,

I am encountering a problem updating a text field in a MySQL table via PHP.

If there is a single quotation mark ' anywhere in the contents of the field, it will not update. Remove the quotation marks, and it updates fine. Here is the script.

Code: Select all

require("openfirstdb.php");
$sq = "SELECT *";
$sq.= " FROM author ";
$sq.= " WHERE AUTO_ID = 0";
$rs = mysql_db_query($DBname,$sq,$link);

while ($row=mysql_fetch_array($rs)){
if($row["pass"]==$pass){ //checks password

$sq2 = "UPDATE author set ";
$sq2.= " body = '$sbody' ,";
$sq2.= " contact = '$contact', ";
$sq2.= " news = '$snews' ";
$sq2.= " WHERE AUTO_ID = 0";
$result = mysql_db_query($DBname,$sq2,$link);

if ($result){
print("Contents successfully updated.");
}
else {
print ("Contents not successfully updated.");
}
}
}
I'm lost. This only happens when I am using UPDATE. No problem with INSERT or when I preview the contents on my browser.

Any suggestions?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

use [php_man]mysql_escape_string[/php_man] on the data before throwing it in your query.

Sidenote: on each iteration your script updates ALL the authors who have their auto_id equal to zero, thus your script could be simplified to just one query:

Code: Select all

require("openfirstdb.php");
$sq = "UPDATE author set";
$sq.= " body = '" . mysql_escape_string($sbody) . "',";
$sq.= " contact = '" . mysql_escape_string($contact) . "', ";
$sq.= " news = '" . mysql_escape_string($snews) . "' ";
$sq.= " WHERE AUTO_ID = 0";
if( mysql_db_query($DBname,$sq,$link) ) 
   echo "Contents updated";
else
   echo "Update failed";
Post Reply