[FIXED] How to sanitize correctly this input?

Issue

I'm fairly new on PHP.

I'm trying to sanitize my input before UPDATE it on myqsql.

If my string is something with apostrophe, like " I'm new on Php" it not works with this code

$description = $_POST['description'];

$description = htmlspecialchars($description);

$description = mysqli_real_escape_string($description);

$description = trim(preg_replace('/\s+/', ' ', $description));

It didn't work: my field in table result empty

If i use

$description = $_POST['description'];

$description = htmlspecialchars($description);

$description =  str_replace("'","\'", $description);

$description = trim(preg_replace('/\s+/', ' ', $description));

It works.

Why $description = mysqli_real_escape_string($description) won't work ?

Solution

Dont use functions like that, instead use prepared statements. Below a very minimalistic example, but it should give you something to build of off:

// Disclaimer: This is untested, but should give you a general direction:
function preparedQuery(string $sql, $paramtypes, $values){
    // Note: You need to some way to get the connection ($mysqli) in this 
    //       function. I suggest a Singleton DB class.
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param($paramtypes, ...$values);
    $stmt->execute();

    return $stmt;
}

And than you can use it:

$result = preparedQuery(
    "INSERT INTO tabel (name, email, age, date) VALUES (?, ?, ?, NOW())",
    'ssi', // s -> string, i -> integer 
    [$name, $email, $age]
);

You can fetch it using $result. You can use it in a while loop. I suggest you read up on prepared statements. It's a but tricky, but once you get the hang of it, very powerful en secure

Answered By - Martijn