How to encode json data for database storage in MySQL?

Question

I have a javascript rich page that is passing a large JSON formatted to php to be put in a MySQL database. The data in the JSON includes user submitted strings, and will include strings containing basic html (<a>, <strong> etc.).

The issue I am having is when a string containing a ' quotation mark is escaped, I cannot strip the slashes, leading to compounding escapes like

<a href=\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'example.com\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'></a>

Every time the user saves this is compounded, severely bloating the database field.

My string conversion to insert data into MySQL is :

$correspondenceArray = base64_encode(json_encode($_POST['saveArray']['correspondenceObject']));

And to get data back is:

function stripslashes_deep($value)
{
    $value = is_array($value) ?
                array_map('stripslashes_deep', $value) :
                stripslashes($value);

    return $value;
}

$correspondenceJSON = stripslashes_deep(json_decode(base64_decode($resultArray['correspondence_array']), true));

From what I have done my intent is to strip the slashes on the data coming out of the database so the javascript has the unescaped data

Edit

I realise json_encode($a,JSON_HEX_QUOT) would possibly help, but the server I'm running has PHP 5.2.16 so that feature isn't available)

Do you have magic_quotes_gpc() turned on? Those'll litter slashes all over your data, and the option should be turned OFF. — Marc B
– Marc B, Commented Feb 22, 2011 at 22:31
Ah no that is turned on. Hmm if I turn this off am I likely to have knock on effects elsewhere? — Zak Henry
– Zak Henry, Commented Feb 22, 2011 at 22:36
You probably want to use mysql_real_escape_string() in your queries when you turn this off to avoid nasty injections, but @Marc B is right, you should turn it off (it's deprecated in PHP 5.3). — Capsule
– Capsule, Commented Feb 22, 2011 at 22:41
@Capsule Luckily I have already been using mysql_real_escape_string() so it should be a fairly safe switch — Zak Henry
– Zak Henry, Commented Feb 22, 2011 at 22:43

score 3 · Accepted Answer · 2011-02-22 23:59:44Z

3

Don't use string-generation for SQL.

If placeholders are used there will no problem (with storage) and no magic escaping is required. Just store it as VARCHAR type. Done and done.

Sanitization for output (and during input) should likewise be done using the appropriate libraries -- there are two different operations; however, this is a separate issue than storage.

Edit

See PDO as one prepared-statement (read: placeholder) implementation. Others may exist (I do not use PHP, but feel obligated to correct the perpetuation of design mistakes relating to manually built string-based SQL queries.)

The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

Sounds too-good-to-be-true. Now quit using string-generated statements. Please.

Happy coding.

edited Feb 22, 2011 at 23:59

answered Feb 22, 2011 at 22:46

user166390

Sign up to request clarification or add additional context in comments.

5 Comments

Zak Henry Over a year ago

The issue is the object that I am encoding is a a very deep multi-dimensional array (currently up to 5 layers deep) and completely dynamic.

user166390 Over a year ago

@Zak Henry This does not change my answer. JSON is text. Text can be safely stored in a database without "escaping" if placeholders are used in the SQL statements (aka "prepared statements"). Please see: php.net/manual/en/pdo.prepared-statements.php or similar -- bad designs must die. Using prepared statements can also -- in theory (depending upon a number of factors including back-end, adapter, and usage-patterns) -- improve performance significantly.

user166390 Over a year ago

Unfortunately PDO looks like it uses some stupid implementation with references to variables -- but in any case it goes from a bad approach to an acceptable approach.

Zak Henry Over a year ago

Sorry I misunderstood your answer. I have never heard of prepared statements. I will look into it

Zak Henry Over a year ago

@pst Ok, I have implemented PDO and it solves my problem. Answer accepted, thankyou! Now you have shown me this I want to go through all my previous queries and update them >.> grrr

Collectives™ on Stack Overflow

How to encode json data for database storage in MySQL?

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related