Inserting into an mySQL database from a local html/javascript website

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);
?>

Is requirement to only post data? Or is a response expected by user? — guest271314
– guest271314, Commented Mar 5, 2017 at 5:34
@guest271314 I'm hoping to carry out two requests from the local HTML/JS website to the online mySQL db table. The first would simply insert a row into the online mySQL db table mentioned above. The second would determine whether a particular row existed within the online mySQL db table and if determined as present, a TRUE or FALSE value would be returned. — user7631121
– user7631121, Commented Mar 5, 2017 at 15:14
If user needs to be online to post and get data, why do you not create an html document online for user to submit <form>, and get response from server? — guest271314
– guest271314, Commented Mar 5, 2017 at 16:47
If I understand you right, you're asking if I want the user to be capable of inserting data into the online mySQL db table, why not simply put the whole website online. The reason I don't want to put full website online is, I want the website itself to be completely private to a number of selected users. But information can be shared between these local HTML/JS web-pages via the use of data provided by and uploaded to the online mySQL db table. If I've misunderstood what you mean please correct me, as I'm really hoping for an applicable solution to the above mentioned problem. — user7631121
– user7631121, Commented Mar 5, 2017 at 17:30
Not put website online. Provide a means for user to exchange data with server online. — guest271314
– guest271314, Commented Mar 5, 2017 at 17:39

holyknight · Accepted Answer · 2017-03-04 02:42:20Z

0

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.

answered Mar 4, 2017 at 2:42

holyknight

3151 gold badge3 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:54:02Z

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

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

Collectives™ on Stack Overflow

Inserting into an mySQL database from a local html/javascript website

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related