Query not saving form data

I have a form that is supposed to submit data via hidden inputs and an ajax call to another script containing the query. It was working the other day and now seems to have suddenly stopped after adding the rest of the form fields.

The hidden inputs are being populated with geo location data from jquery and that is working - so the fields do have values in them.

I’m not sure what has changed to break it and after a second pair of eyes more than anything to see if someone here can spot an error with it.

Form code:

<p id="geolocation_status" style="display:none">Your current location was saved!</p>
        <form class="signup-form" action="" id="geolocation_submit" method="POST">
          <fieldset>
          <input type="hidden" class="geo-country-output" name="geolocation_country" id="geolocation_country" value="">
          <input type="hidden" class="geo-county-output" name="geolocation_county" id="geolocation_county" value="">
          <input type="hidden" class="geo-city-output" name="geolocation_city" id="geolocation_city" value="">
          <input type="hidden" class="geo-add-output" name="geolocation_address" id="geolocation_address" value="">
          <input type="hidden" class="geo-lat-output" name="geolocation_latitude" id="geolocation_latitude" value="">
          <input type="hidden" class="geo-long-output" name="geolocation_longitude" id="geolocation_longitude" value="">
          </fieldset>

        <input type="submit" class="btn-colored-update" value="Save Current Location" name="geolocation_submit" id="geolocation_submit" />
        </form>

            <!-- AJAX request to save the geolocation data -->
    <script type="text/javascript">
        // this is the id of the form
        $("#geolocation_submit").submit(function(e) {

            var url = "includes/process.php?do=save-geolocation"; // the script where you handle the form input.

            $.ajax({
                   type: "POST",
                   url: url,
                   data: $("#geolocation_submit").serialize(), // serializes the form's elements.
                   success: function (data) {
                           $("#geolocation_status").show();
                           setTimeout(function() { $("#geolocation_status").hide(); }, 5000);
                    }
                 });

            e.preventDefault(); // avoid to execute the actual submit of the form.
        });
      </script>

Query:

// Save Geolocation to Database ////////////////////////////////////////////////////////////////////////////////////////////////////////
	if ($do === 'save-geolocation') { 

		$user_id = $_SESSION['user']['id'];
		$user_name = $_SESSION['user']['username'];
		
		$geolocation_country = $_POST['geolocation_country'];
		$geolocation_county = $_POST['geolocation_county'];
		$geolocation_city = $_POST['geolocation_city'];
		$geolocation_address = $_POST['geolocation_address'];
		$geolocation_latitude = $_POST['geolocation_latitude'];
		$geolocation_longitude = $_POST['geolocation_longitude'];
	  	
		$sql = "INSERT INTO user_geolocations(user_id, user_name, geolocation_country, geolocation_county, geolocation_city, geolocation_address, geolocation_latitude, geolocation_longitude)
	    VALUES('$user_id', '$user_name', '$geolocation_country', '$geolocation_country', '$geolocation_county', '$geolocation_city', '$geolocation_address', '$geolocation_latitude', '$geolocation_longitude')";

	    $db->exec($sql);

		$activity_un = $_SESSION['user']['username'];
		$activity_location_city = $_POST['geolocation_city'];
		$activity_location_county = $_POST['geolocation_county'];

		// Create ativity item
	    $sql = "INSERT INTO user_activity(user_id,user_name,activity_type,activity_meta,activity_relatedid)
	    VALUES('$user_id','$activity_un','location-added','$activity_un added their current location. They are in $activity_location_city, $activity_location_county!','')";

	    $db->exec($sql);

	    header('Location: ' . $_SERVER['HTTP_REFERER']);
	}

You have duplicate IDs, so you probably submit only the button.

And you are wide open to SQL injection.

Thanks.

Yes - I’m fixing the SQL injection as I go through. It’s not available publicly yet.

Even when I change the ID’s and try submiting the form it still doesn’t work.

where does $do come from?

Additionally, you can check in the browser’s dev tools (network section) what data are sent to the server.

‘do’ is a URL variable that is passed in the ajax call. In this case ‘do’ equals ‘save-geolocation’ so that query is ran from my functions file.

The URL is includes/process.php?do=save-geolocation.

I’m checking the network tools but it says failed to load response data every time.

but unless PHP actively reads it from the URL (i.e. $_GET), it’s value is NULL.

even if the response fails, you can check the request data.

this idea is rather wrong, for one simple reason: a code is intended to be run by a computer, not read by a human.
To find a problem in the code one have to run it.
Ask your code to tell you its problems: Error reporting basics

1 Like

PHP is reading it… the query in my OP checks if the do equals save-geolocation then runs the code contained within the if statement.

And no I can’t check the data, because it is failing to load - nothing to do with the script Chrome just isn’t loading it.

that would imply that $do is read from the query, for which there is no code given that proves it. hence the question if $do really contains the value you expect it to have.

then, if the request doesn’t even start then there is something wrong with the JS, for which you should consult the JS error console.

It does contain the value.

The second SQL insert where it writes an item to another table works. That entry is being made in the same piece of code, the insert statement before is failing so something is wrong with the posting of the data from the form.

The JS works fine because as I have said it is sucessfully calling the script, with the value assigned to do and is triggering at least one of the record insertions the script is supposed to perform.

In this bit of code

$sql = "INSERT INTO user_geolocations(user_id, user_name, geolocation_country, geolocation_county, geolocation_city, geolocation_address, geolocation_latitude, geolocation_longitude)
VALUES('$user_id', '$user_name', '$geolocation_country', '$geolocation_country', '$geolocation_county', '$geolocation_city', '$geolocation_address', '$geolocation_latitude', '$geolocation_longitude')";

you name eight columns, but provide nine values. You have the country twice.

And, is there any need for this code at the end of the PHP?

header('Location: ' . $_SERVER['HTTP_REFERER']);

Surely as you’re calling from Ajax, all you need to do is exit()?

1 Like

Good spot :slight_smile: it still hasn’t fixed it though.

And looking at the second insertion of the script these variables

$activity_location_city = $_POST['geolocation_city']; 
$activity_location_county = $_POST['geolocation_county'];

are returned blank so something is definately up with the posting of the data from the hidden form.

Yes exit(); is all I need - it didn’t used to be ajax.

Are you sure those variables are blank, or is something else causing the issue? A Mr O’Connor, for example? I know you said earlier you were looking at security, but a prepared statement would eliminate quoting issues too.

1 Like

Yes, I’m not sure what else would cause them to be blank. I’ve been over the form code lots of times and can’t spot anything obvious.

Mr O’Connor?

… contains a single-quote, which would wreck the other single-quotes in the way you build your query. Admittedly it wouldn’t result in the fourth column having blank data in it, it would just stop the query completely. It might also not cause any trouble at all, if you prevent users from having a single-quote in their username, but it might upset things if they visit a place that contains one.

It makes no sense that the form variables are present and correct for the first query, not no longer present in the second query. If you use the variable names that you used for those same values in the first query (where they presumably had valid values), does that do anything differently?

Thats it though - I don’t think they are present at all, even though the data exists in the input fields. I don’t think they are posting but as I said they have earlier in the week.

Ah right. With it - users can’t have anything but numbers and letters in usernames. In this case there are only commas in one of the values (address) but no quotes or anything like that.

Oh, OK, so they don’t contain values in the first query either?

No I don’t think so. I can’t check because the database entry isn’t being made.

Right, then you need to be sure. The way I’d do it - because I don’t know much about Ajax and JS and so on, so don’t know any better way - would be I would have a couple of lines in your PHP just after you’ve done the first if():

// Save Geolocation to Database ////////////////////////////////////////////////////////////////////////////////////////////////////////
	if ($do === 'save-geolocation') { 
// for debug only
var_dump($_POST);
exit();

and then I’d change the Ajax call:

success: function (data) {
// for DEBUG only
alert(data);
//
$("#geolocation_status").show();
                

There’s probably far better ways to do it, but in theory that would pop up a JS alert that shows the values of the $_POST array as they arrive in PHP.

What causes the form variables to get populated with values? With sample values, does the query work from the MySQL console / admin?

Here is the debug info - http://prntscr.com/gr4g4k - looks like one field only is posting.

The fields are populated from the following navigator script (the input[name…);

<script type="text/javascript">
        navigator.geolocation.getCurrentPosition(success, error);

        function success(position) {

            var GEOCODING = 'https://maps.googleapis.com/maps/api/geocode/json?latlng=' + position.coords.latitude + '%2C' + position.coords.longitude + '&language=en';

            $.getJSON(GEOCODING).done(function(location) {
                $('#country').html(location.results[0].address_components[5].long_name);
                $('input[name="geolocation_country"]').val(location.results[0].address_components[5].long_name);
                $('#county').html(location.results[0].address_components[4].long_name);
                $('input[name="geolocation_county"]').html(location.results[0].address_components[4].long_name);
                $('#city').html(location.results[0].address_components[2].long_name);
                $('input[name="geolocation_city"]').html(location.results[0].address_components[2].long_name);
                $('#address').html(location.results[0].formatted_address);
                $('input[name="geolocation_address"]').html(location.results[0].formatted_address);
                $('#latitude').html(position.coords.latitude);
                $('input[name="geolocation_latitude"]').html(position.coords.latitude);
                $('#longitude').html(position.coords.longitude);
                $('input[name="geolocation_longitude"]').html(position.coords.longitude);
            })

        }

        function error(err) {
            console.log(err)
        }
    </script>

And all that works, I can see from my source code that the values are present in the fields.