Saving and Displaying HTML and special characters in a mysql database safely?

Question

The title basically sums it up. I built a small blog but I cant even post links in my articles! What can I do? I've tried htmlentities(), htmlspecialchars(), real_escape_string() and basically every form of escape there is. I am using PHP 5.3 with MySQL 5.1

Here is my code to save the blog to the db:

function check_input($data, $problem='')
{
$data = trim($data);
$data = stripslashes($data);
$data = htmlentities($data);
if ($problem && strlen($data) == 0)
{
    die($problem);
}
    return $data;
}

if(isset($_POST['addBlog'])) { //form submitted?

// get form values, escape them and apply the check_input function
$title = $link->real_escape_string($_POST['title']);
$category = $link->real_escape_string(check_input($_POST['category'], "You must choose a category."));
$content = $link->real_escape_string(check_input($_POST['blogContent'], "You can't publish a blog with no blog... dumbass."));
$date = $link->real_escape_string(check_input($_POST['pub_date'], "What day is it foo?"));

 // our sql query
$sql = $link->prepare("INSERT INTO pub_blogs (title, date, category, content) VALUES (?, ?, ?, ?)");
$sql->bind_param('ssss', $title, $date, $category, $content);


//save the blog     
#mysqli_query($link, $sql) or die("Error in Query: " . mysqli_error($link));
$sql->execute();

if (!$sql) 
{
    print "<p> Your Blog Was NOT Saved. </p>";
}
}

and here is my code to display the blog:

// Grab the data from our people table
        $result = mysqli_query($link, "SELECT * FROM pub_blogs ORDER BY date DESC") or die ("Could not access DB: " . mysqli_error($link));

        while ($row = mysqli_fetch_assoc($result))
        {   
            $id = $link->real_escape_string($row['id']);
            $title = $link->real_escape_string($row['title']);
            $date = $link->real_escape_string($row['date']);
            $category = $link->real_escape_string($row['category']);
            $content = $link->real_escape_string($row['content']);

            $id = stripslashes($id);
            $title = stripslashes($title);
            $date = stripslashes($date);
            $category = stripslashes($category);
            $content = stripslashes($content);

            echo "<div class='blog_entry_container'>";
            echo "<span class='entry_date'><a href='#'>" .$date. "</a> - </span><span class='blog_title'><a class='blogTitleLink' href='blog-view.php?id=" .$id. "'>" .$title. "</a></span>"; 
            echo "<p>" .$content. "</p>";
            echo "</div>";
        }

You're using mysqli prepared statements, **AND** real_escape_string, meaning you're basically double-escaping every field you use in that query. The whole point of placeholders in prepared statements it that you do **NOT** need to do any escaping. the DB does it for you — Marc B, Sep 27 '12 at 03:52
I know this, I guess I had assumed the more security the better :/ — Ty Bailey, Sep 27 '12 at 03:55
you don't gain anything except extra work to undo that extra layer of escaping after you retrieve the data. even a horribly maliciously injection-lada piece of crap data is perfectly safe if you can get it into the DB safely. e.g. one layer of escaping to get it in, and then it's harmless. — Marc B, Sep 27 '12 at 03:56

score 4 · Accepted Answer · edited May 23 '17 at 11:56

4

While encoding characters is a good thing, one must make sure not to over-encode.

Only encode what /needs/ encoded at that time. Don't encode the HTML before putting it into your database. You may want to print things out later, or you may want to run searches against it. Use the proper escape sequences for SQL (or, better yet, use PDO).

Only when you are sending things to the browser should you escape the HTML, and then you need to decide what kind of escaping you need. To convert things like < and & as the character entities so they will display properly, then use the right escape method for that.

edited May 23 '17 at 11:56

Community

1
1

answered Sep 27 '12 at 03:50

Jeremy J Starcher

23,369
6
54
74

I removed my `check_input()` function from the input query and it seems as if everything is print out fine now, except the HTML isn't working exactly... any idea how I can get that working properly? – Ty Bailey Sep 27 '12 at 03:54
**_isn't working exactly_** -- We need need more info than that. Update your question with what is happening, what you are expecting and and error messages. – Jeremy J Starcher Sep 27 '12 at 03:58

Saving and Displaying HTML and special characters in a mysql database safely?

1 Answers1

Linked