Members Creating Content with ' Throwing DB Errors

Everytime a member creates content on my site (for example: sounds, videos and events or even this occurs with tags as well) that includes an apostrophe (') in the name (for example: mcdonald's), I get loads of database errors through stating there is an error in my SQL syntax due to this apostrophe.

What can I do to make my site ignore the fact content contains apostrophes? It is causing some pages on my site not too load and just display SQL errors.

Thanks

Please post the exact mysql errors here, maybe it is a bug and fix is needed.

Hi Alex,

Thanks for the reply.

An example is the following:-

Query:

SELECT COUNT(*) FROM `RayVideoFiles`  left JOIN `Profiles` ON `Profiles`.`ID`=`RayVideoFiles`.`Owner` left JOIN `sys_albums_objects` ON `sys_albums_objects`.`id_object`=`RayVideoFiles`.`ID` left JOIN `sys_albums` ON `sys_albums`.`ID`=`sys_albums_objects`.`id_album` WHERE 1 AND `RayVideoFiles`.`Status` ='approved' AND CONCAT(`RayVideoFiles`.`Tags`) LIKE ('% gettin' over you%') AND `sys_albums`.`Status` ='active' AND `sys_albums`.`Type` ='bx_videos' 

Mysql error: 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 'over you%') AND `sys_albums`.`Status` ='active' AND `sys_albums`.`Type` ='bx_vid' at line 1

This error is being thrown due to a tag being added (gettin' over you), removing the apostrophe eradicates the problem.

Also, what's the simplest method to reduce the size of the database error emails that come through, mine are being delivered from 8Mb upwards.

Many thanks,

Geoff

I can not reproduce this problem on the latest 7.0.3 version.

Please can you specify your Dolphin version and the page which generates this error ?

Hi Alex,

I'm on 7.0.2, rolling out an update to 7.0.3 on our development site this week, if all okay will roll out to live site so hopefully this would rectify the problem.

Actually, I just tested this more. The database errors are only thrown if the generated content that contains the apostrophe in the title or tags is in the locations module and the games module, neither module is developed by Boonex. I guess I better raise this with the developer of these modules unless anyone can advise if there is a simple bit of code I can include to prevent this happening at all please?

Many thanks :)

Then it looks like the bug in the code and fixing is needed, this is not error only - this is security hole too. I suggest to remove these modules until the problem is resolved, until then it is not safe to use these modules.

I stand corrected, it isn't just these two modules.

Video was uploaded with the tag slammin'

And this is now throwing the following database error.

Query:

SELECT COUNT(*) FROM `RayVideoFiles`  left JOIN `Profiles` ON `Profiles`.`ID`=`RayVideoFiles`.`Owner` left JOIN `sys_albums_objects` ON `sys_albums_objects`.`id_object`=`RayVideoFiles`.`ID` left JOIN `sys_albums` ON `sys_albums`.`ID`=`sys_albums_objects`.`id_album` WHERE 1 AND `RayVideoFiles`.`Status` ='approved' AND CONCAT(`RayVideoFiles`.`Tags`) LIKE ('% Slammin'%') AND `sys_albums`.`Status` ='active' AND `sys_albums`.`Type` ='bx_videos' 

Mysql error: 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 'active' AND `sys_albums`.`Type` ='bx_videos'' at line 1

Ouch, if this is a bug in the core dolphin 7 code then this could be an easy to exploit SQL injection issue.

Can not reproduce this bug!

Please can you try this on boonex.us site ?

I just tested this. Is any one else having trouble accessing boonex.us now?

I just get /'waiting for..../'

Never mind, pages finally started to load.  For a moment I thought I actually may have found a security issue.

Alex, my tests at boonex.us did not reveal errors like the ones reported, and my basic SQL injection attempt seemed to fail.

Boonex.us is just really slow right now, i guess.

I'm experiencing no issues. I also tried to reproduce this yesterday and failed.

I'm currently trying to update to 7.0.3 on our development site and then once I have finally got that upgraded smoothly (experiencing problem after problem) then I will update our live site, if on 7.0.3 the problem still occurs, I will report back in this thread.

I'm unsure what could have caused this to happen, none of the core code for anything related to tags has been modified. I don't believe there are any settings that could have been altered to prevent apostrophes from being used is there?

Thanks

Thanks everyone for the testing.

The only setting is magic_quotes, but Dolphin can work with magic quotes on and off, however it is recommended to disable magic quotes since it make sites a bit slower and this setting is already deprecated.

Please could you advise where I can check my magic_quotes settings?

Thanks

You can check it in phpinfo, it is also available in :

Dolphin admin panel -> tools -> host tools -> phpinfo