Can't insert text with apostrophe

Question

I can't insert the text from textarea when the text has apostrophe please sir's how to fix it.

this my whole code. I try mysqli_real_escape_string but it gives a error.

<?php

    session_start();

    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "srdatabase";

    $conn = new mysqli($servername, $username, $password, $dbname);

    $speakerid = $_SESSION['speakerid'];

    $speaker_info = "SELECT * FROM speakers WHERE id=$speakerid";
    $si_result = mysqli_query($conn, $speaker_info);

    $array = mysqli_fetch_array($si_result);
    $dbfullname = $array['speaker_fullname'];
    $dbimage = $array['speaker_image'];
    $dbspecialization = $array['speaker_specialization'];
    $dbdescription = $array['speaker_description'];
    $dbpaymentcost = $array['speaker_paymentcost'];

?>

<!DOCTYPE html>
<html>
<head>
<title>Update Speaker</title>
</head>
<body>

    <form action="updateSpeaker.php" method="post" enctype="multipart/form-data">
        <textarea name="description" class="inputbox" cols="60" rows="5" autofocus required="required" maxlength="2000" style="resize:none;" placeholder="Description"><?php echo htmlspecialchars($dbdescription);?></textarea>
        <br>
        <input name="update" id="buttonsubmit" type="submit" value="Update">
    </form>

<?php

    if(isset($_POST['update'])) 
    {   
        $newdescription = $_POST["description"];
        $finaldescription = $mysqli_real_escape_string($conn, $newdescription);
        $update_data = "UPDATE speakers SET speaker_fullname = '".$_POST["fullname"]."', speaker_description = '$finaldescription', speaker_specialization = '".$_POST["specialization"]."', speaker_paymentcost = '".$_POST["paymentcost"]."' WHERE id=$speakerid";
        mysqli_query($conn, $update_data);

    }
?>



</body>
</html>

Prepared statement:

$update_data = "UPDATE speakers SET speaker_fullname=?, speaker_description=?, speaker_specialization=?, speaker_paymentcost=? WHERE id=?";
$stmt = mysqli_prepare($conn, $update_data);
mysqli_stmt_bind_param($stmt, 'ssssd', $_POST["fullname"], $finaldescription, $_POST["specialization"], $_POST["paymentcost"], $speakerid);

sorry i tried to quote it, but nothing happen. or i quote the wrong one — Red, Sep 03 '17 at 09:25
Did the answers to duplicate question help you? There are examples of code there. — user4035, Sep 03 '17 at 09:57
ok i got it. where is the comment from the other person. He said the problem is the $ sign in "$"mysqli_real_escape_string. Which is for Object oriented style. — Red, Sep 03 '17 at 10:32
To aviod SQL injection you should quote all the variables. I created a sample for you using the prepared statement. I added it into your question. Please, try and let me know. — user4035, Sep 03 '17 at 10:35
ok thank you! @user4035 i will try it. but not now cause i had to go somewhere. — Red, Sep 03 '17 at 10:54

Lawrence Cherone · Answer 1 · 2017-09-03T11:31:53.713

Your current code is also mixing OOP and procedural based functions, so it will not work even once you have fixed the original issue with quoting user input.

I have converted your code into PDO (untested), which should point you in the right direction. Hope it helps.

<?php
session_start();

// config holder
$config = [
    'db' => [
        'host' => 'localhost',
        'user' => 'root (DONT USE ROOT)',
        'pass' => '',
        'name' => 'srdatabase',
    ]    
];

// connect to database
try {
    $db = new PDO(
        "mysql:host=" . $config['db']['host'] .";dbname=". $config['db']['name'],
        $config['db']['user'],
        $config['db']['pass'],
        array(
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        )
    );
} catch (PDOException $e) {
    exit('Could not connect to database.');
}

// check id, though should be getting this from a $_GET 
if (empty($_SESSION['speakerid']) || !is_numeric($_SESSION['speakerid'])) {
    exit('Invalid speaker id');
}

// handle post
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $errors = [];

    // check or set inbound variables
    $id = isset($_POST['id']) ? (int) $_POST['id'] : 0;
    $description = isset($_POST['description']) ? $_POST['description'] : null;

    // you could set errors here if there empty, but lets continue
    /*
    if (empty($description)) {
        $errors['description'] = 'Description is a required field.';
    }
    */

    if (
        empty($errors) && // check for no errors
        !empty($id) &&    // not required if you checked above, check id is not empty
        !empty($description) // not required if you checked above, check description is not empty
    ) {

        // prepare query for update, only want to update description
        try {
            $stmt = $db->prepare('
                UPDATE speakers 
                SET speaker_description = :description
                WHERE id = :id
            ');
            // bind inbound variables to the query, then execute
            $stmt->bindParam(':id', $id, PDO::PARAM_INT);
            $stmt->bindParam(':description', $description, PDO::PARAM_STR);
            $stmt->execute();
        } catch (PDOException $e) {
            $errors['query'] = 'Error updating database: '.$e->getMessage();
        }
    }
}

// select current row based upon the id
$stmt = $db->prepare('SELECT * FROM speakers WHERE id = :id LIMIT 1');
$stmt->bindParam(':id', $_SESSION['speakerid'], PDO::PARAM_INT);
$stmt->execute();

$result = $stmt->fetch();
/* would contain
    $result['speaker_fullname'];
    $result['speaker_image'];
    $result['speaker_specialization'];
    $result['speaker_description'];
    $result['speaker_paymentcost'];
*/
?>

<!DOCTYPE html>
<html>
<head>
    <title>Update Speaker</title>
</head>
<body>
    <?php if (!empty($errors['query'])): ?>
    <?= $errors['query'] ?>
    <?php endif ?>

    <form action="" method="post" enctype="multipart/form-data">
        <input type="hidden" name="id" value="<?= $_SESSION['speakerid'] ?>">

        <textarea name="description" class="inputbox" cols="60" rows="5" autofocus required="required" maxlength="2000" style="resize:none;" placeholder="Description"><?= htmlentities($result['speaker_description']) ?></textarea>
        <?php if (!empty($errors['description'])): ?>
        <span style="color:red"><?= $errors['description'] ?></span>
        <?php endif ?>

        <br>
        <input name="update" id="buttonsubmit" type="submit" value="Update">
    </form>
</body>
</html>

Can't insert text with apostrophe

1 Answers1