Arrest-MySQL API and X10 Free Hosting

Discussion in 'Scripts, 3rd Party Apps, and Programming' started by gigmex10, Apr 16, 2014.

  1. gigmex10

    gigmex10 New Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Is it possible to install and use "Arrest-MySQL - A "plug-n-play" RESTful API for your MySQL database." [Source - https://github.com/gilbitron/Arrest-MySQL] ?

    If it is, I would be grateful for any tips on making this happen.
     
  2. essellar

    essellar Community Advocate Community Support

    Messages:
    3,295
    Likes Received:
    227
    Trophy Points:
    63
    There's nothing to "install", you just have to have the four PHP files uploaded in place (the "index.php" is the request handler -- the page URL that your web users will talk to, the "config.php" is your database credentials info, and the two files in the "lib" directory are the classes that do all of the real work). Since you aren't allowed to run a "naked" API (you need to have a working web site on the account), you would need to use a subdirectory of public_html. (You can use as a subdomain or add-on domain to get a more "RESTfully pure" URL if having, say, "/api/" in the URL looks ugly to you.) There are no binaries or anything else special to deal with; it's just some standard PHP files that need to be uploaded.

    That leaves you with two problems. The first is that you need both the server and the client to understand and permit both the PUT and DELETE HTTP verbs/methods if you need to update and delete (POST and GET for the create and read part of CRUD can be taken for granted). You have no control over the server if you're not running a VPS (or dedicated/colo machine), so the server may or may not understand PUT and DELETE on any given day, depending on how the admins have felt about it lately. You never have control over the client except on your own machine, so if you're expecting users who may be tightly firewalled (at work or at school), or users with old browsers (and I'm really very sorry if that's the case) you may not have the PUT or DELETE verbs available to you.

    The second is something that's entirely under your control, and that's managing user actions. And, really, that's not safe at all. Remember that everything that happens in the database will happen as your database user who has all permissions. User authentication is not a problem; you can use the same authentication/session mechanism you are using for the rest of the site. (You just need to add session_start(), etc., to the API's index.php, and verify that the user is only doing things you want them to do. Yeah, that's kind of hand-wavy, I know, but it's the same sort of thing you'd have to do with an ordinary site.) The real problem is that you don't have access to HTTPS on Free Hosting, so your API and user credentials -- the session id and any other authentication tokens you may be using -- are exposed as plaintext to anyone that might be listening/sniffing. Since the whole point of the API is the next best thing to SQL injection (you can't create or drop tables, but you can delete everything in the table easily enough or upload malicious JSON), you need to be extremely careful about what you allow to happen before you create an ArrestMySQL object and really grok the fact that a malicious user or third party has all of the power that you don't deliberately take away from them. (Blacklisting actions is a fool's game. Forbid everything you don't whitelist.)

    So you have three tasks ahead of you if you want to use this. The first is to move the config.php to somewhere outside of your public_html and modify the include path. That way if the server ever serves PHP directly instead of processing it, you don't have your database, user and password showing up in anybody's browser. That's pretty standard.

    The second is to modify the index.php substantially to add all of the authentication and authorization/verification you need to limit user activity and make the API as safe as it can be over HTTP. And do keep it mind that "as safe as it can be over HTTP" isn't all that safe.

    The third, not mentioned at all so far, is to modify lib/db.php. It uses the mysql_xxx PHP functions, which have been deprecated in the current version of PHP. You can pretty much drop in the mysqli_xxx equivalents without too much work, and it would be a relative snap to use prepared statements. Because it's going to take some development and testing of index.php on a local machine to make this thing safe enough to use anyway, the extra few minutes it takes to update the mysql_xxx stuff to methods/functions that won't throw errors or warnings (and that won't simply go away in a few months or years) isn't going to be much of a burden.
     
  3. gigmex10

    gigmex10 New Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Thank you for writing such a detailed reply to this. Having spent the last thirty and more years writing monolithic, 'no-stack' software for PC networks, and before that IBM Mini-computers, I find this protocol and security stuff a bit of a rude shock. Not that I was ignorant of it. It has simply always been a responsibility I could gladly let others deal with.

    Much as the detail is fascinating (and, I have no doubt, good sense) I am nevertheless falling at the first hurdle. I am testing as I go using www.hurl.it to submit API requests, and getting it wrong. I have my four files loaded in a directory [Arrest-MYSQL] under public_html, (index.php and config.php with my db,user,pw etc assigned, and /lib/ to contain the other two files). Whatever combination I use, I cannot seem to arrive at the correct GET syntax. While I can see that without modification, what I am try to do is wrong, I cannot yet even do the 'wrong' thing successfully. What is the path which theoretically should work before adding these more secure modifications?
     
  4. essellar

    essellar Community Advocate Community Support

    Messages:
    3,295
    Likes Received:
    227
    Trophy Points:
    63
    Have you added the $base_uri parameter (a second parameter that's optional and defaults to "") to the ArrestMySQL object constructor call? Without that, all of the API's interpretation of the URI you use will be shifted left, so it will read "Arrest-MySQL" as the table, the table as a row id, etc. And do note that if you are testing with PHP 5.5 with the db.php as-is and haven't added an error_reporting() call to index.php to suppress the deprecation warnings, you may get a "200 OK" response status but you won't get valid JSON in the response body.

    There does seem to be a piece missing, though, and that's the .htaccess URL rewrite/redirect. The web server doesn't know from APIs by default, and if the request is aimed at http://server.domain.tld/dir/table, it's going to try to serve a resource that lives at dir/table in your public_html -- but index.php lives in dir and table is a figment of the API's imagination. So you get a 404. That's the sort of missing piece that earns software the coveted Works on My Machineā„¢ certification, and it's something so obviously expected that I never noticed it wasn't there. (It's like entering a dark room you intended to enter. You may feel around for a light switch, but you probably wouldn't think to make sure that there was a floor installed under the carpet first.) You need a rewrite rule in your API directory (or in your public_html) that does an internal redirect back to the base of the API directory so that index.php can handle all requests. Make sure that it's an internal redirect, and that the rewrite is NOT passed through (you need the REQUEST_URI value to remain the original value so that the parts can be parsed out).

    At this point, you may be thinking that there are enough modifications needed that you may as well use what's there as a sort of rough guide to a complete rewrite, and I'd be inclined to agree with you.
     
  5. gigmex10

    gigmex10 New Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Ooops - I forgot to mention this. There IS a .htaccess and it contains this:

    <IfModule mod_rewrite.c>
    RewriteEngine on
    RewriteCond %{REQUEST_FILENAME} !-f
    RewriteCond %{REQUEST_FILENAME} !-d
    RewriteRule ^(.*)$ index.php/$1 [L]
    </IfModule>

    In reference to your last paragraph - yes, it does make one nostalgic for the days when a language, or a database for instance was a pretty bomb-proof entity.
     
  6. Skizzerz

    Skizzerz Contributors Staff Member Contributors

    Messages:
    2,929
    Likes Received:
    117
    Trophy Points:
    63
    That has never been the case, it's just that 20 years ago people were far less concerned about ensuring their code was secure (it wasn't secure at all, it just wasn't a concern at the time). The security features of both programming languages as well as databases has improved orders of magnitude since the "good old days".
     
    • Like Like x 1

Share This Page