0

I have a JSON object, which I have converted into a JSON string using the JSON.stringify() method in JavaScript. I then insert this into the database using AJAX posting to a PHP file.

     $("#saveToDatabase").click(function(){
   var place = searchBox.getPlaces();
   var locationJson = JSON.stringify(place[0]);

   $.ajax({
    type: "POST",
    url: "insertLocation.php",
    dataType:"json",
    ContentType:"application/json",
    data:  {
      locationJson : locationJson
  },
    cache: false,
    success: function(result){
     window.alert("successful upload!");
    }});
  });
}

    <?php
require_once("connection.php");



if(isset($_POST["locationJson"])){

    $locationJson = $_POST['locationJson'];

$query ="INSERT INTO Locations (json) VALUES ('$locationJson')";
        $statement = $pdo ->prepare($query);
        $statement->execute();

}

?>

The problem I am having is that at somepoint as the data is being uploaded, the backslash "\" is being removed from my JSON strings. So when I select them from the database and try to manipulate them again in JavaScript, they are no longer valid JSON objects.

I am using SQL to upload to PHPmyAdmin.

Does anyone know a way around this? I need to store my JSON strings on the database without them being invalidated.

Cheers!

James
  • 659
  • 1
  • 5
  • 25
  • 1
    A few things: 1. PHPMyAdmin lets you admin mysql, but you wouldn't _"upload to PHPMyAdmin"_. Are you using MySQL? Or another SQL language? Also, would be helpful to see: a. The string immediately before you insert, b. The string as it sits in the db, then c. The string after you request it.... – random_user_name Jun 08 '18 at 20:37
  • Beware of sql injection :) You could use `$locationJson = json_encode($_POST['locationJson']);` – Liora Haydont Jun 08 '18 at 20:38
  • Maybe duplicate with https://stackoverflow.com/questions/12770704/mysql-is-not-inserting-a-backslash – Dale Nguyen Jun 08 '18 at 20:38
  • `contentType: "application/json"` is wrong. `$.ajax` sends URL-encoded data. The only JSON is `locationJson` because you called `JSON.stringify()` to create it. – Barmar Jun 08 '18 at 21:41

2 Answers2

2

Use a parametrized query instead of substituting the variable into the string.

$query ="INSERT INTO Locations (json) VALUES (:json)";
$statement = $pdo ->prepare($query);
$statement->bindParam(':json', $locationJson);
$statement->execute();

See How can I prevent SQL injection in PHP? for explanations about why substituting variables into SQL queries is a bad idea, even if you use functions like addslashes() to try to escape them.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Does perform an "add slashes" that would escape the backslashes and solve OP's problem? Or is it just an improvement to prevent SQL injection? – random_user_name Jun 08 '18 at 21:05
  • 1
    @cale_b `$pdo->quote($locationJson)` would be better than `addslashes()`, but parameters are universally considered the best way. – Barmar Jun 08 '18 at 21:10
  • Thanks! That helps! (And, part of what I was driving at is it might be nice to add a note to the answer to clarify that aspect.... ) – random_user_name Jun 08 '18 at 21:12
  • I've added a link to the canonical dupe about SQL injection. – Barmar Jun 08 '18 at 21:16
  • This fixed the problem for JSON string insertion into the database, however when I select them from the DB - it seems to be removing the backslashes once again! Can you point me in the right direction? `function getMarkers(){ global $pdo; $query ="SELECT json FROM Locations"; $statement = $pdo ->prepare($query); $statement->execute(); $markers = $statement->fetchall(); return $markers; }` – James Jun 08 '18 at 21:36
  • That code doesn't remove the backslashes. It must be happening when you use `$markers` later. – Barmar Jun 08 '18 at 21:39
  • The plot thickens... I believe I have found the perpetrator: `$markerArray = getMarkers(); $firstLocation = $markerArray[0]; $firstLocation = implode("", $firstLocation);` I think the implode is messing me up - however If I dont implode, then the value of $firstLocation is just "Array". – James Jun 08 '18 at 21:51
  • Why are you using JSON in the first place? What is `place[0]`? – Barmar Jun 08 '18 at 21:52
  • You should use `$firstLocation = json_decode($markerArray[0]['json'], true);`. Then `$firstLocation` will be a PHP array if `place[0]` was a Javascript object or array. – Barmar Jun 08 '18 at 21:54
  • `fetchAll()` returns a 2-dimensional array. – Barmar Jun 08 '18 at 21:55
  • @Barmar each JSON string represents information about a location on a map. I will be able to use the details from each JSON string in the array to place a marker on the map. – James Jun 08 '18 at 21:59
  • So `place[0]` is an object? Use `var_dump($firstLocation);` after my above code. – Barmar Jun 08 '18 at 22:01
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/172791/discussion-between-viktor-and-barmar). – James Jun 08 '18 at 22:14
0

It's always possible to fix escape eaters. I've found when I store JSON strings, I inevitably end up dealing with the same problems later on. Since I never search stored objects in SQL, I generally just convert them to base64. So:

btoa(JSON.stringify(place[0]))

Sadly, some base64 characters cause issues in URL's, filenames, etc., so I actually use a modified base64 for URL's. In javascript:

function safeobj(ob) {
    return btoa(JSON.stringify(ob)).replace(/\+/g, "-").replace(/\//g, "_").replace(/=/g, ".");
}

This pretty much gets through anything unscathed. For me, its worth the bit of extra storage to avoid constantly dealing with these various issues.

If you are supporting IE9-, you will need a shim or polyfill for btoa.

wordragon
  • 1,297
  • 9
  • 16