如何在MySQL中插入特殊字符？

Question

I have one table in which is storing data with json_encode. I have tried to mysql_escape_string as well as mysql_real_escape_string but both are not working in my case.

For example :

my password is : @:;_-#()\/+.,?!'"

update new_devices set parameter = '{"password":"@:;_-#()\/+.,?!'""}' where id = 126

With mysql_real_escape_string :

update new_devices set parameter = '{"password":"@:;_-#()\/+.,?!\\\\\\'\\\\\\\""}' where id = 126;

PHP Code :

function update_password($param_array){
     $param_array['new_pass']=mysql_escape_string($param_array['new_pass']);
     $dirparam['password'] = $param_array['new_pass'];
     $sip_query_result = $this->update_query("Device Update Query", "devices", $param_array['id'],array("dir_params" => json_encode($dirparam)));
}

function update_query($method_name,$table_name,$where,$update_array){
 if (is_array($update_array)) {
    $data_str = " set ";
    foreach ($update_array as $key => $value) {
        $data_str.=$key . " = '" . $value . "',";
    }
    $data_str = rtrim($data_str, ",");
  }else{        
    $data_str=" set ".$update_array;
  }
  $update_query=null;
  if (!empty($data_str))
  $update_query.="update " . $table . $data_str;
  $where_str=null;
  if (!empty($where)) {
      $where_str = " where id =".$where;
  }
  $update_query = $update_query . $where_str;
  mysql_query($update_query);
}

Is that possible in PHP using another solution?

I know that store json_encode data into database its not good idea but application is large and I can't do that change.

Answer 1

Alright, as you can't simply switch the database API in a bigger project, I took a closer look at your problem. Still, you should switch to mysqli or PDO asap.

The mistake you made was to use mysql_real_escape_string() in the wrong position. You should use it directly before you send your data to the databse, so it should actually be used inside your update_query() function.

Let's check the difference between correct and incorrect usage.

How to handle the data

Defining your password.

$password = <<<'PASSWORD'
@:;_-#()\/+.,?!\'"
PASSWORD;

var_dump($password);
// string(18) "@:;_-#()\/+.,?!\'""

Next step: Encoding it to json! Instead, you escaped your string in this place.

$passwordJSON = json_encode($password);
var_dump($passwordJSON);
// string(24) ""@:;_-#()\\\/+.,?!\\'\"""

// compared to:

$passwordEscaped = mysql_real_escape_string($password);
var_dump($passwordEscaped);
// string(22) "@:;_-#()\\/+.,?!\\\'\""

Then comes the time to escape it for the database. But here you used json_encode(), too late.

$passwordJSONEscaped = mysql_real_escape_string($passwordJSON);
var_dump($passwordJSONEscaped);
//string(34) "\"@:;_-#()\\\\\\/+.,?!\\\\\'\\\"\""

// compared to

$passwordEscapedJSON = json_encode($passwordEscaped);
var_dump($passwordEscapedJSON);
// string(32) ""@:;_-#()\\\\\/+.,?!\\\\\\'\\\"""

The result

$resultCorrectWay = mysql_query("INSERT INTO passwordtest (password) VALUES ('$passwordJSONEscaped')");
var_dump($resultCorrectWay);
// bool(true)

// vs

$resultWrongWay = mysql_query("INSERT INTO passwordtest (password) VALUES ('$passwordEscapedJSON')");
var_dump($resultWrongWay);
// bool(false)

Conclusion

By using json_encode() AFTER you already escaped your string, you added new entities which would have to be escaped for your query to work.

Do it in the correct order, then the database can handle your statement.

The whole thing for trying it at home

<?php
ini_set('display_errors', 1);
error_reporting(-1);

mysql_connect('localhost', 'user', 'password');
mysql_select_db('test');

echo '<pre>';
$password = <<<'PASSWORD'
@:;_-#()\/+.,?!\'"
PASSWORD;

var_dump($password);
// string(18) "@:;_-#()\/+.,?!\'""

$passwordJSON = json_encode($password);
var_dump($passwordJSON);
// string(24) ""@:;_-#()\\\/+.,?!\\'\"""

$passwordJSONEscaped = mysql_real_escape_string($passwordJSON);
var_dump($passwordJSONEscaped);
//string(34) "\"@:;_-#()\\\\\\/+.,?!\\\\\'\\\"\""

$resultCorrectWay = mysql_query("INSERT INTO passwordtest (password) VALUES ('$passwordJSONEscaped')");
var_dump($resultCorrectWay);
// bool(true)

$passwordEscaped = mysql_real_escape_string($password);
var_dump($passwordEscaped);
// string(22) "@:;_-#()\\/+.,?!\\\'\""

$passwordEscapedJSON = json_encode($passwordEscaped);
var_dump($passwordEscapedJSON);
// string(32) ""@:;_-#()\\\\\/+.,?!\\\\\\'\\\"""

$resultWrongWay = mysql_query("INSERT INTO passwordtest (password) VALUES ('$passwordEscapedJSON')");
var_dump($resultWrongWay);
// bool(false)

edit: when not json encoding

var_dump($password);
// string(18) "@:;_-#()\/+.,?!\'""
mysql_query("INSERT INTO passwordtest (password) VALUES ('" . mysql_real_escape_string($password) . "')");

Value in the database:

@:;_-#()\/+.,?!\'"