Apostrophe cause problems inserting query when using trim, stripslashes and htmlspecialchars

Question

When I use text with an apostrophe, the query don't work.

Example: This is Ben's party.

This is the function I use:

function text_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

This is the html:

<textarea name="text" rows="20" cols="89"></textarea>

The php script:

if ($_SERVER["REQUEST_METHOD"] == "POST") {

if (empty($_POST["text"])) {
    $errors[] = "There is no text";
} else {
    $text = text_input(mysqli_real_escape_string($con, $_POST['text']));
}

if(empty($errors) === true){
    mysqli_query($con, "INSERT INTO texts (text) VALUES ('$text')");
    exit();
}

score 2 · Accepted Answer · answered Jun 16 '14 at 09:39

2

You need to do the mysqli_real_escape_string after all the other stuff. Otherwise stripslashes will remove the slashes you just added with the escape.

So:

$text = mysqli_real_escape_string($con, text_input($_POST['text']));

answered Jun 16 '14 at 09:39

colburton

4,685
2
26
39

Thank you, it worked. So simple.. Thank you again for the explanation and the example. – Jun 17 '14 at 07:25

score 0 · Answer 2 · answered Jun 16 '14 at 12:44

Your use of stripslashes() and htmlspecialchars() indicates a great deal of confusion.

trim() is the only function in your code that belongs in text_input().
stripslashes() should almost never be used. Instead, you should escape output using functions that are tailored for a specific task. Using stripslashes() on text input will only cause confusion when a user actually needs to use a backslash.
htmlspecialchars() should be the last function used before producing html output. If you HTML escape everything, you will run into headaches when you need to use your database for other purposes. I have seen plenty of physical mail with HTML character references (&#xx;) in the address, even hand-written ones!
mysqli_real_escape_string() should be the last function used before a MySQL Query.

In other words:

$text = trim ($_POST['text']);
$text_h = htmlspecialchars ($text);
$text_m = mysqli_real_escape_string ($con, $text);
...
mysqli_query ($con, "INSERT INTO texts (text) VALUES ($text_m)");
...
echo "<p>'$text_h' added to database.\n";

The first answer already helped me out, but still thank you for the good explanation and the example! — , Jun 17 '14 at 07:26

Apostrophe cause problems inserting query when using trim, stripslashes and htmlspecialchars

2 Answers2