Inserting into an mySQL database from a local html

Question

What I'm trying to do

The intention of my program is to insert data from a local HTML/JS website into an online (non-local) mySQL database.

What I've attempted so far

The original method I was attempting to utilise to accomplish this was to, have my local website utilise javascript to post data via an online PHP file, then have this PHP file insert this information into a mySQL table. But I kept receiving cross-origin request related errors.

After reaching a wall programmatically, I opened up a Stackoverflow Thread to determine whether it was even possible to post from a local website to an online PHP file, during which I was expertly informed this wasn't possible without modifying Chrome related policies on each machine attempting to visit this local website.

Purpose of this thread

To determine whether rather than posting via a PHP file to enable the inserting of data into a mySQL table from a local HTML/JS website, if there is another approach I've not considered.

Reasoning for not modifying browser policies:

I don't have control over which policies are implemented on user browsers or which browser they choose to utilise. Similarly I'm not able to install additional software onto their systems e.g. Apache etc.

Overall Problem

As the methods I've attempted so far to post from a local HTML/JS website as a means to insert into a mySQL database have so far been unsuccessful. I've posted here to determine whether there was another approach that I have not yet considered rather than posting data via PHP files, which could be applied to my source code, rather than a user's web-browser to allow for the execution of mySQL queries initiated from a local HTML/JS website?

JS Code:

function uploadPetData(petName, petAge, petType) {

    var urlString ="Pet_Name="+petName+"&Pet_Age="+petAge+"&Pet_Type="+petType;
    $.ajax({
        type: 'POST',
        url: 'http://example.com/test.php',
        crossDomain: true,
        data : urlString,   
        contentType:contentType,   
        success: function(responseData, textStatus, jqXHR) {
            var value = responseData.someKey;
        }
    });
}

PHP Code:

<?php
$con=mysqli_connect("...","...","...","...");

$petName = $_POST['Pet_Name'];
$petAge = $_POST['Pet_Age'];
$petType = $_POST['Pet_Type'];

$petName = mysqli_escape_string($con, $petName);
$petAge = mysqli_escape_string($con, $petAge);
$petType = mysqli_escape_string($con, $petType);

$query = mysqli_query($con, "INSERT INTO Pets (Name, Season, Episode)
                             VALUES ('$petName', '$petAge', '$petType')");
mysqli_close($con);
?>

Answer 1

I would recommend to use server-side js as standalone client on each machine if you plan to deal with db directly. Browser js wasn't designed for this kind of interactions.

If you decide to keep using browser js you'll need a rest api to accomplish your objectives. You'll also need to mount your files into a local webserver (nginx or apache) in order to deal with cross-site problems of your client-side js. After that you can simply interact with your endpoints with plain ajax or promises or something more advanced like RxJs Observables.

Hope it helps.

Answer 2

Overall Problem

As the methods I've attempted so far to post from a local HTML/JS website as a means to insert into a mySQL database have so far been unsuccessful. I've posted here to determine whether there was another approach that I have not yet considered rather than posting data via PHP files, which could be applied to my source code, rather than a user's web-browser to allow for the execution of mySQL queries initiated from a local HTML/JS website?

The requirement is not possible at a browser with default settings; or without user action allowing the application access to their OS local filesystem, and make cross domain requests.

What you are trying to achieve requires some form of user action

• User lauches browser with specific flags or preferences set at browser to allow cross origin request from file: protocol;
• User installs a browser extension or app, with appropriate permissions set to perform cross origin requests; for example chrome.fileSystemProvider; chrome.syncFileSystem; PowerShell - FileSystem Provider; see also Method for streaming data from browser to server via HTTP, jQuery File Upload Plugin: Is possible to preserve the structure of uploaded folders?;
• User utilizes a web server at browser Opera Unite; POW -- Plain Old Webserver; Embedding an HTTP Web Server in Firefox OS; Use Firefox OS as web server; Building web server using chrome.socket API; Is it possible to embed a HTTP server in a Google Chrome extension?;
• User installs a shell script, sets appropriate permissions to allow shell script to read local file and use OS networking tools to communicate with server.

Alternatives

• Adjust requirement, create a <form> or <input type="file"> element at an online html document, where user can perform user action to upload file;
• Use "cloud storage";
• Provide user with adequate instructions as to how to make a POST request at command-line, for example, using cURL;
• As described at Answers at linked Question, provide guidance for user to launch chromium with --diable-web-security, and possibly --allow-file-access-from-files flags set; Cross-Origin Requests (CORS) in Internet Explorer, Firefox, Safari and Chrome; Disable cross domain web security in Firefox.

The issue is once an external application has read-write-execute permissions at user local filesystem, the script could be a potential security risk.

If user is in agreement with the potential security risk of allowing your application to read, write and execute arbitrary scripts at their local filesystem, they should be in agreement with either a cloud storage solution, for example, a service that you compose; using and online <form> to upload files; or omitting browser from procedure altogether and performing the request to POST files at command-line of the given operating system.

As discussed in detail above, the HTML/JS website is being stored locally. It is just the MySQL db being stored online. The intention for this project is, the user would make decisions on the local private HTML/JS website, then these decisions would he reflected within the online mySQL db. Such a data-transfer would occur in an automated manner. Hence my original attempt to relay this information from JS to PHP to MySQL. As such, for both automation and privacy reasons, placing a form online for users to manually enter data into would be inapplicable.

The command-line option being the most suitable to being "automated", for example, using cron

• How do I setup a cron job on OS X to run a curl command at a specific time every day?
• How do you run a crontab in Cygwin on Windows?
• Using curl to automate HTTP jobs

It is interesting to note that you have not mentioned authenticating the users whose filesystem your application would have access to, and whose local files are being stored in a remote MySQL database

• How to upload (FTP) files to server in a bash script?

• Setting up a cron for transferring a file through FTP at 5 PM daily

• Copying a file to a remote ftp server with bash