Frequently Asked Questions SQL Error When Logged In ( FAQ )

registered · Posted: Thu Feb 21, 2008 8:46 pm

Wait a minute, I found that string "Unable to Insert" in the RWS_WhoIsWhere module. Try deactivating that module.

Worker Joined: Aug 22, 2006 Posts: 192

Gremmie wrote:

What version of MySQL are you running?

Hi Gremmie,

As previosuly stated...

emmaphp wrote:

...if it helps, I can confirm that my system is running MySQL 4.1.14

Posted: Thu Feb 21, 2008 8:51 pm

Well, false alarm on the RWS_WhoIsWhere module. Nevermind that.

You don't have your INSTALLATION folder still on your server do you? If you do, rename it.

Other than INSTALLATION and WhoIsWhere, I don't even see the string 'Unable to Insert' anywhere in the code.

Someone's going to have to get in there and debug that.

I don't suppose you can upgrade your MySQL. That's really old.

Posted: Thu Feb 21, 2008 8:54 pm

Okay, hold the phone. I did find it.

Deactivate that RWS_WhoIsWhere module.

It is making database calls without the $db abstraction layer, so that is why the new logging facility didn't catch it.

Posted: Thu Feb 21, 2008 8:54 pm

Gremmie wrote:

Wait a minute, I found that string "Unable to Insert" in the RWS_WhoIsWhere module. Try deactivating that module.

Hi Gremmie,

As you will have seen when you previously visited my website, (after I gave you the details in the Private Message I sent), the RWS_WhoIsWhere Module is already deactivated.

I am not able to update the version of MySQL that is running on my webspace providers system.

Also, yes, the INSTALLATION directory and all that it contains is indeed still on my server, however it has already been renamed to 'INSTALLATION_' Crying or Very sad

Posted: Thu Feb 21, 2008 9:01 pm

Are you sure about that?

Well, in any event, find modules/RWS_WhoIsWhere/includes/RWS_wiw.inc.php

Find this chunk of code:

Code:




if (is_user($user)) {


   $userInfo = getusrinfo($user);


   $delSql = 'delete from '._RWS_WIW_TABLE_HEAP." where who='".mysql_real_escape_string($userInfo[2])."'";


   mysql_query($delSql) or die(_RWS_WIW_UNABLETOREMOVE.' '._RWS_WIW_MYSQLSAID.': '.mysql_error());


   $rws = mysql_query('select custom_title from '._RWS_WIW_TABLE_MODULES.' where title=\''.mysql_real_escape_string($name).'\'');


   $rwsresult = mysql_fetch_row($rws);


   $updSql = 'update '._RWS_WIW_TABLE_HEAP." SET mn='".mysql_real_escape_string($rwsresult[0])."' WHERE who='".mysql_real_escape_string($userInfo[2])."'";


   $updRC = mysql_query($updSql);


   if (mysql_affected_rows()==0) {


      mysql_query('insert into '._RWS_WIW_TABLE_HEAP." values('$userInfo[2]','$rwsresult[0]')") or die(_RWS_WIW_UNABLETOINSERT.' '._RWS_WIW_MYSQLSAID.': '.mysql_error());


   }


}

Change it to this:

Code:




if (is_user($user)) {


/*********************************


   $userInfo = getusrinfo($user);


   $delSql = 'delete from '._RWS_WIW_TABLE_HEAP." where who='".mysql_real_escape_string($userInfo[2])."'";


   mysql_query($delSql) or die(_RWS_WIW_UNABLETOREMOVE.' '._RWS_WIW_MYSQLSAID.': '.mysql_error());


   $rws = mysql_query('select custom_title from '._RWS_WIW_TABLE_MODULES.' where title=\''.mysql_real_escape_string($name).'\'');


   $rwsresult = mysql_fetch_row($rws);


   $updSql = 'update '._RWS_WIW_TABLE_HEAP." SET mn='".mysql_real_escape_string($rwsresult[0])."' WHERE who='".mysql_real_escape_string($userInfo[2])."'";


   $updRC = mysql_query($updSql);


   if (mysql_affected_rows()==0) {


      mysql_query('insert into '._RWS_WIW_TABLE_HEAP." values('$userInfo[2]','$rwsresult[0]')") or die(_RWS_WIW_UNABLETOINSERT.' '._RWS_WIW_MYSQLSAID.': '.mysql_error());


   }


********************************/


}

Posted: Thu Feb 21, 2008 9:07 pm

Okay, that code runs all the time. The module doesn't need to be active. It is included by index.php and modules.php. I'll enter this into our bug tracking system. It looks like the SQL that the who is where code is using is not compatible with that older version of MySQL.

Posted: Thu Feb 21, 2008 9:09 pm

GREMMIE!!!... Wave

THAT WAS IT!... Dance-Y

...

PROBLEM SOLVED!.. Very Happy

...

So to conclude Gremmie, do I need to do anything else now or just keep that new code in place and consider the issue fixed?

Also, please can you explain (in simple terms) why the problem occured, moreover why no one else apparently had the problem and I did?

Thanks SOOOOOO Much Gremmie and all those that helped with this 'small', but non the less significant issue! Wave

Posted: Thu Feb 21, 2008 9:11 pm

Another way to work around this problem is to find this line:

Code:




include_once('modules/RWS_WhoIsWhere/includes/RWS_wiw.inc.php');

in both index.php and modules.php and comment it out:

Code:




//include_once('modules/RWS_WhoIsWhere/includes/RWS_wiw.inc.php');

Posted: Thu Feb 21, 2008 9:12 pm

Wow, what a doozy!!!

So in summary, emmaphp, there is some SQL in that chunk of code that isn't compatible with the version of MySQL you are using.

Posted: Thu Feb 21, 2008 10:08 pm

Gremmie; I will thank you too!. I was puzzling over this all during dinner. I've been trying to make my local MYSQL run in strict mode but according the the manual you should be able to insert a NULL value in an auto-increment field so that is unlikely to be the source of any problems. Plus he's running an old rather than a bleeding edge MYSQL.

Posted: Fri Feb 22, 2008 8:05 am

After sleeping on this, and visiting emmaphp's page again, I think I see the problem finally. emmaphp gave his module the descriptive name FAQ's (notice the apostrophe). I'm guessing this isn't being properly escaped and used in an SQL query. So it probably is not a MySQL incompatibility, just a simple bug in the code. All his other modules work fine.

I bet ya, emmaphp, that if you were to put the code back and then re-title your module to FAQ or FAQs, it would start working again too.

Bang Head

Posted: Fri Feb 22, 2008 8:18 am

You could try swopping references of

Code:

mysql_real_escape_string

to

Code:

mysql_escape_string

So it would look something like this;

Code:




f (is_user($user)) {


   $userInfo = getusrinfo($user);


   $delSql = 'delete from '._RWS_WIW_TABLE_HEAP." where who='".mysql_escape_string($userInfo[2])."'";


   mysql_query($delSql) or die(_RWS_WIW_UNABLETOREMOVE.' '._RWS_WIW_MYSQLSAID.': '.mysql_error());


   $rws = mysql_query('select custom_title from '._RWS_WIW_TABLE_MODULES.' where title=\''.mysql_escape_string($name).'\'');


   $rwsresult = mysql_fetch_row($rws);


   $updSql = 'update '._RWS_WIW_TABLE_HEAP." SET mn='".mysql_escape_string($rwsresult[0])."' WHERE who='".mysql_escape_string($userInfo[2])."'";


   $updRC = mysql_query($updSql);


   if (mysql_affected_rows()==0) {


      mysql_query('insert into '._RWS_WIW_TABLE_HEAP." values('$userInfo[2]','$rwsresult[0]')") or die(_RWS_WIW_UNABLETOINSERT.' '._RWS_WIW_MYSQLSAID.': '.mysql_error());


   }


}

I have found that some server setups don't like mysql_real_escape_string

Having said that, Gremmie raised a good point and if that cures the problem then we need to track that and fix it.

Posted: Fri Feb 22, 2008 8:47 am

Gremmie wrote:

After sleeping on this, and visiting emmaphp's page again, I think I see the problem finally. emmaphp gave his module the descriptive name FAQ's (notice the apostrophe). I'm guessing this isn't being properly escaped and used in an SQL query. So it probably is not a MySQL incompatibility, just a simple bug in the code. All his other modules work fine.

I bet ya, emmaphp, that if you were to put the code back and then re-title your module to FAQ or FAQs, it would start working again too.

Bang Head

Hi everyone,

Gremmie's last post here appears to be spot on.

I reverted back to the original code in the modules/RWS_WhoIsWhere/includes/RWS_wiw.inc.php file.

I then renamed my FAQ module by removing the apostrophe I had used and...

TADA!!!...Gremmie was correct yet again!!! Dance-Y

So now we know that the cause of the problem is the use of an apostrophe in module names, what is the IDEAL fix/solution...(I assume it isn't the option of changing the code in the modules/RWS_WhoIsWhere/includes/RWS_wiw.inc.php file).

Posted: Fri Feb 22, 2008 9:23 am

I think we need to fix RWS_wiw.inc.php. Notice this path:

Code:




if (mysql_affected_rows()==0) { 


      mysql_query('insert into '._RWS_WIW_TABLE_HEAP." values('$userInfo[2]','$rwsresult[0]')") or die(_RWS_WIW_UNABLETOINSERT.' '._RWS_WIW_MYSQLSAID.': '.mysql_error()); 


   }

Right there is a usage of $rwsresult[0] that hasn't been addslashed or escaped in any way. I wonder if that is the path being taken in emmaphp's system.

Posted: Fri Feb 22, 2008 9:47 am

Wow...slightly off topic, but look what I saw in the news:

http://news.yahoo.com/s/ap/apostrophes_in_names

Here is a quote, describing problems with the apostrophe in computer systems:

Quote:

Two: Even if the computer system is sophisticated enough to welcome an O'Brien or Al-Kurd, the name must be stored in the database, where a hyphen or apostrophe is often mistaken for a piece of computer code, corrupting the system.

That's what happened during the Michigan caucus in 2004, when thousands of O'Connors, Al-Husseins, Van Kemps and others who went to the polls didn't have their votes counted.

Wow, sounds familiar! Laughing

Posted: Fri Feb 22, 2008 10:26 am

I was just able to replicate this on my test system. Activated RWS... module. All okay. Activated FAQ, I had a few FAQ's in it from prior testing. All okay. Changed the title on the FAQ module to FAQ's. Home page loads okay but then you go FAQ's module you get the message that Emmaphp posted at the start of this thread. It must be because RWS... is trying to insert FAQ's into WIW_TABLE_HEAP.

Didn't Bob Seger write "working on a mystery without any clues"?

Posted: Fri Feb 22, 2008 11:11 am

You don't even need to have RWS activated, that is what bugs me about it.

Anyway, I will fix this in the next day or two for the patch release. I'll post the fix here in case anyone wants it sooner.

Posted: Fri Feb 22, 2008 11:13 am

Whoops, looks like Raven is going to fix it. That is fine, since I think the R in RWS actually stands for Raven! Smile