#3504 Storing RSA Keys in a database issue...

3.4.9
works-for-me
Marc Delisle
5
2013-06-11
2012-01-16
Paul Kirby
No

Hello All

I am one of the main developers to a browser based game and I am currently setting up an Auth server to store all the users account information,
this uses OpenSSL with RSA key pairs to talk to and from our Auth Server to Game Servers so that we only pass hash to the game servers which is then used to requiest the required user infomation, so we have secure communication between the two servers.

The issue that I am having is I am trying to store the RSA Key Certifcates into the database but when I insert all the information via the insert GUI its displays the following:
"You don't have permission to access /URLPATH/tbl_replace.php on this server."

This also happens if I try it with the root login which has full access (MySQL and Appache) so its not an access issue.

However it works fine if I add the following into the SQL GUI:
INSERT INTO `servers2` (`id`, `game`, `url`, `hash_id`, `caption`, `description`, `status`, `type`, `last_reset`, `next_reset`, `duration`, `iduration`, `cert`, `cert_pw`) VALUES
(NULL, 'gamename', 'gameurl', 'gamehash', 'gametitle', 'gamedesc', 'disabled', 'invite', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'gameduration', 0, '-----BEGIN CERTIFICATE-----\nKEYINFO_REMOVED\n-----END CERTIFICATE-----\n', '');

Also once its all inserted fine I can no longer edit that row via the GUI, however I can edit it if I use the SQL GUI and enter SQL to change the required data.

Not too sure, but I think it may be down to the '-----BEGIN CERTIFICATE-----' and the '-----END CERTIFICATE-----' that is wrapped around the Certificate data.
I can strip those lines and base64_decode the data and then store that as binary data thats works fine, but I shouldn't need to do all that, I should be able to strore it as text with the wrappers.

So is there any chance in this being fixed because I am ATM entering them in by hand while I am working on my Admin page.

Let me know if you require any more information.

Thanks in advance Paul

Discussion

1 2 > >> (Page 1 of 2)
  • Marc Delisle
    Marc Delisle
    2012-01-29

    Please attach here the export of your table's structure, and a screenshot taken when you are trying to insert.

     
  • Marc Delisle
    Marc Delisle
    2012-01-29

    • status: open --> pending
     
  • Paul Kirby
    Paul Kirby
    2012-01-29

    Image of the error that is displayed.

     
  • Paul Kirby
    Paul Kirby
    2012-01-29

    Hello Marc

    I have added the exported SQL information along with an image of what I am trying to insert and an image of what is displayed when I click on the Go button.

    Its down to the wrapper around the text that I am trying to insert like for example the following will fail:
    -----BEGIN PUBLIC KEY-----
    MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDGCz+Jq+McrSuSpWLFz+/rV4sn
    yEn0JVHTWbePnS4YONCMz8K86M6X6HHVtAtxTOqMmRhZqf4jd26tjE7KeV4ySJKD
    9ueN6+QZLnC7PmlE1EEjI/MLNH87ICgO/K7b//w2cpZeZGJSAsVHwuoP/P6n4Q4H
    aQhTocpjtukcz0lwPQIDAQAB
    -----END PUBLIC KEY-----

    However the following will insert fine:
    [BEGIN PUBLIC KEY]
    MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDGCz+Jq+McrSuSpWLFz+/rV4sn
    yEn0JVHTWbePnS4YONCMz8K86M6X6HHVtAtxTOqMmRhZqf4jd26tjE7KeV4ySJKD
    9ueN6+QZLnC7PmlE1EEjI/MLNH87ICgO/K7b//w2cpZeZGJSAsVHwuoP/P6n4Q4H
    aQhTocpjtukcz0lwPQIDAQAB
    [END PUBLIC KEY]

    It seems that phpMyAdmin doesn't like ----- (5 minuses) being used in the text.

    Its strange that it works fine when I insert it via the mysql console and by another mysql admin page, but I would like for phpMyAdmin to allow this due to its the prefered admin application for mysql.

    Let me know if there is anymore info that you require.
    Thanks in advance.
    Paul

     
  • Paul Kirby
    Paul Kirby
    2012-01-29

    • status: pending --> open
     
  • Marc Delisle
    Marc Delisle
    2012-02-01

    Paul,
    I have a problem with your keypairs.sql.txt which does not contain sql code. Try again, this time with a .sql extension.

     
  • Paul Kirby
    Paul Kirby
    2012-02-01

    It does when I download and view it...

    It has a CREATE TABLE and INSERT INTO blocks, but I will re-upload another version with the .sql extension.

     
  • Paul Kirby
    Paul Kirby
    2012-02-01

    Export SQL + 1 example record zipped

     
    Attachments
  • Paul Kirby
    Paul Kirby
    2012-02-01

    SF must be doing stuff because I am having issues uploading and test downloading it, keeps saying the files don't exist when it does, if using Fire Fox just keep clicking on the [Try Again] button and it will download.

    If you still are having issues downloading the zipped sql file then here is the CREAT TABLE block...

    CREATE TABLE IF NOT EXISTS `keypairs` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `owner_id` int(10) unsigned NOT NULL,
    `private_key` text NOT NULL,
    `public_key` text NOT NULL,
    `passphrase` varchar(25) NOT NULL,
    `enabled` enum('Y','N','O') NOT NULL DEFAULT 'N',
    PRIMARY KEY (`id`),
    KEY `owner_id` (`owner_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

    I would insert the INSERT INTO block but its very big and messy, but hopefully you can down load the zipped SQL file.

    Give me a few mins to upload the fils to my server, I am getting fedup with SF atm.

     
  • Paul Kirby
    Paul Kirby
    2012-02-01

    Ahhhh, I have sent you an email with the link to my site with those files.

     
  • Marc Delisle
    Marc Delisle
    2012-02-12

    • assigned_to: nobody --> lem9
     
  • Marc Delisle
    Marc Delisle
    2012-02-12

    Paul,
    I cannot reproduce your problem, inserting your sample data with the 5 minuses in your table. Here is my setup:
    phpMyAdmin 3.4.9
    PHP 5.3.10 (no Suhosin extension)
    MySQL 5.6.4-m7
    mysqli extension, mysqlnd

    Can you try reproducing it on http://demo.phpmyadmin.net, choosing the latest stable version?

     
  • Marc Delisle
    Marc Delisle
    2012-02-12

    • status: open --> pending-works-for-me
     
  • Paul Kirby
    Paul Kirby
    2012-02-12

    That is strange, it seems to work fine on the latest stable version which is the same version as mine.

    I will have to look at the settings that you have set and compare them to our one.

     
  • Paul Kirby
    Paul Kirby
    2012-02-12

    • status: pending-works-for-me --> open-works-for-me
     
  • Paul Kirby
    Paul Kirby
    2012-02-12

    Well this is doing my head in :(
    We have the following installed on our server:
    Apache/2.2.14
    MySQL 5.1.41 (also has mysqli)
    PHP/5.3.2-1
    phpMyAdmin 3.4.9 (English only tar.gz)
    However we seem to have Suhosin Patch 0.9.9.1
    For some reason it says its installed yet it hasn't, and no matter what I do I cannot get rid of it :(

     
  • Marc Delisle
    Marc Delisle
    2012-02-14

    • status: open-works-for-me --> pending-works-for-me
     
  • Marc Delisle
    Marc Delisle
    2012-02-14

    There is a newer Suhosin Patch (0.9.10), can you try it? Also, can you compile your own PHP 5.3.2 and see what happens?

     
  • Paul Kirby
    Paul Kirby
    2012-02-17

    • status: pending-works-for-me --> open-works-for-me
     
  • Paul Kirby
    Paul Kirby
    2012-02-17

    Hello Marc
    Sorry for the long delay, have been very ill, I will try and see if i can get that patch, as for compiling a fresh copy of PHP 4.3.2 That may be an issue due to the owner of the server prefers to use stable release and only resorts to a compile when it fixes a security hole etc, however I can setup a virtual server which I have done many times in the past (to find bugs etc), and see if it has any isses with the stable release of PHP etc as well as a compiled version.

    I will get back to you with the result.

    Thanks
    Paul

     
  • Paul Kirby
    Paul Kirby
    2012-02-17

    Ok getting the owner of server (i.e brother busy working) to update stuff is like getting blood from a stone, soooo I have setup up a virtual server at home running ubuntu-11.04-server-amd64 and have installed the very basics like SSH, Apache, PHP 5.3.5-1 (comes with Suhosin Patch 0.9.10), MySQL 5.1.54 and phpMyAdmin 3.3.10 which install all fine without any issues.

    Now when I create the database, table and insert (using the insert gui) it works fine so thats all good there.
    So it must be either a PHP (along with Suhosin 0.9.9.1) not tried not tried the same version of PHP that we have on our live server also haven't tried phpMyAdmin 3.4.9 on here yet due to I have download, unpack, move to a web folder, setup a vhost etc by hand and I wanted to try the quickest way first.

    The only issue is that PHP 5.3.5 isn't available for our live server (could be down to the type that he has set for apt) so I will have to have a word with him later.

    At least we now know that it has worked for me now, just have to wait for that version to apear on our server :)

    I will get back to you once we get that PHP version installed and have tested the insert.

    Once again thanks
    Paul

     
  • Marc Delisle
    Marc Delisle
    2012-03-02

    • status: open-works-for-me --> pending-works-for-me
     
  • Marc Delisle
    Marc Delisle
    2013-01-18

    • status: pending-works-for-me --> open
     
  • Marc Delisle
    Marc Delisle
    2013-06-01

    • status: open --> closed-works-for-me
     
1 2 > >> (Page 1 of 2)