phpmysqlselectwhere-clausespecial-characters

MySQL SELECT not working for text containing a newline

I'm trying to search in a table if a text already exists, using PHP's PDO.

The problem is that if the text contains a newline character, MySQL does not find the row in the table.

My query is:

SELECT * FROM tableName WHERE colName = 'test
other chars \\ '' " _ \% àèìòù'

If I remove the new line, it find the row.

Edit:

I found that the SELECT finds the row only if the content of that row was saved replacing newline with \n, carriage return with \r and tab with \t.

For example, with PHP:

$text = str_replace(array("\n", "\r", "\t", "'", "\\"), array("\\n", "\\r", "\\t", "''", "\\\\"), $text;
$query = "INSERT INTO tableName SET colName = '$text'";
...

It's true to do that or I'm missing something other?

Solution

  • I found that the SELECT finds the row only if the content of that row was saved replacing newline with \n, carriage return with \r and tab with \t.

    For example, with PHP:

    $text = str_replace(array("\n", "\r", "\t", "'", "\\"), array("\\n", "\\r", "\\t", "''", "\\\\"), $text;
$query = "INSERT INTO tableName SET colName = '$text'";
...

    By doing that, It should works independently of which OS the MySQL runs on.

    Note: this is only the explanation of why the SELECT could not find the row in the database, consider that every data entered in a query must first be sanitized to prevent errors or SQL Injections!