php sqlsrv_fetch_array return to json

Question

any body can help.. How do I remove \r\n from data extract from TSQL database? Im using PHP to retreive the data.

http://localhost/model1.php?model=ROLL

$model = (isset($_GET['model'])) ? $_GET['model'] : '';

$model = str_replace('\r\n','', $model);

if ($model != '') {
$tsql = "SELECT Brand,Model from product where Model LIKE '%$model%'";

$stmt = sqlsrv_query( $conn, $tsql );
$result = array();
$row_check = sqlsrv_has_rows( $stmt );

if ($row_check == true) {
    while ($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) {
        $result['Response'][] = array(
            'Brand' => $row['Brand'],
            'Model' => $row['Model']
        );
    }
}
..
..
..
header('Content-Type: application/json');

echo json_encode($result, true);
die();

I already implement this..but nothing happen.. $model = str_replace('\r\n','', $model); — twister, Mar 11 '21 at 03:48
Are you certain that your $model variable has the text you think it has? It might be a good idea to temporarily put a print_r($model); line right before your first line in that code to see what that variable contains. :) — summea, Mar 11 '21 at 04:44
same result after changed to this -> $model = str_replace("\r\n","", $model); — twister, Mar 11 '21 at 05:14

summea · Answer 1 · 2021-03-12T17:52:45.777

Like Tangentially Perpendicular mentioned in the comment from earlier, and possibly like the PHP documentation might indicate in its example that contains a "\r\n", when I try the following code example:

<?php

// ref: https://stackoverflow.com/q/66576069/1167750
$_GET['model'] = "2 ROLLING HOOPS\r\nAND 2 SETS OF 3\r\nCORRUGATIONS;";

$model = (isset($_GET['model'])) ? $_GET['model'] : '';

// ref: https://stackoverflow.com/questions/66576069
//   /php-sqlsrv-fetch-array-return-to-json#comment117691397_66576069
$model = str_replace("\r\n", " ", $model);

echo "Result:\n";
echo $model;
echo "\n";

?>

I get this output using PHP 7.3.27:

Result:
2 ROLLING HOOPS AND 2 SETS OF 3 CORRUGATIONS;

If I only use single quotes when doing str_replace('\r\n', ' ', $model); I get the following output:

Result:
2 ROLLING HOOPS
AND 2 SETS OF 3
CORRUGATIONS;

which probably isn't what you are wanting to get as a result. Thus, I would still recommend using the double quote approach for this.

I would recommend trying a simple code example (like in this post) and seeing if you get the same result on your side. This might help narrow down if something else if affecting your results. For example, maybe there is something strange happening with your $_GET['model'] or $model related content during the process.

Also, I would recommend using prepared statements in the near future for safety/security! It might be bad if someone were to send you strange input as the $_GET['model'] variable.

Update:

After trying an example URL similar to this with the test code from earlier in this post: http://localhost/q26.php?model=2%20ROLLING%20HOOPS\r\nAND%202%20SETS%20OF%203\r\nCORRUGATIONS;

I found that I needed to modify the following str_replace() line to look like this:

$model = str_replace("\\r\\n", " ", $model);

in order to compensate for the way that the \r\n appears to be being read.

So the relevant, updated example code looks like this for now:

<?php

$model = (isset($_GET['model'])) ? $_GET['model'] : '';

// ref: https://stackoverflow.com/questions/66576069
//   /php-sqlsrv-fetch-array-return-to-json#comment117691397_66576069
$model = str_replace("\\r\\n", " ", $model);

echo "Result:\n";
echo $model;
echo "\n";

?>

I'm not sure why there might be a browser difference with the \r\n part. To check, I tried the relevant, updated code from earlier with the example query and it seems to work in both Chrome (version 88.0.x) and Firefox (version 86.0.x) for me:

Chrome Result:

Firefox Result:

i detect the issue maybe to browser.. if im using mozilla \r\rn still appear..but if im using chrome \r\n not appear... what the issue exactly? — twister, Mar 11 '21 at 07:39
can you advise how do I implement prepared statements based on my code above? — twister, Mar 11 '21 at 08:33
Hi @twister, I've updated the post above to possibly address your first point, though it might not answer your question. — summea, Mar 12 '21 at 17:55
@twister: As far as your question about implementing prepared statements, I think that might be a different enough topic from your original post that you might want to open a new question about how to implement prepared statements for the `LIKE` part of your statement. But before doing that, I would recommend searching around online to see if that question has already been answered. Doing a quick search, I found [this other post](https://stackoverflow.com/a/7357296/1167750) that might address this. — summea, Mar 12 '21 at 17:57
@twister There might be some differences in how [`sqlsrv_prepare()`](https://www.php.net/manual/en/function.sqlsrv-prepare.php) works compared to the [PDO `prepare()`](https://www.php.net/manual/en/pdo.prepare) statement discussed in [the other thread](https://stackoverflow.com/q/583336/1167750), though. — summea, Mar 12 '21 at 18:01
@twister, [Here is another example](https://www.php.net/manual/en/pdostatement.bindparam.php#99698) mentioned [in the previous thread](https://stackoverflow.com/questions/583336/how-do-i-create-a-pdo-parameterized-query-with-a-like-statement#comment85318424_583348) that might help for background info, though I don't know if it's the best approach for this. — summea, Mar 12 '21 at 18:07

php sqlsrv_fetch_array return to json

1 Answers1