where to use mysql_real_escape_string on JSON data in GET request

Question

I am using JSON data from GET Request for Selecting & Inserting into MySQL. If anybody had used mysql_real_escape_string with JSON, please let me know

$json_data = mysql_real_escape_string($_GET['json_data']);
json_decode($json_data, true);

then mysql_queries

this way is ok

or else we have to do it on every variable like

$json_data = mysql_real_escape_string($_GET['json_data']);
$string = json_decode($json_data, true);
$variable1 = mysql_real_escape_string($string['variable1']);
$variable2 = mysql_real_escape_string($string['variable2']);
$variablen = mysql_real_escape_string($string['variablen']);

then mysql_queries

Answer 1

where to use mysql_real_escape_string on JSON data in GET request

You put three different domains in a single sentence, each of them having different syntax and different escape rules. Don't mix them!

// $text is just some text received in the query string
// It might be a correct JSON representation of some data structure
// but it may be anything else as well; it is a source for injection
// nonetheless, so it have to be thoroughly checked
$text = $_GET['json_data'];

// Check if $text looks like a valid JSON representation
$data = json_decode($text, TRUE);
// We expect an array encoded as JSON in $_GET['json_data']
if (! is_array($data)) {
    // This is not good; recover from this situation somehow;
    // display an error message or use a default value instead or
    // abort the script or any combination of the above
    exit(1);
}

// Validate the structure of $data and the values it contains
if (! isset($data['variable1'])) {
    // Do something: use a default value, display a message etc.
}
// 'variable1' is set, can work with it
$var1 = $data['variable1'];

// Validate the type and the value of $var1
// F.e. if you expect an integer then check if it's an integer and/or
// convert it to an integer
if (! is_int($var1)) {
    // Do something, for example fix it
    $var1 = (int)$var1;
}
// Validate the value; if it's a quantity, f.e., it must be positive
// (zero may or may not be allowed, it depends on your application logic)
if ($var1 <= 0) {
    // Something is wrong here; do something
    // report an error, fix the value, abort the processing, it depends...
}
// $var1 looks legit now; use it or put it into the database

// This test is a joke but let's be realistic. It's 2015 and the
// old mysql PHP extension is dead. Don't use it!
// Use mysqli or PDO_MySQL instead
if (date('Y') <= 2005) {
    $var1db = mysql_real_escape_string($var1);
    $query  = "INSERT INTO tbl1(col1) VALUES ('$var1db')";
} else {
    // Look ma! No need to "escape string" any more!
    $query = "INSERT INTO tbl1(col1) VALUES (?)"
    $stmt  = mysqli_prepare($conn, $query);
    mysqli_stmt_bind_param($stmt, 'i', $var1);
    mysqli_stmt_execute($stmt);
}

Stop using the mysql PHP extension!

It is old, it has limited functionality, it is not maintained any more and, more important, it was deprecated on PHP 5.5 and removed altogether from PHP 7.

Use either mysqli or PDO_MySQL. While PDO seems more versatile to me, its easier to switch from mysql to mysqli (using the procedural interface of mysqli). There are good articles on the web that explain how to switch.

Don't stick with the past, dare to progress!

Answer 2

You should call mysql_real_escape_string() on every separate value just before you insert them to the database (so after any other validations)

But as @Jay Blanchard already said, you should not use the mysql_* functions anymore and look into PDO instead.

why you should use PDO

Answer 3

mysql_real_escape_string is the very last thing you do before embedding a value into a query. You never mysql_real_escape_string something and then modify it and then put it into the query. You may be undoing the escaping you did with mysql_real_escape_string by doing something else to it. For instance:

$value = "'foo'";
$value = mysql_real_escape_string($value);
$value .= "'";
$query = "INSERT INTO ... VALUES ('$value')";

Well, congratulations, the escaping was entirely pointless, you're still producing a syntax error.

JSON is entirely irrelevant to this whole procedure. There's one and only one way to properly construct queries with mysql_real_escape_string, and that's by escaping the values right when embedding them into the query string; no sooner, no later. Having said that, you really need to go with the times and use PDO or mysqli with prepared statements instead of manual escaping.