sprintf for adodb update

[codemonkey88]

Hi,
I have been developing a cms site for a client, which uses the adodb package to update the database. I have been doing this as below;

$updateSQL = sprintf("UPDATE tblSections SET Content = '%s' , ContentTitle = '%s' ,UpdatedDate = '%s' WHERE SectionID = %s",	
		$Content,
		$ContentTitle,
		date('Y-m-d H:i:s',time()),
		$SectionID
		); 
		
		$Resultl = False;
		$Result1 = $connDB->Execute($updateSQL) or die($connDB->ErrorMsg());
		$Result1 = True;
/*Some validation stuff*/

When I set this up on the test server, and have used the same syntax on other sites, this works fine. However, when I have put this on the client server, I get syntax errors anywhere there is an apostraphie in the page content.

Persumably this is breaking the string, but it seams odd it has worked everywhere else, and as I am using a wysiwyg editor, I can't really stop people putting apostrophies in the content.

I tried a string replace to convert them to ascii codes, but this just broke other elements with double quotes:(

Any ideas as to how this might be resolved are greatly appreciated.

[Wraithmanilian]

Have you tried addslashes()?

[diafol]

$var = mysql_real_escape_string($variable);

May be a preferred method.

$updateSQL = sprintf("UPDATE tblSections SET Content = '%s' , ContentTitle = '%s' ,UpdatedDate = '%s' WHERE SectionID = %s",	
		mysql_real_escape_string($Content),
		mysql_real_escape_string($ContentTitle),
		date('Y-m-d H:i:s',time()),
		mysql_real_escape_string($SectionID)
		); 
 
		$Resultl = False;
		$Result1 = $connDB->Execute($updateSQL) or die($connDB->ErrorMsg());
		$Result1 = True;
/*Some validation stuff*/

[Wraithmanilian]

@ardav - I stand corrected. :)

[codemonkey88]

$var = mysql_real_escape_string($variable);

May be a preferred method.

$updateSQL = sprintf("UPDATE tblSections SET Content = '%s' , ContentTitle = '%s' ,UpdatedDate = '%s' WHERE SectionID = %s",	
		mysql_real_escape_string($Content),
		mysql_real_escape_string($ContentTitle),
		date('Y-m-d H:i:s',time()),
		mysql_real_escape_string($SectionID)
		); 
 
		$Resultl = False;
		$Result1 = $connDB->Execute($updateSQL) or die($connDB->ErrorMsg());
		$Result1 = True;
/*Some validation stuff*/

Cheers - I don't actually have access to the code from home, but this looks promising. I'm surprised I haven't come across it actually, sounds like a pretty fundamental action :\

[diafol]

Yeah, mres is a life saver. Be careful of scripting (inserting a <script> tag into the wysiwyg). Most wysiwygs disable or remove script tags automatically, but it may be worth testing your editor for this. It's unlikely that a client would purposely do this to his own site, but you'd want to lock down any security issues.

@Wraith - huh, I used to use addslashes and htmlentities for all input for a long time before coming across this one! :)

[codemonkey88]

Cheers - I don't actually have access to the code from home, but this looks promising. I'm surprised I haven't come across it actually, sounds like a pretty fundamental action :\

Great, Thanks :)