UPDATE will randomly stop working, mysql size or form input problem?

[etrooper]

Hi All,
Have a bit of a strange problem..

I have written a small peice of code to write my web pages from an admin section of my website.

All seemed to be working very well, until I noticed pages randomly stop being able to be UPDATED via mysql

I looked into maybe the fact that my field was too small in my mysql database, so I changed it to LONGTEXT, but I still have the same problem.

The weird thing is if I add a new database entry then use this script to create the 'content' section of the new listing I can sometimes UPDATE the page with a 1000 lines of text, sometime only 100 lines, and then get the error message "Sorry there was an error, please try again"???

I thought maybe the database has a character or byte size limit, but if I create a new db lisitng I can once again enter data into it via the below script, until it randomly stops.

Any one got any ideas what this could be?

if ($submit) {

$currentidb = $_SESSION['idthree'] ;

$add1 = '<P class=normal>';
$add2 = '</P><br>';
if ($radio1) {
	$add1 = '<P class=title2>';
	$add2 = '</P>';
}
if ($radio2) {
	$add1 = '<div class="center"><table class=main cellpadding="5" cellspacing="0"><tr><td>';
	$add2 = '</td></tr></table></div><br>';
}
if ($users_file) {
	echo "user file turned out true??";
	$entercontent = $users_file_name ;
	$add1 = "<P class=header><img src=/images/";
	$add2 = "></P><br>";
} else {
	$entercontent = nl2br("$entercontent");
}
$entercontentb = $add1.$entercontent.$add2;
$result = mysql_query("SELECT * FROM add1 WHERE id ='$currentidb'",$db);
$myrow = mysql_fetch_array($result);
$newvar = $myrow['content'].$entercontentb;
$sql = "UPDATE `add1` SET `content` = '$newvar', date = NOW() WHERE `id` = '$currentidb'";   
$result = mysql_query($sql);
if (!$result) {
	echo "<P class=header>Sorry there was an Error, Please try again</P>"; 
} else {
echo "<P class=header>thankyou information updated</P>";
}
$link2 = TRUE ;
}

[tsinka]

Hi,

change the mysql_query calls to e.g.

$result = mysql_query($sql) or die("SQL error: ".mysql_error());

to see if the submitted data causes an sql error.

Additionally, use mysql_escape_string to make sure that e.g. data that contains single quotes (') won't mess up the queries:

$sql = "UPDATE `add1` SET `content` = '".mysql_escape_string($newvar)."', date = NOW() WHERE `id` = '$currentidb'";    

Thomas

[etrooper]

Hi Thomas,

Thankyou very much I changed the code as you recommeded and it works again.

Why is it that without escaping the string it works for a little bit then stops? Does it have to do with the current contents of the mysql field when trying to update it?

I am reading up on the PHP manual about mysql_escape_string now.

Thanks once again, your help was very much appreciated.

Lucas

[tsinka]

Hi,

glad to hear that you solved the problem 🙂

The problem isn't MySQL but the data you want to store, e.g.

INSERT INTO the table (field1) VALUES('$value1');

If $value1 contains bla the qery would be

INSERT INTO the table (field1) VALUES('bla');

But if the value is b'la the query would be

INSERT INTO the table (field1) VALUES('b'la');

You can see that the singlq quote in b'la messes up the query since MySQL thinks that the value is 'b' and la' would be a syntax error in this case.
mysql_escape_string automatically changes a value in a way that it wouldn't mess up a query.

So your queries will fail randomly as soon as a single quote appears in a value you want to insert. Depending on the data you want to store this can be after 10 rows or 1000.

Hint: Make sure that you always surround the values by using ' and not ". Using double quotes doesn't conform to the SQL standard and AFAIK e.g. oracle won't accept double quotes in queries and bail out with a syntax error. MySQL isn't strict concerning double quotes which could be a real pain if you want to migrate to oracle at a later point.

Thomas

[etrooper]

Hi Thomas,

Very nice description, I understand exactly what the problem was now.

Also I will use ' instead of " from now on.

Thanks again, much appreciated.

Lucas