#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    3

    Storing HTML in a database


    I've written a simple blog script for my website. The script has the tiny MCE wysiwyg editor integrated , to make writing content easier. The html tiny MCE generates is stored in a database, like so:
    Code:
    $q="insert into content(content) values('" . MySQL_real_escape_string($_POST['content']) . "')";
    The escape function adds \ before ' and ", which makes the posts look weird when displayed. An example can be seen here:
    http://ashleycox.co.uk/blog/index.php?post=1

    My question: how should I be storing, and retrieving, html from a database? The simpler the better!
    thanks!
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Um, maybe you should not use the Internet for testing your code. Not everybody on this planet is nice and will help you with your bugs rather than exploiting them.



    Originally Posted by ashleycox
    The escape function adds \ before ' and "
    No, it doesn't. The ancient "magic quotes" setting you're using does.

    Your PHP setup and your code desparately need an update. Those "magic quotes" and the mysql_* functions are obsolete since, I don't know, 10 years. In modern PHP, they've already been removed or are about to be removed. People today use PDO or MySQLi for accessing the database.

    Depending on how your other code looks like, this may be a major rewrite. However, it's really the only proper way to solve this problem. You can't run around with obsolete stuff forever. The next PHP update will force you to rewrite your code.

    Comments on this post

    • ashleycox agrees
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    3
    Originally Posted by Jacques1
    Um, maybe you should not use the Internet for testing your code. Not everybody on this planet is nice and will help you with your bugs rather than exploiting them.





    No, it doesn't. The ancient "magic quotes" setting you're using does.

    Your PHP setup and your code desparately need an update. Those "magic quotes" and the mysql_* functions are obsolete since, I don't know, 10 years. In modern PHP, they've already been removed or are about to be removed. People today use PDO or MySQLi for accessing the database.

    Depending on how your other code looks like, this may be a major rewrite. However, it's really the only proper way to solve this problem. You can't run around with obsolete stuff forever. The next PHP update will force you to rewrite your code.
    Thanks for the info. I had a feeling it was going to come down to a rewrite using mysqli. Can you advise as to how you would correctly restore and retrieve the html using mysqli functions?
    Thanks for your help!
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by ashleycox
    Can you advise as to how you would correctly restore and retrieve the html using mysqli functions?
    Thanks for your help!
    Sure. In modern database code, dynamic queries are no longer implemented by concatenating SQL snippets and all kinds of variables. This has proven to be extremely error-prone and insecure, because getting the escaping wrong just once immediately makes the query vulnerable to SQL injections. Instead, we use prepared statements to strictly separate the query itself from the values that should go into it. A prepared statement is a "query template" with parameters for values. You first send this template to the database system. MySQL parses it, comes up with an execution plan etc. And then you send the actual data and have MySQL execute the whole thing.

    Since the query structure is defined ahead, there's no risk of people injecting SQL snippets and manipulating the query. This makes it much more secure (and cleaner) than the traditional string fumbling.

    In your case, the database code would look something like this:

    PHP Code:
    // have MySQLi throw exceptions in case of errors
    $mysqli_driver = new mysqli_driver();
    $mysqli_driver->report_mode MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT;

    // connect to database
    $database = new mysqli('localhost''YOUR_USER''YOUR_PW''YOUR_DB');

    // create prepared statement
    $content_stmt $database->prepare('
        INSERT INTO
            content (content)
        VALUES
            (?)
    '
    );
    // pass parameters
    $content_stmt->bind_param('s'$_POST['content']);
    // execute prepared statement
    $content_stmt->execute(); 
    The connection part should be in a separate script, of course.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    3
    Originally Posted by Jacques1
    Sure. In modern database code, dynamic queries are no longer implemented by concatenating SQL snippets and all kinds of variables. This has proven to be extremely error-prone and insecure, because getting the escaping wrong just once immediately makes the query vulnerable to SQL injections. Instead, we use prepared statements to strictly separate the query itself from the values that should go into it. A prepared statement is a "query template" with parameters for values. You first send this template to the database system. MySQL parses it, comes up with an execution plan etc. And then you send the actual data and have MySQL execute the whole thing.

    Since the query structure is defined ahead, there's no risk of people injecting SQL snippets and manipulating the query. This makes it much more secure (and cleaner) than the traditional string fumbling.

    In your case, the database code would look something like this:

    PHP Code:
    // have MySQLi throw exceptions in case of errors
    $mysqli_driver = new mysqli_driver();
    $mysqli_driver->report_mode MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT;

    // connect to database
    $database = new mysqli('localhost''YOUR_USER''YOUR_PW''YOUR_DB');

    // create prepared statement
    $content_stmt $database->prepare('
        INSERT INTO
            content (content)
        VALUES
            (?)
    '
    );
    // pass parameters
    $content_stmt->bind_param('s'$_POST['content']);
    // execute prepared statement
    $content_stmt->execute(); 
    The connection part should be in a separate script, of course.
    Thanks for that. Just 2 more questions:

    I don't quite understand this bit:
    PHP Code:
    $content_stmt $database->prepare(
        INSERT INTO 
            content (content) 
        VALUES 
            (?) 
    '
    ); 
    // pass parameters 
    $content_stmt->bind_param('s'$_POST['content']); 
    How is '?' substituted by the value of 's'?

    Also, if I wanted to insert multiple values, would I do it like this?

    PHP Code:
    $content_stmt $database->prepare(
        INSERT INTO 
            content (title,content) 
        VALUES 
            (?,?) 
    '
    ); 
    // pass parameters 
    $content_stmt->bind_param('s'$_POST['title']); 
    $content_stmt->bind_param('s'$_POST['content']); 
    or is that completely messed up...

    Thanks!
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    The parameters are written as question marks (as you already found out). And then you simply write down the value types as characters (the "s" stands for type "string") and list the actual values:

    PHP Code:
    $stmt->bind_param('ssid'$some_string$another_string$an_integer$a_double); 
    The question marks aren't literally substituted. Think of a prepared statement as a function: You define a function with a bunch of parameters. And then you can call the function and pass values to it.

    It actually works very much like that. You can even execute the same prepared statement multiple times with different values.


    By the way, please don't fullquote every reply. It's right above your post, so no need to repeat it.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    3
    Ah, I get it! Thanks for explaining. Will try to rewrite the script tomorrow!

    Thanks for your help, much appreciated.

    ps... Sorry about the full quotes, for some reason when you hit 'quick reply' it automatically quotes the message and I'm always forgetting to delete the quote haha.
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by ashleycox
    Will try to rewrite the script tomorrow!
    Cool.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    3
    OK, I still can't get it to work. Here's my code:
    PHP Code:
    function addCategory($name,$description) {
    global 
    $database;
    $cat_stmt=$database->prepare('insert into categories(name,description) values(?,?)');
    $cat_stmt->bind_param($name,$description);

    if(
    $cat_stmt->execute()) {
    return 
    true;
    } else {
    return 
    false;
    }
    $cat_stmt->close();

    Here's the error:

    Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables in /var/www/blog/admin.php on line 40 Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'No data supplied for parameters in prepared statement' in /var/www/blog/admin.php:42 Stack trace: #0 /var/www/blog/admin.php(42): mysqli_stmt->execute() #1 /var/www/blog/admin.php(302): admin->addCategory('TestCategory', 'testCategoryDescription') #2 {main} thrown in /var/www/blog/admin.php on line 42

    Any idea where it's going wrong?
    Thanks again!
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    There are actually several issues.

    First of all, you completely forgot the type string. You need 'ss'. Secondly, checking return values to determine errors is obsolete. Modern, object-oriented code throws an exception in case there's an error. The exception can either be handled or left alone, in which case it will abort the script and send an error message to the error log or the screen -- which is what you just saw.

    In practice, this means you don't have to "ask" MySQLi if there was an error. It takes care of that itself. You generally don't have to deal with errors at all unless you actually wanna handle them (like trying an alternative route or something like that).

    So the code is simply

    PHP Code:
    <?php

    $cat_stmt 
    $database->prepare('
        INSERT INTO
            categories (name, description)
        VALUES
            (?, ?)
    '
    );
    $cat_stmt->bind_param('ss'$name$description);
    $cat_stmt->execute();
    $cat_stmt->close();
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    3
    Thanks again for your response - sorry for the late reply, for some reason I didn't receive an e-mail notification.

    You're right - I did forget the string, thanks for pointing that out!

    Question re return values - as you can see from the code, the code is part of a function. My code is supposed to execute the function and echo a success message if the function executed successfully, or echo a failure message if it did not. Am I still ok to use return values for this, or is there a better way I should be doing it?
  22. #12
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Why do you wanna abuse the return value for signalling errors? The return value is for passing data around, not for handling failed code. Working with scripts which do that is just awful. Every single function call must be wrapped in an if statement to see if it went wrong. Otherwise the script will keep going and do who-knows-what. Delegating errors is a massive pain, because you have to manually pass the return value around.

    Using return values for error is simply wrong. Yes, many parts of PHP do that. But this is only because PHP started out as a primitive procedural language and didn't have a proper error system at that time. Now we do have it: the already mentioned exceptions.

    The benefits of exceptions are clear:

    • Proper separation of regular code execution and program failures. A failed query is an exceptional situation, it's not a regular yes-or-no question like "Is this number positive?".
    • No need to constantly check for errors. PHP will tell you if there's an error, and then you can react to it.
    • Systematical error handling. Errors can be categorized and delegated.

    In your case, it doesn't even make sense to give feedback in the function itself. The user doesn't care if a particular query went wrong. What he/she wants to know is whether the whole operation of adding a category was successful. So what you would do is wrap the whole script in a try-catch block in order to catch any error coming from any part of the program. If there was an error, then you display an error message like "Adding a category failed". Otherwise you display the success message.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    3
    OK, I've completely rewritten the blog script. That was fun!

    I'm still getting backslashes when trying to insert data into the database that contains the ' character...

    is there anything I should've added before inserting the html into the database, or this fault of the tiny mce wysiwyg editor?
  26. #14
  27. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Like I said, I suppose this is caused by your server still having the ancient "magic quotes" feature turned on. Turn it off.

    If they are already turned off, then you'll have to find out when exactly the backslashes are added. Before they're sent to your server? Right after they've arrived? Later?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    3

    Thumbs up


    Thanks, I'd completely overlooked that. For some reason my server has a php.ini file in the php config directory and in the /var/www directory... I'd initially edited the wrong file. Works great now!

IMN logo majestic logo threadwatch logo seochat tools logo