PDO UPDATE doesn't seem to store quotes?

2019-03-30 09:20发布

问题:

I'm just starting out with PDO methods and now I'm stuck at a little question. If I create a form to insert first name and last name into a database I can insert all types of special characters with the code below:

try {
    $db = new PDO('mysql:dbhost=' . $dbhost . ';dbname=' . $dbname, $dbuser, $dbpass);
    $db -> exec("SET CHARACTER SET utf8");
} catch(PDOException $e) {
    echo $e->getMessage();
}
$query = $db->prepare("INSERT INTO users(fname, lname) VALUES(:fname, :lname)");
$insert_array = array(
    ":fname" => $fname,
    ":lname" => $lname
);
$query->execute($insert_array);
$db = NULL;

I can insert ":;,-!"#¤%&&(%)?{][]}£$€{{{$@@_--" without any problems, even insert an SQL-injection. But when I try to update the database with a similar code it does accepts all types of special characters, except quotes. Why is that? The code I'm using to update is:

try {
    $db = new PDO('mysql:dbhost=' . $dbhost . ';dbname=' . $dbname, $dbuser, $dbpass);
    $db -> exec("SET CHARACTER SET utf8");
} catch(PDOException $e) {
    echo $e->getMessage();
}
$query = $db->prepare("UPDATE users SET fname=:fname, lname=:lname WHERE userid=:userid");
$update_array = array(
    ":fname" => $fname,
    ":lname" => $lname,
    ":userid" => $_GET['userid']
);
$query->execute($update_array);
$db = NULL;

I'm grateful for all the help I can get.

-=SOLUTION=-

I had to use htmlspecialchars() to "decode" the string. Like this:

<form action="" method="post">
    First name<br><input type="text" name="fname" value="'.htmlspecialchars($user['fname']).'">
    Last name: <br><input type="text" name="lname" value="'.htmlspecialchars($user['lname']).'">
    <input type="submit">
</form>

Now all kinds of special characters works perfectly. Thanks for all help everybody, really appreciate it! :D

回答1:

Waleed Khan is correct I believe. Not necessarily xssing yourself in this case (though this is a clear vulnerability you show here and leave the door wide-open for such attacks), but instead just breaking your html as far as I can tell.

By just echoing $user['fname'] and $user['lname'] values raw - which contain double quotes from the previous submission - you inadvertently allow the premature closure the html element attribute value as you draw it, thus breaking your HTML form. Surprised it still submits at all. In your browser, check something like Firebug and examine the form - you should see the input is oddly formed and maybe some extra characters drawn after it.

Always use htmlentities() or other similar escape helper functions on PHP values before echoing them directly in HTML. Always.

Example:

<form action="" method="post">
    First name<br><input type="text" name="fname" value="<?php echo htmlentities( $user['fname'], ENT_COMPAT, 'UTF-8' ); ?>">
    Last name: <br><input type="text" name="lname" value="<?php echo htmlentities( $user['lname'], ENT_COMPAT ); ?>">
    <input type="submit">
</form>

Edit: regarding your claim that "And it's working to update with quotes if I delete the quotes in the value of the form", which I take to mean you set your HTML element attribute values using single-quotes rather than double-quotes, it's really not working at all.

If the user submitted a value with single quotes, it would similarly break your fixed example because a single quote in the value will prematurely close your HTML element attribute value declaration. Using double-quotes to declare an HTML element attribute value is best, but if you choose to use single quotes when you build the HTML element, then in PHP, use htmlentities($string, ENT_QUOTES).

Do study the htmlentities manual page though to make sure you're using it properly.