• Resolved mikebian

    (@mikebian)


    I have several non WordPress tables I added to my database. Two of these I want to be able to edit from your plugin. Using Data Explorer, I can edit one of the tables with no problem. With the other, I can click on Edit, but when I try to save the row, I get an “ERROR: Not authorized” error message back and the row is not updated.

    I am using BlueHost and with them, I do not have true admin access. I created both tables in the same manner through phpMyAdmin, and data is inserted into both tables via PHP code that gets executed in a form submit, so WordPress definitely has at least INSERT permissions. Since both tables were created in the exact same manner, and I can update one of them through your app, I can’t see why the other table would not be updatable.

    Any thoughts? Thanks.

Viewing 15 replies - 1 through 15 (of 17 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Mike,

    Can you edit your WP options table? What are the names of the tables you cannot edit? Do they contain any special charaters? That might be an issue…

    Best regards,
    Peter

    Thread Starter mikebian

    (@mikebian)

    I was able to update and save a row in the ‘WP_OPTIONS’ table.

    The tables are ‘wp_AIT_Trips’, which works fine, and ‘wp_AIT_Registrations’, which gives the “ERROR: Not authorized” message. No special characters.

    Thanks for the quick reply!

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Mike,

    Can you export these tables from the Data Explorer main page? Click on link Manage > button Export. If it works, can you send me the export?

    Thanks,
    Peter

    Thread Starter mikebian

    (@mikebian)

    I was able to do the export but I can not send you all the data. The registrations contain my customer database and that has the private data of all my customers. I removed those insert statements from the file, and I can send you the table defs and data from the trips table. Not sure how to send it to you though.

    Thread Starter mikebian

    (@mikebian)

    Here are the table defs:


    — Create table {wp_prefix}AIT_Registrations

    CREATE TABLE {wp_prefix}AIT_Registrations (
    RegTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CapName varchar(100) NOT NULL,
    CapEmail varchar(100) NOT NULL,
    CapPhone varchar(100) NOT NULL,
    StkName varchar(100) NOT NULL,
    StkEmail varchar(100) DEFAULT NULL,
    StkPhone varchar(100) DEFAULT NULL,
    Addr varchar(100) NOT NULL,
    City varchar(50) NOT NULL,
    State varchar(50) NOT NULL,
    Zip varchar(10) NOT NULL,
    Country varchar(20) NOT NULL,
    Trip varchar(100) NOT NULL,
    EmergContact varchar(100) NOT NULL,
    EmergPhone varchar(50) NOT NULL,
    Cancelled char(1) DEFAULT NULL,
    TripStartDate date NOT NULL,
    PRIMARY KEY (RegTS) USING BTREE
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


    — Create table {wp_prefix}AIT_Trips

    CREATE TABLE {wp_prefix}AIT_Trips (
    id mediumint(9) NOT NULL AUTO_INCREMENT,
    trip varchar(200) NOT NULL,
    startDate date NOT NULL,
    endDate date NOT NULL,
    URL varchar(200) NOT NULL,
    status char(1) NOT NULL,
    spotsLeft int(11) NOT NULL DEFAULT ‘0’,
    PRIMARY KEY (id),
    UNIQUE KEY trip (trip,startDate),
    KEY trip_2 (trip,startDate)
    ) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=latin1;

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Mike,

    The table structure looks fine. I can import it and edit the table from the Data Explorer. Can you check your table settings for the back-end?
    > Go to Settings menu
    > Sub menu WP Data Access
    > Tab Back-end
    > Check: Table access

    Best regards,
    Peter

    Thread Starter mikebian

    (@mikebian)

    Table access is set to “Show WordPress Tables”. Everything else was checked except “no content wrap” and debug.

    I turned debug on and tried to edit the table again thinking I would get more info somewhere, but didn’t see anything. I checked the PHP log also. What does that actually do?

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Mike,

    Sorry, I don’t have a clue yet! I scanned the source code for message “ERROR: Not authorized”. It indicates one of the following issues:
    – You try to insert, update or delete, but this action is not allowed (restricted in the Front-end settings)
    – Your wpnonce is incorrect or expired (if so, it is strange this only happens with one table)

    Are you getting this error when you click on the edit link? What happens if you click on the view link? Can you delete rows?

    Best regards,
    Peter

    Thread Starter mikebian

    (@mikebian)

    So, I just tried inserting a row. That worked fine. Then I tried editing it and got the error. Then I tried deleting it, and that worked fine.

    View works fine.

    When I click edit, the edit page comes up. I then either make a change or just leave it alone, makes no difference. When I click “Save Changes To Database”, I get the error message.

    Thanks again!

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Do you have the latest version installed Mike?

    Thread Starter mikebian

    (@mikebian)

    WordPress v 5.3.2

    WP Data Access 3.0.2

    MySQL: Server version: 5.6.41-84.1 – Percona Server (GPL), Release 84.1, Revision b308619
    Protocol version: 10

    PHP version: 7.3 (ea-php73)

    Thread Starter mikebian

    (@mikebian)

    And I can edit those rows in phpMyAdmin just fine. I was trying to use your plugin to avoid having to go into phpMyAdmin to update them. But, with the way BlueHost works, a different user is used when you go into phpMyAdmin than what WP uses and you have no way of controlling that.

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    That looks good! I think everything is fine. I just don’t understand why this one table does not allow you to update.

    There are two things we can do:
    (1) I can add some debug message to some files and send you those files
    (2) You can check the values of some input items

    (1) This is only possible if you have ftp access so you can replace some specific files.

    (2) If you open the inspector and search for the input item with id _wpnonce you should see the following values:

    <input type="hidden" name="action" value="edit">
    <input type="hidden" name="action2" value="save">
    <input type="hidden" name="page_number" value="1">
    <input type="hidden" id="_wpnonce" name="_wpnonce" value="???">

    I would like to know what’s on the position of ???. Do the other items have the same values as those above.

    We can suspend this untill monday if you like…

    Best regards,
    Peter

    PS I suddenly have an idea! Column RegTS is your primary key. This is a date field and might be the problem. Could you add an auto increment column (like the other table)? You can still create a unique index on column RegTS.

    Dave

    (@dnuttall)

    I’ve been following this thread with great interest!

    Mike,
    Does BlueHost NOT allow you to create your unique database administration users and then allow you to assign one or more to each database?

    I run on DreamHost and their cPanel gives me total control over which users have access to databases, whether the access is via WordPress, phpMyAdmin or any other PHP code. (i.e., I have some apps that were created with SQL Maestro’s “PHP Generator).

    Just curious!
    Dave

    • This reply was modified 5 years ago by Dave.
    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Mike,

    It is column RegTS! Sorry, I overlooked this when I tried the first time. I wasn’t aware that it was happening on the save action.

    I dropped the primary key, added an auto increment column (id) and a unique on column RegTS like this:

    alter table wp_AIT_Registrations drop primary key;
    alter table wp_AIT_Registrations add column id bigint(20) unsigned auto_increment primary key;
    alter table wp_AIT_Registrations add unique index (RegTS);

    Now it works!

    Best regards,
    Peter

Viewing 15 replies - 1 through 15 (of 17 total)
  • The topic ‘ERROR: Not authorized’ is closed to new replies.