Private Message SQL Error

14 years 3 months ago #118201 by softforge
Private Message SQL Error was created by softforge
I have been testing the CB AutoWelcome Plugin on Joomla 1.5.15 and CB 1.2.1 and have been getting the following error when someone clicks the activation link:

[code:1]SQL errorDB function failed with error number 1064
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 'Brien', 0)' at line 1 SQL=INSERT INTO jos_uddeim (fromid, toid, message, datum, systemmessage, disablereply) VALUES (136, 160, 'Welcome Aboard Test Test! Hello Test Test, Welcome to our site!', 1259755333, 'Jamie O'Brien', 0)
SQL =

INSERT INTO jos_uddeim (fromid, toid, message, datum, systemmessage, disablereply) VALUES (136, 160, 'Welcome Aboard Test Test!

Hello Test Test, Welcome to our site!', 1259755333, 'Jamie O'Brien', 0)
[/code:1]

I realised the problem was down to our admin having an ' in his name which was causing the invalid SQL. I traced the code back and found that pms.uddieim.php was causing the problem.

I changed line 125 of that file to the following which solved the problem:

[code:1]$udde_sysm=str_replace("'", "''", $quereply);[/code:1]

I'm sure this is not the best way to fix the problem, but it does the job for the moment.

I will post a link to this thread in the uddeim forum as well, just posted it here in case anyone else comes across the same problem in the same way.

If you like our plugins, please consider writing a review on the Joomla Extension Directory or the Community Builder Directory ...

Please Log in to join the conversation.

14 years 3 months ago #118219 by slabbi
Replied by slabbi on topic Re:Private Message SQL Error
Well, better use this:

[code:1] if ($doObfuscate) {
[...]
} else {
$cm = $udde_msg;
$cm = $this->_uddeUnescape($cm);
$cm = addslashes($cm);
[...]
}
[/code:1]

When you enable obfuscating quotes are no problem. Without obfuscating they should be escaped.

Plz could you try if this fixes your problem, too?

uddeIM & uddePF Development
CB Language Workgroup
CB 3rd Party Developer

Please Log in to join the conversation.

14 years 3 months ago #118274 by softforge
Replied by softforge on topic Re:Private Message SQL Error
I'm not exactly sure what you mean, but the problem is with the name field being used as the system name. Here are bigger chunks of the code, from line 119:


Before my change

[code:1] // try to find the realnames settings of udde
if($this->config->realnames) {
$sql="SELECT name FROM #__users WHERE id=".(int) $udde_fromid;
$_CB_database->setQuery($sql);
$quereply=$_CB_database->loadResult();
if($quereply) {
$udde_sysm=$quereply;
}
}

if ($doObfuscate) {
$cm = $udde_msg;
$cm = $this->_uddeUnescapeCrypt($cm);
$cm = uddeIMencrypt($cm,$this->config->cryptkey,CRYPT_MODE_BASE64);
$sql="INSERT INTO #__uddeim (fromid, toid, message, datum, systemmessage, disablereply, cryptmode, crypthash) VALUES (".$udde_fromid.", ".$udde_toid.", '".$cm."', ".$udde_time.", '".$udde_sysm."', 0, 1,'".md5($this->config->cryptkey)."')";
} else {
$cm = $udde_msg;
$cm = $this->_uddeUnescape($cm);
$sql="INSERT INTO #__uddeim (fromid, toid, message, datum, systemmessage, disablereply) VALUES (".$udde_fromid.", ".$udde_toid.", '".$cm."', ".$udde_time.", '".$udde_sysm."', 0)";
}[/code:1]

After my change

[code:1] // try to find the realnames settings of udde
if($this->config->realnames) {
$sql="SELECT name FROM #__users WHERE id=".(int) $udde_fromid;
$_CB_database->setQuery($sql);
$quereply=$_CB_database->loadResult();
if($quereply) {
$udde_sysm=str_replace("'", "''", $quereply);
}
}

if ($doObfuscate) {
$cm = $udde_msg;
$cm = $this->_uddeUnescapeCrypt($cm);
$cm = uddeIMencrypt($cm,$this->config->cryptkey,CRYPT_MODE_BASE64);
$sql="INSERT INTO #__uddeim (fromid, toid, message, datum, systemmessage, disablereply, cryptmode, crypthash) VALUES (".$udde_fromid.", ".$udde_toid.", '".$cm."', ".$udde_time.", '".$udde_sysm."', 0, 1,'".md5($this->config->cryptkey)."')";
} else {
$cm = $udde_msg;
$cm = $this->_uddeUnescape($cm);
$sql="INSERT INTO #__uddeim (fromid, toid, message, datum, systemmessage, disablereply) VALUES (".$udde_fromid.", ".$udde_toid.", '".$cm."', ".$udde_time.", '".$udde_sysm."', 0)";
}[/code:1]

Maybe you can show what I need to change from this?

Many thanks.

If you like our plugins, please consider writing a review on the Joomla Extension Directory or the Community Builder Directory ...

Please Log in to join the conversation.

14 years 3 months ago #118298 by slabbi
Replied by slabbi on topic Re:Private Message SQL Error
Just add one line of code. I mentioned the position of the extra line in my post.

uddeIM & uddePF Development
CB Language Workgroup
CB 3rd Party Developer

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.887 seconds

Facebook Twitter LinkedIn