CODEWITHSUNDEEP
javascriptphpmysql
josh
josh

Reputation: 55

Handling special characters in and out of mysql

I'm building a leaflet web app which stores messages assigned to geolocations.

I add data one line at a time by sending it from javascript to PHP using:

$name = mysqli_real_escape_string($conn, $_POST['NAME']);
$latitude = mysqli_real_escape_string($conn, $_POST['LATITUDE']);
$longitude = mysqli_real_escape_string($conn, $_POST['LONGITUDE']);
$message = mysqli_real_escape_string($conn, $_POST['MESSAGE']);

$sql = "INSERT INTO geoData (NAME,LATITUDE,LONGITUDE,MESSAGE)
VALUES ('$name', '$latitude', '$longitude', '$message')";

I get the data back out using PHP to echo the data back to javascript using:

$conn = mysqli_connect($dbServername,$dbUsername, $dbPassword, $dbName);
if(! $conn ){
    die('Could not connect: ' . mysqli_error());
}
$sql = 'SELECT * FROM geoData';

$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        $rows[] = $row;
    }
 } else {
    echo "0 results";
 } 

mysqli_close($conn);

<script type="text/javascript">
        var data = JSON.parse( '<?php echo json_encode($rows); ?> ' ); 
</script>

This works fine UNLESS the message has special characters such as apostrophes for example 'Dave's dogs's bone'. This creates an error

What is the best practise for such an application which uses PHP and javascript. I think I need some way to encode the special characters which javascript can then decode and display.

The error comes as: Uncaught SyntaxError: missing ) after argument list

<script type="text/javascript">
        var data = JSON.parse( '[{"NAME":"The Kennel","LATITUDE":"50.7599143982","LONGITUDE":"-1.3100980520","MESSAGE","Dave's Dog's Bone"}] ' ); 
</script>

Many thanks

Upvotes: 0

Views: 98

Answers (2)

M. Eriksson
M. Eriksson

Reputation: 13635

The issue is your JSON.parse() which isn't needed at all in this case.

Change:

var data = JSON.parse( '<?php echo json_encode($rows); ?> ' );

to

var data = <?= json_encode($rows); ?>;

JSON.parse() is for parsing stringified json. Echoing the result from json_encode() will give you the correct result straight away.

Side note

I would recommend adding $rows = []; before your if (mysqli_num_rows($result) > 0) or json_encode($rows) will throw an "undefined variable" if the query doesn't return any results (since that variable currently is created inside the loop when you're looping through the results).

Side note 2

When making database queries, it's recommended to use parameterized Prepared Statements instead of using mysqli_real_escape_string() for manually escaping and building your queries. Prepared statements are currently the recommended way to protect yourself against SQL injections and makes sure you don't forget or miss to escape some value.

Upvotes: 3

iazaran
iazaran

Reputation: 216

You produce that error yourself by adding ' in json. If you want check that use this:

JSON.parse( '[{"NAME":"The Kennel","LATITUDE":"50.7599143982","LONGDITUTE":"-1.3100980520","type":"bad","reason":"Dave\'s Dog\'s Bone","improvement":"","reviewed":"0"}] ' );

And if you want correct that in main code use str.replace(/'/g, '"') for your var data, before parse it to json.

Upvotes: 0

Related Questions