regex,PHP- remove single , double quotation and backslash from a complex json string

Question

The following snippet is just a part of a larger json. This json is passed through an ajax call from client side to server side in PHP. The json structure has the value attribute in many places. And this json is produced from a jQuery plugin.

Code snippet:

{"1": {"value":"abcdefg","style": { "autoFormatter":{} } } }

Before saving the value in database, I need to remove any single quotation ('), double quotation ("") or backslash () from the value part (i.e. abcdefg) of the value attribute. So I am trying to run a regular expression in the full json so that any occurrence of the previously mentioned characters is removed .

For example "value":"xyz'abc" should be changed into "value":"xyzabc"

What should be the proper way ?

[`json_decode`](http://php.net/json_decode) and [`str_replace`](http://php.net/str_replace) or [`preg_replace`](http://php.net/preg_replace) afterwards? It's difficult to tell what your concrete trouble is. Somewhat seems like you attempted to modify the whole JSON string instead of just the value. — mario, Dec 16 '18 at 18:18
@mario, my intention is to modify the value attribute's value only, not the whole json — Istiaque Ahmed, Dec 16 '18 at 18:19
Or you could use parameterised and bound queries and then you would not have any problems storing this in the database — RiggsFolly, Dec 16 '18 at 18:30
@Istiaque Ahmed Can you explain why you need to remove this charachters? As RiggsFolly mentioned, there is no issue storing this in the database. — Steffen Mächtel, Dec 16 '18 at 18:35
Yes sure. Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Dec 16 '18 at 18:37
@SteffenMächtel, the reason is once the value with the mentioned characters is stored in DB, I need to fetch it later from DB and hold it in a javascript variable. Then I call a `fromJson` method of the jQuery plugin I am using. But If those characters are present , then the JS variable shows error (malformed JSON etc). ANy way to store that value in JS with the mentioned characters included in it ? — Istiaque Ahmed, Dec 16 '18 at 18:40
@RiggsFolly, any way to solve the issue mentioned just in my previous comment ? — Istiaque Ahmed, Dec 16 '18 at 18:41

LSerni · Answer 1 · 2018-12-16T18:29:05.857

2

The simplest way would be, I think,

import the JSON into a PHP array
walk the array, recursively
store it back into a JSON string.

Something like:

$arr = json_decode($str, true);

// Modify $arr.
array_walk_recursive($arr, function(&$value, $key) {
   if ('value' === $key) {
       $value = str_replace([ '"', "'", '\\' ], '', $value);
   }
});

$str = json_encode($arr);

Using regular expressions to parse a JSON string can probably be costrued as "thanks, but no".

Older PHPs

function cleanvalues(&$value, $key) {
   // Programmers from Blefuscu usually write this as
   // if ($key == $value)
   // -- feel free.
   if ('value' === $key) {
       // Haven't tested this, and don't think it's faster, but
       // regexes can be used here:
       // $value = preg_replace('#["\'\\\\]+#', '', $value);
       $value = str_replace(array('"', "'", '\\'), '', $value);
   }
}

array_walk_recursive($arr, 'cleanvalues');

edited Dec 16 '18 at 18:29

answered Dec 16 '18 at 18:17

LSerni

55,617
10
65
107

what is about the backslah ? – Istiaque Ahmed Dec 16 '18 at 18:18
Oh, I had forgotten it along the way. Fixed. – LSerni Dec 16 '18 at 18:19
can you reply to this comment https://stackoverflow.com/questions/53805089/regex-php-remove-single-double-quotation-and-backslash-from-a-complex-json-st?noredirect=1#comment94459058_53805089 ? – Istiaque Ahmed Dec 16 '18 at 18:49
@IstiaqueAhmed I have added a different answer, since your *real* question is, I think, also a different one. – LSerni Dec 16 '18 at 19:16

score 0 · Answer 2 · answered Dec 16 '18 at 19:15

0

The answer to a slightly different question

This is what is called a "XY problem". You are trying to solve problem Y ("Removing SQL-problematic characters from JSON") when your actual problem is problem X:

I need to fetch it later from DB and hold it in a javascript variable. Then I call a fromJson method of the jQuery plugin I am using.

So what you need is: "a way of storing a textual value in a SQL-neutral, JSON-neutral, Javascript-neutral representation".

You can do this by using Base64. Use Base64 as the "common language" between your app layers - SQL, Javascript and PHP. Then decode values when they enter a boundary, and encode them when they go out again:

$value = "string with weird JSON that won't go into SQL";
$val2  = base64_encode($value);
...now $val2 is stored into SQL and can be retrieved with no problems
(search can be difficult but can be done, somewhat).

Then you retrieve the $val2 and can store it into Javascript with no problems, only remember it's base64. Yes, it'll take somewhat more space, but nowadays is that really a problem?

// Value recovered from SQL through PHP
var base64val = '{$val2}';
// base64val is a safe string, but useless.
var trueval = btoa(base64val);
// trueval is a JSON string, but not yet decoded.
var fromjson = JSON.parse(trueval);
// finally fromjson holds the real value

Now, you have a workflow into which anything goes - you can put slashes, dollar signs, quotes, double quotes and, more importantly, a lot of UTF8 characters that would have wrecked my previous answer (you may want to seek some thrills reading about "malformed UTF8 attacks and where to find them").

answered Dec 16 '18 at 19:15

LSerni

55,617
10
65
107

I have a big JSOn data, that has in many places the `"value"` attribute. This attribute holds data which I am trying to modify. So should I run `base64_encode` on the whole JSOn structure ? – Istiaque Ahmed Dec 16 '18 at 19:25
Yes. That will solve all your data representation problems (unless there are more layers yet to the question:-) ) – LSerni Dec 16 '18 at 19:59
`var base64val = '{$val2}';` - why do you need the curly braces ? – Istiaque Ahmed Dec 16 '18 at 20:32
Quoting f rom what you said : ` var fromjson = JSON.parse(trueval); // finally fromjson holds the real value` - I do not need to parse the JSOn. I get a JSON value from the JS plugin application, send it to server side with `JSON.stringify`, store it in DB and then fetch again in PHP and then take it in a JS variable and then call the plugin method with the JSON as the parameter and then it is supposed to work. - this is the work flow. – Istiaque Ahmed Dec 16 '18 at 20:40
@IstiaqueAhmed because you're decoding a *string*. The resulting output of that line from PHP would be valid Javascript, e.g. `var base64val = 'eW91IHJlYWxseSBkZWNvZGVkIHRoaXM/Cg==';`. You can then run `btoa()` on the sequence, and get the JSON code even if it contains strange characters (as long as it was valid JSON when it was encoded). – LSerni Dec 16 '18 at 20:40
`var trueval = btoa(base64val);` reverts the encoded string to what it was before . The relevant part is `{"value":"aabc'def"` and the JS variable just breaks at the position of the apostrophe – Istiaque Ahmed Dec 16 '18 at 20:41
I mistakenly said in my earlier comment that I do not need to parse the JSOn. Actually I need it and I do it – Istiaque Ahmed Dec 16 '18 at 20:46
Sorry - long day, not really up to a chat. I'd suggest you integrate the question with a minimum example of what you need to achieve, and in particular, what plugin you're feeding the JSON to. Or a screenshot of the actual problem. I'm afraid "the JS variable breaks" is not something I am familiar with. – LSerni Dec 16 '18 at 20:51
I'll do that and post here – Istiaque Ahmed Dec 16 '18 at 20:56

Steffen Mächtel · Answer 3 · 2018-12-16T21:24:00.687

0

I created a small test to figure out the problem. First i thought u have a problem writing the json string into the database, becouse you dont use any kind of "escape_string" or bind param.

--- UPDATE ---

Just use the json string as an object in html later without the use of $.parseJSON(jsonString).

<?php
/*
  CREATE TABLE `table_53805089_test` (
  `id` int(11) NOT NULL,
  `json` text COLLATE utf8_unicode_ci NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 */

// Example json string from ajax call
$jsonFromAjaxRequest = '{"1":{"value":"ab\"cd\'efg","style":{"autoFormatter":{}}}}';

// Connect to database
$mysqli = new mysqli('localhost', 'root', '', 'stackoverflow');

// Write into database
$query = 'REPLACE INTO `table_53805089_test` 
              (`id`, `json`) 
              VALUES (1, \'' . $mysqli->escape_string($jsonFromAjaxRequest) . '\')';
$mysqli->query($query);

// Read from database
$query = 'SELECT json FROM `table_53805089_test` WHERE id = 1';
$result = $mysqli->query($query);
$row = $result->fetch_assoc();

// Use in html -> javscript variable
?>
<!DOCTYPE html>
<html>
    <head><title>test</title></head>
    <body>
        <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
        <script>
            // Error:
            // jsonString = '<?php echo $row['json']; ?>';
            // $(document).ready(function () {
            //  data = $.parseJSON(jsonString);
            //  console.log(data);
            //});

            // Works:
            var jsonData = <?php echo $row['json']; ?>;
            console.log(jsonData['1']['value']);

            // Suggestion in case $row['json'] is empty
            var jsonData = <?php echo $row['json'] ?: '{}' ?>;
            console.log(jsonData['1']['value']);
        </script>
    </body>
</html>

edited Dec 16 '18 at 21:24

answered Dec 16 '18 at 19:16

Steffen Mächtel

981
8
13

suppose the `value` attribute is like this - "value": "abc"def". then the solution does not work because the middle quotation seems to terminate the string beforehand. – Istiaque Ahmed Dec 16 '18 at 19:35
how to include single quotation, double quotation and backslash in the 2nd parameter in `addcslashes` ? – Istiaque Ahmed Dec 16 '18 at 19:40
I think its not possible to send " as value with json. Its already escaped to \"? – Steffen Mächtel Dec 16 '18 at 19:46
`var jsonString = '';` - I am talking about this line.it works with single quotation but not with double quotation. – Istiaque Ahmed Dec 16 '18 at 19:49
1

I am creating a test2 to figure this 100% out :D – Steffen Mächtel Dec 16 '18 at 19:50
`$jsonFromAjaxRequest` as you said does not have the escaping (i.e. `"value":"abcd\'efg"`). The escaping is done in server side PHP. with `json_encode`. – Istiaque Ahmed Dec 16 '18 at 19:51
How is the json string created on client side? With something like JSON.parse()? – Steffen Mächtel Dec 16 '18 at 19:59
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/185328/discussion-between-istiaque-ahmed-and-steffen-machtel). – Istiaque Ahmed Dec 16 '18 at 20:01
may I ask you to take a look at a completely different question about jQuery draggable and droppable here : https://stackoverflow.com/questions/54498364/jquery-drag-and-drop-simulation-does-not-work-for-the-last-draggable ? – Istiaque Ahmed Feb 03 '19 at 13:16

regex,PHP- remove single , double quotation and backslash from a complex json string

3 Answers3

Older PHPs

The answer to a slightly different question