Cannot replace carriage return and new line in php

Question

I get a string from the frontend which has a line break in it. It is saved in an array which looks like this:

[address] => Array (
    [0] => Foo
Bar
)

I then use json_encode() on the array before writing it into the SQL DB:

$string = json_encode( $string, JSON_UNESCAPED_UNICODE );

This turns the array into:

{"address":["Foo\r\nBar"]}

Unfortunately the DB doesn't like \r or \n if not escaped, so it gets rid of the \r and \n.

1. So the first question is, is there a function that I can use to properly escape the string, so it can be written properly into the DB without losing the line break?

---

2. I didn't find any function for that, so I tried to use str_replace to just replace the \r\n with \\n. The function is:

$string = str_replace(["\r\n","\r","\n"], "\\n", $string);

This however does not work. I don't know why. The function itself works, as I tried to replace only "n" with "bla" and it worked. However the moment I try to replace the backslash it does not find anything to replace. I don't know if some "special" backslash character is used here or what else could be going on here.

This is driving me nuts, seriously. So I hope somebody can help me out. Thanks in advance.

Answer 1

Problem : Your str_replace is not working because you are using double quotes.

Solution : You should replace your double quotes with single quotes and then the magic will happen :D

$string = str_replace(['\r\n','\r','\n'], '\\n', $string);

EXTRA USEFUL INFORMATION : For more you should take a look at for details as it's useful to get to know the difference between double quotes and single quotes as:

What is the difference between single-quoted and double-quoted strings in PHP?

Answer 2

It depends on how you insert the string into the DB. However you do it, you need to escape it properly.

If you're using PDO, you can achieve this like this:

$conn = new PDO(.....);
$sql_str = $conn->quote($string);
$conn->exec("INSERT INTO table (str_value) {$sql_str}");

Or, better use a prepared statement:

$conn = new PDO(.....);
$stm = $conn->prepare("INSERT INTO table (str_value) :value");
$stm->execute(array(':value' => $string));

Hope that works.

Answer 3

Storing JSON directly in a database? Yeuch!

However if you really must do it, then why do you feel the need to change the representation of the data? When you run it back through a JSON decoder you wil get the original data back. The problem is only how to get it into a safe format for insertion into your database.

That you have created this from a PHP array implies you've got NO EXCUSE for not checking the content of the data before you save it (not that writing data supplied directly from Javascript is in any way valid or forgiveable).

is there a function that I can use to properly escape the string

Yes, there are several - but you've not told us which API you are using. This is not some magical trick to solve the problem you currently find yourself in - escaping any data you write to your database properly is essential to prevent SQL injection.

In addition to the PDO methods mentioned by Alex, you can do it in the (deprecated) mysql extension using mysql_escape_string/mysql_real_escape_string or in mysqli procedural code with mysql_escape_string / mysqli_real_escape_string or msqli_prepare + mysqli_bind_param. The mysqli functions also have object oriented representations.