jquery not loading data with spaces from mysql database

Question

I am having some problem when retrieving data from my database using jquery.

This is my client.js

function get_sede_data(client) {
  $('#client-detail-co').load('client.php?name='+client);
}

This is my client.php file

<?php

$name = $_GET['name'];
$sql_name = "select * from client where c_name = $name";
$re_sql_name = mysqli($con, $sql_name);

if (mysqli_num_rows($re_sql_name) > 0) {
    while ($row = mysqli_fetch_assoc($re_sql_name)) {
        echo
        "<h1>Client details: {$row['c_name']}</h1>";
    }
} else {
    echo "ERROR" . mysqli_error($con);
}
mysqli_close($conexion);
?>

This normally works with clients with only one name (ex: George) but with a client with two names (ex: Mary Ann) it doesnt work. Any idea why it's not retreiving names with spaces?

[Little Bobby](http://bobby-tables.com/) says **[you are at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/)**. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even **[escaping the string](https://stackoverflow.com/q/5741187)** is not safe! I recommend `PDO`, which I [wrote a function for](http://paragoncds.com/grumpy/pdoquery/#function) to make it extremely **easy**, very **clean**, and way more **secure** than using non-parameterized queries. — GrumpyCrouton, Aug 28 '17 at 16:51
You're saying that `$row['c_name']` will return `MaryAnn` instead of `Mary Ann` when `Mary Ann` is what's stored in your DB? — j08691, Aug 28 '17 at 16:52
No, $row['c_name'] should return Mary Ann, but the js function only returns Mary and the "Ann" is missing. — Volt, Aug 28 '17 at 18:16

GrumpyCrouton · Accepted Answer · 2017-08-28T17:15:42.363

To fix your issue, you need to enclose your variable with singlequotes, as it's a string and not an integer.

$name = $_GET['name'];
$sql_name = "select * from `client` where `c_name`='$name'";
$re_sql_name = mysqli($con, $sql_name);

if(mysqli_num_rows($re_sql_name) > 0) {
    while($row = mysqli_fetch_assoc($re_sql_name)) {
        echo "<h1>Client details: {$row['c_name']}</h1>";
    }
} else {
    echo "ERROR" . mysqli_error($con);
}
//you had this as "conexion" but you used "con" above, so I changed this to "con"
mysqli_close($con);

You should also convert to using prepared statements:

MySQLi Prepared Statements

$name = $_GET['name'];

/* prepare statement */
if ($stmt = $mysqli->prepare("select `c_name` from `client` where `c_name`= ?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $name);

    /* execute query */
   $stmt->execute();

    /* bind variables to prepared statement */
    $stmt->bind_result($c_name);

    /* if num_rows > 0 then fetch values */
    if($stmt->num_rows > 0) {
        while ($stmt->fetch()) {
            echo "<h1>Client details: {$c_name}</h1>";
        }
    } else {
        //error reporting
    }

    /* close statement */
    $stmt->close();
}
/* close connection */
$mysqli->close();

PDO Prepared Statements

This is what I personally recommend, as I find it easier than mysqli.

First, you're connection handler needs to be changed to this:

$host = 'localhost';
$db   = '';
$user = '';
$pass = '';
$charset = 'utf8';

$dsn = "mysql:host={$host};dbname={$db};charset={$charset}";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);

Note: We edit some of the default database connection stuff here, most notibly ATTR_EMULATE_PREPARES. When this is true, prepared statements are emulated which could be only as safe as string concatenation

Then, your query will look like this:

stmt = $pdo->prepare("SELECT * FROM `client` WHERE `c_name`= ?");
$stmt->execute([$name]);
$results = $stmt->fetchAll();

if($results > 0) {
    foreach($results as $result) {
        echo "<h1>Client details: {$result['c_name']}</h1>";
    }
} else {
    //error reporting
}

I added the quotes on my variable, but didnt solve the problem. The .js function on client.js still returns 'Mary' and not the full name. — Volt, Aug 28 '17 at 18:21

jquery not loading data with spaces from mysql database

1 Answers1

You should also convert to using prepared statements:

MySQLi Prepared Statements

PDO Prepared Statements