Putting JSON string as field data on MySQL

Question

I have this idea where I make a multi/non-multi dimensional array into a JSON string using PHP's json_encode and store the data into my SQL database.

For example, I have a table called 'users'. Table 'users' have 3 fields: id, name, data

Using php, I would like to fetch user John's data: SELECT data FROM users WHERE name='john'

Now the value/text for 'data' field will be like this: {"gender":"male","birthday":"8-Jan-1991","country":"UK","city":"London"}

I will decode the 'data' field using PHP's json_decode and then I will convert the stdClass object into an array using one of my self-made PHP functions. And then I can show John's information wherever I want like this: $user['data']['country'].

This saves me from the hazel to create extra fields on database for country, city, birthday, etc. But is it safe? Is there any disadvantages or problem with using this method to store data on MySQL.

Answer 1

Proper escaping and you are fine but I must add that this is where XML is the better format than json since it will also allow you to do also use the data inside the xml in your queries

<?xml version="1.0" encoding="UTF-8" ?>
<user>
   <gender>male</gender>
   <birthday>8-Jan-1991</birthday>
   <country>UK</country>
   <city>London</city>
</user> 

select

SELECT ExtractValue(data, '//gender') AS gender FROM users WHERE name='john' AND EXTRACTVALUE(data, '//country') != 'UK';

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_extractvalue

Answer 2

But is it safe?

As long as you properly escape the input, using an appropriate library to access the database (or at least use mysql_real_escape_string) then yes, it is safe. Or at least, not more risky then storing anything else, in terms of hacking the database.

Is there any disadvantages or problem with using this method to store data on MySQL

Yes, here are a few:

• It's not possible, or at least much harder, to query against anything in the "data" column. Say you want all users that live in London. You'll have to fetch all the "data" columns in the entire database and do the searching in PHP.

• It's also not possible to sort by anything in the "data" column when querying. It would have to be done in PHP.

• You have to take care of ensuring that the data stored is stored in the correct format. You should do this anyway, but it does remove an extra level of protection against storing "bad" data.

It looks like you have essentially turned MySQL into a NoSQL database. Although my experience is limited of them, the are able to index + sort data in the stored documents/JSON data to some extent. As a relational database, MySQL can't: it can only sort + index the defined columns. You're getting the worst of MySQL, the difficulty of scaling, without using any of its advantages, namely being able to run complex queries.

That being said if you are sure that you'll never need to run such queries, it might make it easier to move to NoSQL later if you store things as JSON.

Edit: If you're concerned about using up space with empty columns, you can always add tables. Say a user-addresses table. This is actually quite a good way to be future-friendly if you might sometime need more than one address per user.