saving HTML with PHP & MySQL

Question

I'm having troubles with saving certain characters to MySql database table from PHP. I have a WYSIWYG editor through which I create page content and save them to a DB. When I have ' ' or apostrophe in my code, the html text doesn't get saved to the DB. Following is my code:

sending data over to the server:

function SaveContent()
    {
        var content = CKEDITOR.instances.ContentEditor.getData();
        content = content.replace(/"/g, "'");
        var page = document.getElementById("hfpage").value;

        if (window.XMLHttpRequest)
        {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp=new XMLHttpRequest();
        }
        else
        {
            // code for IE6, IE5
            xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange=function()
        {
            if (xmlhttp.readyState==4 && xmlhttp.status==200)
            {

                location.href=document.referrer;
            }
        }
        //alert(content);
        xmlhttp.open("GET","savecontent.php?content="+content+"&page="+page,true);
        xmlhttp.send();
    }

php code for database saving:

//save content editted/added through the HTML editor
function SaveContent($page, $content, $user)
{
    $con = $this->OpenConnection();
    //first check if the content exists, if so do an update
    $sql  = "SELECT * FROM sitecontent WHERE Page = '" . $page . "'";
    $content = mysql_real_escape_string($content);
    $rowcount = mysqli_num_rows(mysqli_query($con,$sql));
    if( $rowcount > 0 )
    {   
        //run update
        $sql = 'UPDATE `sitecontent` SET `Content`="' . $content . '", `UpdatedBy`="' . $user . '", `LastUpdated`= NOW() WHERE `Page` = "' . $page . '"';
        $result = mysqli_query($con,$sql);
        //echo $sql;
    }
    else 
    {
        //run insert
        $sql = 'INSERT INTO `sitecontent` (`Page`, `Content`, `LastUpdated`, `UpdatedBy`) VALUES ("' . $page . '","' . $content . '", NOW(),"'. $user . '")';
        $result = mysqli_query($con,$sql);
    }
}

Notice that I already use 'mysql_real_escape_string' as I already saw on some posts on forums, but this doesn't help.

Appreciate if someone could help me with the problem.

If you are using `mysqli_query()` you should use prepared statements. — samayo, Jul 14 '13 at 12:13

score 2 · Answer 1 · answered Jul 14 '13 at 12:16

First you are mixing mysql_ and mysqli_, you should stick to only one.

    $content = mysql_real_escape_string($content);
    $rowcount = mysqli_num_rows(mysqli_query($con,$sql));

In the above code, I can see you are using mysqli_query() but, you are not using prepared statements Always, use Prepared Statements, when you are using mysqli_ because, that is what it was made for.

And another thing, the mysql_real_escape_string is a mysql_ function and not mysqli_'s so you need to change that to mysqli_real_escape_string()

score 2 · Answer 2 · edited May 23 '17 at 11:49

Simon _eQ's answer is correct regarding how to save your content correctly. However, I thought it worth flagging up that the problem you are encountering is a warning sign that your database is open to SQL Injection. Basically, the reason your content wasn't being saved was because the ' was seen as a part of the query. It is harder to take advantage of this in inserts but many hackers perform malicious actions on your database in select, and delete queries because of this.

Looking at your code whilst you tried to escape your $content (although using the wrong escape function as Simon pointed out) your $page variable doesn't seem to be escaped. I would normally assume the page is a value passed through a POST or GET, if so this is open to manipulation...

I would recommend that you look into how to avoid sql injection with mysqli as not only will it help you solve your problem everytime it will also mean you can be confident your code is always secure.

Here is a link with more info... How can I prevent SQL injection in PHP?

saving HTML with PHP & MySQL

2 Answers2