error updating task/event/note

[wolfwindshadow]

error updating task/event/note

when trying to update a task/event/note I get the following.

phpBMS Error: 300 Update Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'repeat=1, repeatfrequency=1, repeattype="repeatWeekly",repeatdays="mwr", repea' at line 1 -- UPDATE notes SET subject="Chad at WITC for CLASS", content="Chad at WITC for class... schedule subject to change [ Chad Lampson - Sunday, June 18, 2006 6:26:05 PM ]", importance=3, category="", location="WITC Ashland", parentid=NULL, private=1,completed=0, completeddate=NULL, enddate="2006-6-19", endtime="21:45:00", startdate="2006-6-19", starttime="16:00:00", repeat=1, repeatfrequency=1, repeattype="repeatWeekly",repeatdays="mwr", repeattimes=-1, repeatuntildate="2006-8-3", assignedtoid=2, assignedtodate=NULL, assignedtotime=NULL, attachedtabledefid=0, attachedid=0, modifiedby="2" WHERE id=7

[Anonymous]

Some of the field names in the Notes table are MySQL keywords, and therefore must be backquoted in SQL statements...

In modules/base/include/notes_addedit_include.php, check all the queries to make sure the following fields are backquoted: status, repeat, type.

Also, I noticed the use of double-quotes in the queries. Typically, single quotes are used for fixed values (e.g., date_format(datefield,'%c%e%Y'). Depending on how MySQL is configured, using double-quotes could cause problems.

For example, at line 103-123, the function getRecords() shouled be as follows (the update query will require similar changes):

function getRecords($id){ //========================================================================= ===============

global $dblink;

$querystatement="SELECT

id, subject, assignedtoid, type, content, importance,

category,

attachedtabledefid, attachedid,

parentid,location,private,status, repeat,repeatfrequency,repeattype,repeatdays,repeattimes, DATE_FORMAT(repeatuntildate,'%c/%e/%Y') as repeatuntildate,

completed,DATE_FORMAT(completeddate,'%c/%e/%Y')

as completeddate,DATE_FORMAT(startdate,'%c/%e/%Y') as startdate,

TIME_FORMAT(starttime,'%l:%i %p') as

starttime,DATE_FORMAT(enddate,'%c/%e/%Y') as enddate, TIME_FORMAT(endtime,'%l:%i %p') as endtime,

assignedtoid,DATE_FORMAT(assignedtodate,'%c/%e/%Y')

as assignedtodate,TIME_FORMAT(assignedtotime,'%l:%i %p') as assignedtotime,assignedbyid,

createdby, creationdate, modifiedby, modifieddate FROM notes WHERE id=".$id;

$queryresult = mysql_query($querystatement,$dblink); if(!$queryresult) reportError(100,("Could not retrieve

record: ".mysql_error($dblink)." ".$querystatement));

$therecord = mysql_fetch_array($queryresult); if(!$therecord) reportError(300,"No record for id

".$id);

return $therecord;

}//end function

[brieb]

Added possilbe fix as an attachment. Currently, my development server is not running mysql 5, so I will need someone to test the patch to see if it fixes the problem.

Anonymous pointed some mySQL standards I was not aware of. We might need to escape all other files that reference fields like status, type (invoices come to mind). As for the double-single quote problem with fixed values, hopefully that will not be a problem in the future, as these select quesries should not be date formatting when date format can be changed administrativley in the future.

[brieb]

Seems my diff only fixes the SELECT statement, and not the update, or insert. I will try to fix those as well.

[brieb]

possible fix

[brieb]

Ok, the new file should fix the problem, but again, I will need someone to try the diff file on a MySQL 5 setup.

[brieb]

Tested diff with Mysql 5.0.22 no errors

[mipalmer]

Tested on 5.0.26 and 4.1.11 no errors.