CODEWITHSUNDEEP
phpjavascriptmysqlajaxjson
Zak Henry
Zak Henry

Reputation: 2185

How to encode json data for database storage in MySQL?

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)

Upvotes: 0

Views: 3052

Answers (1)

user166390
user166390

Reputation:

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.

Upvotes: 3

Related Questions