How to insert special character in 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.

use `mysql_real_escape_string` also possible duplicate. http://stackoverflow.com/questions/2584066/how-to-insert-special-characters-into-a-database — Karthi, Oct 27 '16 at 05:52
@LifeTimeProgrammer thanks for reply but its not duplicate of that As i said in question i have already tried and its not working — Ankit Doshi, Oct 27 '16 at 05:53
let me add my question to show my query with mysql_real_escape_string — Ankit Doshi, Oct 27 '16 at 05:54
one: use prepared statements, and your problem won't even emerge. two: **never** store passwords as plain text. **never**. use `password_hash()` to create a hash and `password_verify()` to verify said hash. — Franz Gleichmann, Oct 27 '16 at 05:54
Show your original code. `{"password":"@:;_-#()\/+.,?!'""}` is JSON or a literal string? That is not valid because of the `'` after the `!`. — chris85, Oct 27 '16 at 05:57
Possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — chris85, Oct 27 '16 at 06:00
I would say separate the password out into a separate column and sanitize with parameters / prepared stmt. Otherwise potential tomfoolery with `{` and `@:` thinking out loud — Drew, Oct 27 '16 at 06:03
Why is the escaping adding 6 backslashes? Why does it only escape one of the double quotes? — chris85, Oct 27 '16 at 06:06
@chris85 thanks for reply but don't know why its adding 6 backslashes — Ankit Doshi, Oct 27 '16 at 06:07
Can you show your actual PHP? Without the PHP usage this is a dup of the SQL injection prevention question (not verbatim but no dup is) because the `'` is breaking the query. Using parameterized queries would solve that. — chris85, Oct 27 '16 at 06:08
@chris85 i can't show same as mine. but let me show demo which is give you better idea. — Ankit Doshi, Oct 27 '16 at 06:11
@chris85 I have added php code as well will you please help me out if you have something ? — Ankit Doshi, Oct 27 '16 at 06:36
Why are you json_encoding your parameter before passing it to `update_query()`? — Philipp, Oct 27 '16 at 06:48
@Philipp because we are storing data into mysql table with json_encode which was developed before a long time it was mistake so is there any possible solution for that — Ankit Doshi, Oct 27 '16 at 07:27

Philipp · Answer 1 · 2016-10-27T14:42:57.763

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:

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

Thanks a lot for reply and such help. But still i am getting issue like my password is going to store in database with \ is there any other possible solution to store exact string as we get from user ? For example : Current given password : @:;_-#()\/+.,?!'" Stored database password : @:;_-#()\/+.,?!\\\\\\'\\\\\\\"" — Ankit Doshi, Oct 27 '16 at 08:37
As you can see, some of those '\' are coming for the json encoding... so just do not encode it if it bothers you I guess, or mess around with the data and remove those charakters after encoding. Is there an actual problem with those charakters? Tbh your question is like "I am adding 'ABC' onto the string, why is it not saved as the string before I added 'ABC'?" — Philipp, Oct 27 '16 at 08:41
@Phillipp I have remove json encoding from my fields but still whenever i am going to store password with quotes or double quotes its store with slashes so will you please help me out for this ? — Ankit Doshi, Oct 27 '16 at 14:26
Do you mean, it saves **additional** slashes? I can not reproduce that. — Philipp, Oct 27 '16 at 14:41
Ok? Is there actually a reason why you are not hashing the password? Storing passwords as its actual values is one of the worst practices — Philipp, Oct 28 '16 at 06:26
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/126887/discussion-between-ankit-doshi-and-philipp). — Ankit Doshi, Oct 28 '16 at 06:37

How to insert special character in MySQL?

1 Answers1