CODEWITHSUNDEEP
phpmysqlmysqli
kahunaRN
kahunaRN

Reputation: 163

MySQL unwanted automatic replacing \n to linebreaks when storing data

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?

Upvotes: 2

Views: 66

Answers (3)

kahunaRN
kahunaRN

Reputation: 163

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.

Upvotes: 0

hunteke
hunteke

Reputation: 3716

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

  1. your data won't be corrupted or need to be manually handled by you,

  2. 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.

Upvotes: 3

Hetal Chauhan
Hetal Chauhan

Reputation: 807

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

$conteudo = nl2br($conteudo);

Then store into database.

Upvotes: 0

Related Questions