Best way to sanitize input SQL

Results 1 to 6 of 6
  1. #1
    Yeah nah, nah yeah Liam is offline
    GammaRank
    Dec 2011 Join Date
    Down underLocation
    3,024Posts

    Best way to sanitize input SQL


    RaGEZONE Recommends

    RaGEZONE Recommends

    Hello, I am coding a comment system for my website and I want to ensure a user-friendly comment section while not limiting them from posting comments that use ' & "... This is what I've got:

    PHP Code:
    $comment $db->real_escape_string($_POST['comment']); 
    Is this appropriate input filter?

    How about

    PHP Code:
    htmlentities(stripslashes($comments['comment'])) 
    How do I test the integrity of the code to ensure it can't be exploited?


  2. #2
    Programmer TimeBomb is offline
    ModeratorRank
    May 2008 Join Date
    United StatesLocation
    1,252Posts

    Re: Best way to sanitize input SQL

    Hey Liam, the best way to prevent SQL injection is not to focus on sanitizing input - it's never full proof. Rather than that, it's actually industry standard and strongly recommended to use parametrized queries, also known as prepared statements.

    When you're just sanitizing your input, the data (potentially from user input) is being treated as part of the query, which is always dangerous for various reasons. When you use prepared statements, the data is treated separately from the query itself - it's essentially treated as data to be inputted into the database, which is much safer and more sensible.

    See PHP: PDO::prepare - Manual or PHP: mysqli::prepare - Manual for the technical implementation in PHP, and check out PHP: Prepared statements and stored procedures - Manual or https://en.wikipedia.org/wiki/Prepared_statement for more general information about prepared statements.

  3. #3
    "(still lacks brains)" NoBrain is offline
    ModeratorRank
    Sep 2011 Join Date
    United KingdomLocation
    2,687Posts

    Re: Best way to sanitize input SQL

    Like TimeBomb said, prepared statements are always the way to go. Considering it's 2017 you SHOULD be using prepared statements regardless. If you're allowing users to embed images, use different font styling and font sizing then you'll run into issues using anything but.

    I'd also recommend using a popular framework such as CakePHP or Laravel rather than writing an entire system from scratch to prevent the most common attacks since they are tried and tested. While this may be a major rewrite for your system, it is highly recommended.

  4. #4
    Registered Noobs is offline
    MemberRank
    Jan 2017 Join Date
    SingaporeLocation
    12Posts

    Re: Best way to sanitize input SQL

    Quote Originally Posted by Liam View Post
    PHP Code:
    htmlentities(stripslashes($comments['comment'])) 
    i always use this one.
    Don't forget to add my Skype: vyra.ghozali

  5. #5
    MMO Supervisor Biesmen is online now
    SupervisorRank
    Apr 2007 Join Date
    2,099Posts

    Re: Best way to sanitize input SQL

    Quote Originally Posted by Liam View Post
    PHP Code:
    htmlentities(stripslashes($comments['comment'])) 
    This is not an 'input filter' but an 'output filter'. This is code which (partly) filters XSS injections, not a SQL Injection (not even close). As @TimeBomb and @NoBrain mentioned: you could use prepared statements. However, you'll still need to filter out XSS injections in the output.

    The reason why you do not want to (XSS) filter your data before inserting it into the database, is because you might want to keep the raw data for other reasons (such as editing the data, or showing the actual data on different places without a XSS filter). As a XSS filter I highly recommend to use HTML Purifier. This is being used in many CMS'. You (unless you're an extremely well educated XSS expert) cannot make it as 'waterproof' as possible. That's why I recommend you to use HTML Purifier: Documentation - HTML Purifier. Especially since you mentioned this is for a comment page on your website.
    Forum Rules | Account Support | Subscribe
    RaGEZONE Facebook

    "The reason why people give up so fast is because they tend to look at how far they still have to go, instead of how far they have gotten."

  6. #6
    hi academic is offline
    True MemberRank
    Jun 2010 Join Date
    AustraliaLocation
    493Posts

    Re: Best way to sanitize input SQL

    Like everyone has said, I would definitely recommend using prepared statements. In addition to all security features and things like that it's also really awesome and easy to modify these statements and execute again without modifying the actual statement itself.

    Example of using prepared statements in PHP:
    PHP Code:
    <?php
    $mysqli 
    = new mysqli("localhost""root""""test");

    $stmt $mysqli->prepare("INSERT INTO users (username, password, email) VALUES (?, ?, ?)");
    $stmt->bind_param("sss"$username$password$email);

    $username "academic";
    $password password_hash("password123"PASSWORD_DEFAULT);
    $email "email@example.com";

    $stmt->execute();

    $username "MentaL";
    $password password_hash("password1234"PASSWORD_DEFAULT);
    $email "mental@example.com";

    $stmt->execute();

    $stmt->close();
    $mysqli->close();
    ?>




Advertisement