Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    925
    Thanks
    76
    Thanked 29 Times in 29 Posts

    Exclamation preparing user input for insertion.

    Hello,

    Struggling with user input here and trying to figure out the best way to prepare data for insertion to a database. For instance what is the best way to escape the data to protect the database?

    Been googling for an hour now, I have come across these functions: quotemeta(), addslashes() and htmlspecialchars() but I'm getting confused on the best one to use.

    I created a simple function which takes the user input and then sent the data to htmlspecialchars() but when I enter the relative characters, they are not being converted. When using addslashes() it only adds slashes before double or single quotes but the video I have watched states it should also cover more characters...

    Just looking for some advice really...I ended up deleting the code I had and decided to start from scratch. I was told I should let the user enter any data they want but just to make sure to escape it before I insert it into the database.

    Any advice guys?

    Kind regards,

    LC.

  • #2
    Regular Coder
    Join Date
    Dec 2010
    Location
    London
    Posts
    339
    Thanks
    63
    Thanked 11 Times in 11 Posts
    You should be using mysql_real_escape_string() to help prevent SQL injections.

    I use a combination of that and addslashes()

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Step 1 is always stripslashes if you are runing magic quotes (gone as of 5.4). Never use addslashes especially in combination with mysql_real_escape_string.
    strip_tags can be used if you want to remove HTML completely.
    Finally use binding with PDO or MySQLi instead of the old mysql library.
    PHP Code:
    if (ini_get('magic_quotes_gpc'))
    {
        
    $_POST['enterdata'] = stripslashes($_POST['enterdata']);
    }

    if (
    $stmt $conn->prepare('INSERT INTO table (field) VALUES (?)'))
    {
         
    $stmt->bind_param('s'$_POST['enterdata']);
         
    $stmt->execute();
         
    $stmt->close();

    Simple as that.
    Don't forget to do any validation and verification you need to before you insert.
    htmlspecialchars or htmlentities should be used in display and not in storage. This will help get around a few charset issues with characters that are available in htmlentities that would therefore become unsearchable (unless you go by the htmlentity of it of course) in the storage if converted prior to storage.

  • Users who have thanked Fou-Lu for this post:

    connormcwood (07-25-2012)

  • #4
    Regular Coder
    Join Date
    Dec 2010
    Location
    London
    Posts
    339
    Thanks
    63
    Thanked 11 Times in 11 Posts
    sorry i did actually mean stripslashes

    What's wrong with using mysql_real_escape_string()?

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Nothing, if you are using mysql library and should be used as the final step before insertion.
    But mysql library is getting really old (I see they've finally added notes indicating that its become obsolescent and its use is discouraged), so you should opt for PDO or MySQLi for the new connectivity type. And since both can make use of prepared statements, its silly to execute a raw statement when prepared are available and dealing with variable data.

    So the mysql is still available, and there is no indication that it's hit a deprecated stage, but has certainly hit the obsolescent phase (awhile ago actually). So you really should avoid using it for any current and new projects as it will eventually disappear or should be used only on support for legacy applications.

  • Users who have thanked Fou-Lu for this post:

    paddyfields (07-24-2012)

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Its definitely worth switching over to PDO. After some use, you'll find its easier to use, more convenient, and as you write larger apps, custom functions/workarounds you had to write to work with the regular MySQL results are already taken into account with PDO. If nothing else, it sanitizing the data for you is great, leaving you only to worry about validation and not special characters.

  • #7
    Regular Coder
    Join Date
    Dec 2010
    Location
    London
    Posts
    339
    Thanks
    63
    Thanked 11 Times in 11 Posts
    Ah man, PDO looks complicated. I see some long nights of coding ahead

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Its not! I promise you!

    Its object oriented, which if you're not used to, can be a bit strange, but it is very simple to use.

  • #9
    Regular Coder
    Join Date
    Dec 2010
    Location
    London
    Posts
    339
    Thanks
    63
    Thanked 11 Times in 11 Posts
    (sorry to LearningCoder for jumping on this thread a bit)

    As a simple update to bring my code a bit more up to date for now can I simply change all instances of 'mysql' to 'mysqli' ?. Obviously with the future prospect of converting my whole site to PDO.

  • #10
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Well, I donno definitively, but I doubt you can do a one for one switch of mysql to mysqli... there are bound to be differences in function names. Plus, the real advantage of switching to mysqli is to use the object side of it, same with PDO. Just changing all your functions to the mysqli equivalents won't help/fix anything.

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    No you can't. Even procedural MySQLi differs from MySQL even though the names are the same with the addition of the i, the argument lists are different. Both mysql and mysqli make use of the same information (connections, resultsets, etc), but mysqli requires that parameter first, while mysql had that parameter as an optional second.

    So no, you definitely can't switch. Plus the best part of prepared statements requires rewrite.
    These reasons are why I still suggest writing aggregate storage classes. Then all you need to do is rewrite the underlying drivers for it instead of the code that uses it.

    Edit:
    Also, mysqli is not global like mysql is. For whatever reason that mysql connection was original determined to be allowed as a global resource is beyond me. At least mysqli fixes that.

  • #12
    Regular Coder
    Join Date
    Dec 2010
    Location
    London
    Posts
    339
    Thanks
    63
    Thanked 11 Times in 11 Posts
    Ok, thanks to you both. Very helpful.

  • #13
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    925
    Thanks
    76
    Thanked 29 Times in 29 Posts
    No problem paddy, it was quite interesting to read the replies to this. To be honest, what you guys are talking about here is too hard for me to grasp at the moment but it's always useful to come back to threads like this for when I do get to the next stage in my learning development.

    I have another issue at the moment with the isset() function. It keeps executing the else statement to the isset() if statement. For example, if I leave the pass and email form fields blank, the code only echo's the username, when in actual fact it shouldn't execute that at all seeing as though the if statement should be executing and not the else because the variables have not been set?? I've probably missed something so basic. Here is my code:
    PHP Code:
    <?php
    session_start
    ();

    function 
    check_string($data){
        
    $chars = array('!','?','~','@','"','£','$','%','^','&','*','(',')','[',']','#','/','<','>','+','=','-','_','.',',',':',';','{','}','|');
        
    $charlen count($chars);
        
        for(
    $i=0;$i<$charlen;$i++){
           if(
    strstr($data$chars[$i])){
              
    $result "TRUE";
              return 
    $result;
           }
        }
        
    $result "FALSE";
        return 
    $result;
    }

    $user $_POST['user'];
    $pass $_POST['pass'];
    $email $_POST['email'];

    if(
    $_SERVER['REQUEST_METHOD'] == "POST"){

        if(!isset(
    $user$pass$email)){

           echo 
    "Please fill in ALL of the form fields.";
           
    header("resfresh:5, url=index.php");
     
        }
        else{
           echo 
    $user."<br>";
           echo 
    $pass."<br>";
           echo 
    $email."<br>";
        }
    }
    else{
        echo 
    "There was an issue with the server <br>";
        echo 
    "You will be redirected shortly."
        
    header("refresh:5, url=index.php");
    }
    ?>
    I also tried comparing the variables to 'NULL' but it didn't help at all. As you can see, I am not making use of the check_string() function at the moment but have opted to dis-allow users to enter those characters as a username because I want to keep the usernames to basic characters, otherwise someone could enter: ^%@*~({ as a username, which I definitely do not want.

    Can anyone help me out here?

    Kind regards,

    LC.
    Last edited by LearningCoder; 07-25-2012 at 11:03 AM. Reason: added words

  • #14
    Regular Coder
    Join Date
    Dec 2010
    Location
    London
    Posts
    339
    Thanks
    63
    Thanked 11 Times in 11 Posts
    try this instead of the isset()

    PHP Code:
    if($user && $pass && $email != NULL){
    echo 
    $user."<br>"
    echo 
    $pass."<br>"
    echo 
    $email."<br>"
    }
    else

    echo 
    "Please fill in ALL of the form fields."

    Last edited by paddyfields; 07-25-2012 at 12:38 PM.

  • #15
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    The isset doesn't work for you simply because you have already created the variables. To get around the notice's you'll receive if the form isn't posted properly simply change it to this:
    PHP Code:
    if(isset($_POST['user'], $_POST['pass'], $_POST['email'])){
        
    $user $_POST['user'];
        
    $pass $_POST['pass'];
        
    $email $_POST['email']; 
    And use the else clause to redirect.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •