[RESOLVED] PHP MYSQL custom blog - Some syntax not permitted when new post added

[tone72]

I am creating a custom blog in PHP and MYSQL as a way of learning PHP. I have an admin section that requires admin login to use the features. In this section is a feature to add new blog entries. there is also a feature to update existing entries. Both add new entries and update existing entries works well, except if certain syntax (e.g. ' and 😉 is included in the content. If this syntax is included, then add and update will not insert or update the database. I'm not sure where to start to begin correcting this issue, so if anyone has any ideas please share.

[bradgrafelman]

Sounds like you aren't sanitizing all user-supplied input before putting it into a SQL query string. Doing so will leave your code vulnerable to SQL injection attacks and/or just plain SQL errors.

EDIT: Woops - hit submit too early. Continued below...

Instead, you must first sanitize it with a function such as [man]mysql_real_escape_string/man (for string data) or use prepared statements.

If you need more info/help than that, you'll have to post some code where the data in question is processed and (supposed to be) sent to the DB.

[tone72]

Thank you so much for your very quick response! I will have a go with the mysql_real_escape_string() and see if I can figure out exactly how to use this. Thanks again!

[tone72]

The mysql_real_secape_string worked well and now I can insert and update my database even when I include the previously problematic syntax.

Below is my php code where I included the mysql_real_escape_string. To avoid an undefined index notice I also included isset(). My code looks pretty messy so I am wondering if there is a way of tidying it up a bit (e.g., can I combine the isset() for each variable?).

<?php

session_start();

require("../admin/config.php");

$db = mysql_connect($dbhost, $dbuser, $dbpassword);
mysql_select_db($dbdatabase, $db);

if(!isset($_SESSION['USERNAME'])){
	header("Location: http://localhost/hugs4happiness/pages/index.php");
}

if(isset($_POST['subject'])) {
	$_POST['subject'] = mysql_real_escape_string($_POST['subject']);
}

if(isset($_POST['summary'])) {
	$_POST['summary'] = mysql_real_escape_string($_POST['summary']);
}

if(isset($_POST['body'])) {
	$_POST['body'] = mysql_real_escape_string($_POST['body']);
}

if(isset($_POST['author'])) {
	$_POST['author'] = mysql_real_escape_string($_POST['author']);
}

if(isset($_POST['submit'])){
$sql = "INSERT INTO entries(cat_id, dateposted, subject, summary, body, author) VALUES(" . $_POST['cat_id'] . ", NOW(), '" . $_POST['subject'] . "', '" . $_POST['summary'] . "', '" . $_POST['body'] . "', '" . $_POST['author'] . "');";
mysql_query($sql);
header("Location: http://localhost/hugs4happiness/pages/viewentry.php?id=" . mysql_insert_id());
}

else{

require("../pages/header.php");

?>

[bradgrafelman]

There are a couple of concise coding techniques you can utilize to clean that up a bit. Here's the first:

Use [man]sprintf/man to form strings like SQL queries:

$sql = sprintf(
    "INSERT INTO entries(cat_id, dateposted, subject, summary, body, author)
                  VALUES(%d    , NOW()     , %s     , %s     , %s  , %s    )",
        $cat_id,
        "'" . mysql_real_escape_string($subject) . "'",
        "'" . mysql_real_escape_string($summary) . "'",
        "'" . mysql_real_escape_string($body)    . "'",
        "'" . mysql_real_escape_string($author)  . "'"
);

And the second would be to use the ternary operator:

$cat_id  = isset($_POST['cat_id'])  ? (int)$_POST['cat_id']         : NULL);
$subject = isset($_POST['subject']) ? strip_tags($_POST['subject']) : NULL);
$body    = isset($_POST['body'])    ? nl2br($_POST['body'])         : NULL);

This is not only concise but also has two benefits: you can do some general filtering/type-casting/etc. when storing the POST'ed value in the new variable, and if the data wasn't POST'ed you'll still define the variable but with a default value (e.g. NULL) instead.

Also note that a benefit of using more localized variables (e.g. $body instead of $POST['body']) is that you don't lose data integrity. Quick example, say my name is "Brian O'Connor" which you'll then receive as "Brian O\'Connor" since you overwrote the original copy of my data with the mysql_real_escape()'ed version of it. Now say you try to greet me to your site:

echo "Welcome, $_POST[name]!";

The output would then be:

Welcome, Brian O\'Connor!

which just looks broken.

[tone72]

Thank you again. I am very appreciative of your assistance. I've been learning PHP for a couple of weeks now and I'm just starting to get the hang of it. It is so great to have a resource to help me along. 🙂