mySQL errors - problems writing to tables

[nmagee]

Hi,

Just installed OJS 1.1.6 on my Linux server, and it installed just fine, except wouldn't allow me to write to the tbljournal in steps 2, 3, and 4 in the install process.

I can go into mySQL and edit the tables manually, and the changes work just fine, but I am wondering what might be going on? I have also tested as an author making a contribution and had the same error (writing to table) in step 1 or 2 of the submission process.

Do you have any ideas of what I can test or fix to resolve this? I'm not really well versed in SQL -- I would have thought at first that it is a permissions error, or some variable set wrong -- but I'm doubting it's that.

thanks,
NEM

[kevin]

That is quite odd if parts of the system are working. I assume it is giving an error like "Failed to update tbljournal table" when it fails?

If so, could you edit one of the files that is failing (e.g., admin/editor/step2.php) and change the line that looks like this:

Code: Select all

$db->query($query) or die("Failed to update tbljournal table");

To:

Code: Select all

$db->query($query) or die($db->error());

This will output the MySQL error code and error message, which might be more helpful in diagnosing the problem.

[nmagee]

Kevin,

my, you are a smart one. Were I more familiar with PHP, I might have thought of that, since it's how I get by in Perl at times.

You were absolutely correct that the error was odd, and when I set the error messages to give some out put, they revealed that some apostrophes that were in the long text blocks of input data were bonking the SQL syntax. So it had nothing really to do with the OJS.

So it even did me the favor of revealing where each "apostrophic" error was, and I just removed them all. Which is odd though, considering that these were stock information that came in the OJS by default. Do you think my host could have its mySQL settings a bit nitpicky? Is something like that common?

I don't much care now, since it's working brilliantly now, but it would be good to know.

thanks again,
best
NEM

[kevin]

Ah, I suspect magic_quotes_gpc is off in your php.ini settings. This setting is normally on by default, and ensures that quotes and backslashes in GET/POST/COOKIE data are escaped.

If you're using another application that requires magic_quotes_gpc to be off, you can enable it just for OJS via a .htaccess file (see http://ca.php.net/manual/en/function.ini-set.php )

[nmagee]

Kevin,

That did the trick. I'm glad PHP can be tweaked so easily via an .htaccess file -- I just dropped the one line in a file at the root dir of OJS, and it worked great.

thanks again for your very quick and helpful help!

Neal

[ramon]

Fellas,

I have one author with a problem submitting to the system. He claims his at step 2 of the submission, and when he submits the form he gets "Failed to insert into tblusers table" error.

I tried changing all the error messages to $db->error() but it didn´t work. Also, in step2.php that error message does not exist.

So I changed the submissionfunctions.php also..


I looked at magic_quotes and they are on..

[ramon]

The user was using the single quote character instead of the apostrophe...

[kevin]

The problem is probably in admin/include/submissionfunctions.php in the registerAuthor function. The data is not properly escaped before it is inserted into tblusers.

[ramon]

The problem was in the title the author was adding.

Maybe the problem is in the admin/include/metadataedit.php

This is the only function I found that deals with article metadata:

Code: Select all

if (isset($submit) && ($usertype == "editor" || $usertype == "admin" || ($usertype == "author" && empty($bPublished)))) {
   // Update submission metadata
   $query = "UPDATE tblarticles SET chMetaTitle = '" . (empty($chMetaTitle) ? _tr('UNTITLED_SUBMISSION') : $chMetaTitle) . "',
                            chMetaAbstract = '$chMetaAbstract',
                            chMetaSponsor_Author = '$chMetaSponsor_Author',
                            chMetaDiscipline = '$chMetaDiscipline',
                            chMetaSubjectClass = '$chMetaSubjectClass',
                            chMetaSubject = '$chMetaSubject',
                            chMetaCoverageGeo = '$chMetaCoverageGeo',
                            chMetaCoverageChron = '$chMetaCoverageChron',
                            chMetaCoverageSample = '$chMetaCoverageSample',
                            chMetaType_Author = '$chMetaType_Author',
                            chMetaType_Editor = '$chMetaType_Editor',
                            chMetaLanguage = '$chMetaLanguage',
                            chMetaSponsor_Editor = '$chMetaSponsor_Editor',
                            chMetaFormat = '$chMetaFormat',
                            chMetaRights = '$chMetaRights'
                            WHERE nArticleID = '$id'";
                           
                  $db->query($query) or die("Failed to update tblarticles table");



thanks

[kevin]

But you said the error occurred inserting into tblusers, not tblarticles?

Nonetheless I've checked in a fix for the author problem.

[ramon]

That´s exactly the point..

After I changed the error message to display the DB error, it showed the author that the error was in the input of the title. He changed the single quote for apostrophe and then it worked.

[Jakob]

Kevin,

Slashes inserted in the copyright statement can be troublesome as well. When submitting articles in step2 these slashes do generate table updating errors as the copyright statement is transfered into tblarticles. Or do I miss something...?

By the way, is there any complete french translation of locale available ? I have loosely translated the user interface and some parts of admin which I would be pleased to share with whoever is interested: see http://www.hisal.org

and last : submitted articles, but not yet approved or inserted in a volume, are listed in the Author's Index page ???? Should that be ?

Thanks for your patience, regards
Jakob Schlüpmann

[kevin]

Slashes inserted in the copyright statement can be troublesome as well. When submitting articles in step2 these slashes do generate table updating errors as the copyright statement is transfered into tblarticles. Or do I miss something...?

As long as magic_quotes_gpc is enabled (or left at the default setting) in your php.ini it shouldn't be a problem. If you have this setting disabled you will encounter many such problems.

By the way, is there any complete french translation of locale available ? I have loosely translated the user interface and some parts of admin which I would be pleased to share with whoever is interested: see http://www.hisal.org

Please contact John Willinsky (john.willinsky@ubc.ca) regarding this. I think there is a French translation underway but I'm not sure what its status is, so we may take you up on that offer.

and last : submitted articles, but not yet approved or inserted in a volume, are listed in the Author's Index page ???? Should that be ?

That should not occur. Did you happen to publish some articles individually in "publish by year" mode before switching to using issues? That could result in some inconsistencies like this.

[Jakob]

Kevin,

Quote :

As long as magic_quotes_gpc is enabled (or left at the default setting) in your php.ini it shouldn't be a problem. If you have this setting disabled you will encounter many such problems.

magic_quotes_gpc is enabled through .htaccess file in top OJS directory, but that particular problem is unanswered : I temporarly went around it escaping manualy the slashes in the copyright statement. See : http://www.hisal.org/submissions.php#copyright ("Cession de droits d'auteur").

That should not occur. Did you happen to publish some articles individually in "publish by year" mode before switching to using issues? That could result in some inconsistencies like this.

The journal has been configured in a "As part of a volume alone, by volume and year designated" mode from the beginning. But do you mean that if someone changed this setting temporarly and then went back to initial state, it might lead to such problems ? See : http://www.hisal.org/search.php?op=index

Thanks,
Jakob Schlüpmann

[kevin]

magic_quotes_gpc is enabled through .htaccess file in top OJS directory, but that particular problem is unanswered

Sorry, I wasn't thinking straight. That is indeed a bug.

Here is a fix that correctly escapes those values:

Code: Select all

Index: admin/author/step2.php
===================================================================
RCS file: /cvs/ojs/admin/author/step2.php,v
retrieving revision 1.54
diff -u -r1.54 step2.php
--- admin/author/step2.php      13 May 2004 08:40:47 -0000      1.54
+++ admin/author/step2.php      10 Jun 2004 08:04:26 -0000
@@ -69,9 +69,9 @@
 
        // Insert the editor-filled metadata for this submission
        $chMetaSource = addslashes($journaltitle);
-       $query = "update tblarticles set chMetaSponsor_Editor = '$chSponsor_Editor',
-                                                                               chMetaSource = '$chMetaSource',
-                                                                               chMetaRights = '$chCopyrightNotice'
+       $query = "update tblarticles set chMetaSponsor_Editor = '".addslashes($chSponsor_Editor)."',
+                                                                               chMetaSource = '".addslashes($chMetaSource)."',
+                                                                               chMetaRights = '".addslashes($chCopyrightNotice)."'
                                                                                where nArticleID = '$id'";
        $db->query($query) or die("Failed to update tblarticles table");

The journal has been configured in a "publish by year" mode from the beginning. But do you mean that if someone changed this setting temporarly and then went back to initial state, it might lead to such problems ? See : http://www.hisal.org/search.php?op=index

According to the home page of your site, you are currently in "publish by issue" mode. Your author index page seems to indicate that some articles were published individually in "by year" mode before the journal was switched to using issues.