MySQL unwanted automatic replacing \n to linebreaks when storing data

Question

I am using PHP to pass a query like this:

"UPDATE `prove750_mrdias`.`stringsMrDias` 
SET `conteudo` = '$conteudo' 
WHERE `stringsMrDias`.`ID` = $id;"

when I echo $conteudo I get Sobre\nmim as expected.

But after the query, I look at the database and the value stored is the formatted string:

'sobre

mim'

That's causing all sorts of problems when parsing the data back to my application.

If I go to phpMyAdmin and pass the value of $conteudo manually it maintains the expected behavior, only when querying the replace is happening without calling it.

Any ideas?

Escape the `\n` character, but I don't see how it would cause "problems". Its just 1 application decides to format the text and the other one doesn't. — Xorifelse, Apr 02 '18 at 12:22
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Apr 02 '18 at 12:23

hunteke · Accepted Answer · 2018-04-02T12:34:18.347

I suspect it's an issue of interpolation. You can kill two birds with one stone by using prepared statements. By using prepared statements

your data won't be corrupted or need to be manually handled by you,
your application will not be subject to security issues a la SQL injection.

This might look like:

$sql = "UPDATE `prove750_mrdias`.`stringsMrDias` SET `conteudo` = ? WHERE `stringsMrDias`.`ID` = ?";
$preparedStatement = $pdo_handle->prepare( $sql );
$preparedStatement->execute([$conteudo, $id]);

That is, you first tell the database the form of the query you want executed, and then -- in a separate call -- you send the arguments to that query.

Thanks, will have a look at the prepared statements documentation. — kahunaRN, Apr 02 '18 at 12:33

score 0 · Answer 2 · answered Apr 02 '18 at 12:33

0

Try http://php.net/manual/en/function.nl2br.php Example,

$conteudo = nl2br($conteudo);

Then store into database.

answered Apr 02 '18 at 12:33

Hetal Chauhan

787
10
22

score 0 · Answer 3 · answered Apr 02 '18 at 13:46

Prepared statements was the right direction.

After looking at the mysqli documentation I ended up with a code like this:

`$sql = "UPDATE `prove750_mrdias`.`stringsMrDias` SET `conteudo` = (?) WHERE `stringsMrDias`.`ID` = (?)";

if (!($stmt = $con->prepare($sql))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$stmt->bind_param('ss', $conteudo,$id)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}


if (!$stmt->execute()) {
     echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}`

Had to use the bind_param() as execute() don't accept any parameters.

MySQL unwanted automatic replacing \n to linebreaks when storing data

3 Answers3