Escaping special characters is a usual business in programming and PHP is not an exception either. There are plenty of escaping functions in PHP and we already mentioned some of them that deal with encoding HTML entities. MySQL is also picky when it comes to special characters and it has its own sets and rules which are usually covered with escaping functions such as addslashes. However, when it comes to storing JSON in the MySQL the approach is a bit different.
In newer versions, MySQL added support for JSON data type, which is something developers wished for a long time even though data that is usually stored in MySQL is conceptually different from no SQL types such as JSON. Traditionally JSON data is used in combination with MongoDB or similar no SQL storage engines. So why the all of the hype with MySQL support for JSON?
Well, sometimes you have a software which heavily relies on MySQL but you want to add a piece of JSON data. In the past, you would fire MongoDB which is from my perspective, as a developer on the one side and system administrator on the other, redundant. It’s a total waste of admin time having to maintain an additional server for a piece of JSON data. So the solution was usually to serialize JSON and save it as a text. However, you would lose the ability to work with JSON in the context of the database itself. Now when MySQL added support for JSON type it seemed like a relief, but is it really a relief?
We won’t delve deep into the operations on a JSON type as this might become a tutorial on its own. But the main difference between storing serialized JSON as a TEXT type and storing JSON as a JSON type is the ability to select a part of the JSON data given some condition. Or even the ability to do some operations directly on JSON data in the database context. On the other side serialized JSON is actually text which you need to convert back to the JSON format in the PHP context before you can work with the data. Not to mention cumbersomeness of the PHP itself when it comes to dealing with JSON.
The main problem with JSON type in MySQL is the inability to properly escape JSON before the attempt to store it in the database. There is a MySQL function JSON_QUOTE which magically resolve all escaping problems with your JSON strings but it as well magically remove all your abilities to work with JSON in the database context. It is not much more than what we did in the past, which is to serialize JSON and store it as a text.
We still need to jump over the MySQL hoops and to manually prepare JSON to conform MySQL standards before we attempt to save the data. This means we need to consider all MySQL special characters and on the other hand, JSON special characters. Otherwise, our save attempt will fail miserably.
The first things to consider are single quotes. These are allowed in JSON strings but MySQL will protest if these are not properly escaped. The use of addslashes would destroy the ability to manipulate with JSON in the MySQL context. For single quotes we would better use:
$json_string = addcslashes($json_string, "'");
If there are double quotes in the JSON strings they would be escaped with a backslash. But for the MySQL, you would need to double escape or you would face an error. So now we need to do something like this:
$json_string = str_replace("\\\"", "\\\\\"", $json_string);
Moreover, because MySQL uses C escape syntax in strings you would need to double any other special character that uses a backslash (like newlines \n) with the double backslash in the similar fashion.