Developers often need to escape special characters when they’re coding and PHP is not an exception neither. In PHP we’re familiar with an abundance of escaping functions and some of them deal with encoding HTML entities. In addition, MySQL is also particular when it comes to special characters and it has its own set of rules: they are usually covered with escaping functions such as addslashes. However, when we talk about storing JSON in MySQL the approach in escaping special characters is a bit different.
Normally, JSON data is used in combination with MongoDB or similar no SQL storage engines. Thankfully, in newer versions, MySQL is supported for JSON data type. Despite the fact, the data that is usually stored in MySQL is conceptually different from no SQL types such as JSON, trust me, JSON data support is something developers wished for a long time. So, why all that hype with MySQL support for JSON?
Occasionally, you might come in a situation when you have to deal with software that is heavily dependant on MySQL, and then comes a situation when you need to add a piece of JSON data. In the past, you would need to fire MongoDB, and that was from my point of view – being a developer on one side and a system administrator on the other – a total waste of time. Imagine admin has to maintain one additional server just because (s)he uses a piece of JSON data. What a waste of resources! So, what did we do to get around the problem of wasting our time? Developers usually had to serialize JSON and we saved JSON data as text. But then we encountered another problem – we lost the ability to work with JSON in the context of the database itself. You just can’t have it all, can’t you? But, then came MySQL with the newly added support for JSON type and the situation seemed like the sun shining after the rain. But hey, was it really?
This time we won’t deal with the operations on a JSON type data – the context is simply too broad and this might be a great idea for a tutorial on its own. However, we will bring out the main difference between storing serialized JSON as a TEXT type and storing JSON as a JSON type. The difference in storing serialized JSON as a TEXT type vs JSON type lies in the ability to select a part of the JSON data under specific conditions, and consequently the ability to perform operations directly on JSON data in the database context. Obviously, serialized JSON as a text type demands additional work too – in the PHP context that serialized JSON text must be converted back to the JSON format before you can work with the data. And, yes, we won’t mention the clumsiness of the PHP itself when it comes to dealing with JSON.
So, what is the main problem with JSON type in MySQL? From my point of view, the number one reason is the inability to properly escape JSON before attempting to store JSON in the database. Yes, I know, there is this neat MySQL function JSON_QUOTE which magically deals with all escaping problems with your JSON strings, but in addition to that, it also magically removes all your abilities to work with JSON in the database context. Not so magical, and not so new, huh? From where I stand, JSON_QUOTE function does not help much more than what we did in the past when we serialized JSON and stored it as a text.
Old school rules still apply – we still need to jump over the MySQL hoops and we still need to manually prepare JSON to comply with MySQL standards before we save the data. What does that mean in the context of special characters? This means we need to consider all MySQL special characters and of course we need to consider all JSON special characters, too. If we fail to do that, our save attempt will fail miserably too.
So, what is the first thing we need to consider? Yep, you’ve guessed it. Single quotes. They are allowed in JSON strings but MySQL will disapprove heavily if you don’t escape single quotes. What about escaping them with addslashes? Well, using addslashes would destroy the ability to manipulate JSON in the MySQL context. Therefore, when you need to escape single quotes you should use:
$json_string = addcslashes($json_string, "'");And when you encounter double quotes in the JSON strings, you should escape them with a backslash. However, when working with MySQL, you need to double escape double quotes or you will face an error. So, in the case of double quotes you would need to do something like this:
$json_string = str_replace("\\\"", "\\\\\"", $json_string);In addition, don’t forget that MySQL uses C escape syntax in strings. For this reason, you need to double any other special character that uses a backslash (like newlines \n) with the double backslash in a similar fashion.