3

Before inserting JSON-encoded data in MySQL, do you first JSON encode the data and then escape it prior to inserting, or do you first escape the data before JSON encoding it and then insert it?

ProgrammerGirl
  • 3,157
  • 7
  • 45
  • 82
  • Don't store JSON-encoded data in an SQL database. That just makes it impossible to use the searching and relational features of SQL. – Quentin Apr 19 '13 at 15:06

3 Answers3

3

First JSON encode it then escape it prior to inserting in the database.

Harpreet
  • 709
  • 5
  • 14
  • 1
    Thanks, but why in that order? – ProgrammerGirl Apr 19 '13 at 12:33
  • 2
    JSON carries it own structure. If we first escape it then encode, the data to be inserted may again be vulnerable. According to rule escape should be applied just before the insertion and not before any other function. – Harpreet Apr 19 '13 at 12:35
0

Don't use mysql_real_escape_string - it's deprecated. http://php.net/manual/en/function.mysql-real-escape-string.php If you use one of the DB libraries such as PDO, then that will do the work for you. See real escape string and PDO

Community
  • 1
  • 1
naomi
  • 1,934
  • 1
  • 14
  • 29
  • Why the -1? I can see the argument that it doesn't answer the question of which order to do the things in. But it is better advice than that would be – naomi Apr 19 '13 at 15:25
-3

The very statement of question is wrong.

  1. Do not store JSON in database.
  2. real escape doesn't make your data safe, as it's implied in your question.
  3. mysql-real-escape-string belongs to mysql - so, the answer is quite obvious.
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • why wouldn't you store JSON in a database? if not even mistaken, DBMS support JSON format nowadays. – I try so hard but I cry harder Mar 15 '21 at 15:14
  • First, this question has been asked **eight years ago** when NO DBMS had any support for JSON at all. Second, although JSON has some limited use, 99% of time it's just because people do not understand relational databases and don't know how to create a proper structure, simply abusing their database as a result – Your Common Sense Mar 15 '21 at 15:34
  • I know this question was asked eight years ago. The reason I asked, was because I was curious as to whether it was/is still considered bad practice or that your view on "storing JSON in database" has been changed since, due to the updates. I follow your SO and phpdelusions, so to see you had -3 votes was something that sparked my interest. – I try so hard but I cry harder Mar 15 '21 at 15:39