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.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    137
    Thanks
    1
    Thanked 0 Times in 0 Posts

    question about validation and sql injection

    A) validating username in php

    as part of a registration form a user fills there desired username and this is stored in a mysql. there are certain conditions for the username.

    a) the username should only begin either letters or numbers, and Underscore character
    example = user123, 123user, u_ser123, user_123 = completely case insensitive
    b) a user may choose not to have an underscore or numbers sometimes. example = username

    presently my validation for username is

    $username = $_POST["username"];
    if( $username == "" || !eregi("^[a-zA-Z0-9_]+$", $username) )
    {
    $error.="User name cannot be blank or has special characters";
    }

    Question = how can i rewrite this php validation for username to meet the above criteria or is my validation correct


    B) preventing sql injection

    till now i have been capturing the form values and directly inserting into the table without considering sql injection however for this project as it is for a forum i would like to implement prevention of sql injection. from what i have read about preventing sql injection there are several steps that need to be followed,

    htmlentities
    addslashes
    trim
    mysql-real-escape-string
    magic_quotes_gpc is ON
    magic_quotes_runtime is OFF
    magic_quotes_sybase is OFF

    as i have not done preventing sql injection i am not sure what is the correct process.

    Question =

    a) please advice a step by step process of how to go about avoiding the sql injection before the insert sql query is executed starting from

    $username = $_POST["username"]; till the

    insert into tablename(field1, field2) values($value1, $value2) SQL query is executed which will prevent sql injection even if the user enters any special characters while filling the form.

    b) should i consider the setting of magic quotes as in should it be ON or OFF or should i ignore it if so should it be
    ON or OFF

    c) also with the prevention methods if a user types a special character in the data will that character be written in the table as a escaped character or how does it store those special characters

    d) a very important point here, i have a feature where a user can check if a username is available or not. so while storing a username if the username is stored as john\smith in mysql and if the user is searching for johnsmith this would not match, so even in the table the username should be stored without slashes as i have to read the username and compare with what the user has typed to see if they both are same or different.


    please advice if i have missed any other steps to prevent sql injection.


    thanks a lot for your help.

  • #2
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    1) Your regular expression looks ok, although eregi() (as the name indicates) is a case-insensitive regex function. In other words, your a-zA-Z groups are redundant. Are you sure you want them to allow a name to start with an underscore? You might consider altering that and also using the preg_match(), as the PCRE functions are supposed to be more efficient and ultimately more flexible.

    2) Regarding SQL injection. Remember that you are trying to prevent an attack on the RDBMS / SQL server, not trying to parse out HTML tags or prevent XSS or CSRF attacks. Those should be done on the backend when the data is output. That ensures that no matter how the data is inserted (if your incoming filters miss it, in other words), that the output is properly filtered.

    Your second consideration is to the target; is it MySQL / Postgresql / some other RDBMS? Does the PHP extension you're using offer innate functions meant to properly escape data for that target? Or does the extension offer a method to use a parameterized query?

    Let me give you an example; this is a tried and true method to escape data for the standard MySQL extension:
    PHP Code:
    if ( !empty($_POST['username']) ) {
      
    $cleanData['username']= mysql_real_escape_string($_POST['username']);

    Very simple; we take some incoming data via POST, escape the data using the innate MySQL extension function mysql_real_escape_string() (that uses the actual MySQL library to properly escape data targeted towards MySQL, naturally) and reassign that value to a 'clean' array.

    Other extensions or classes may offer a way to use a parameterized query, which is preferable to 'just' escaping data. The MySQLi extension does, or PDO (which I use) does as well.

    magic_quotes should be avoided like the plague. It was a method provided by the PHP developers to help new PHP coders write quick scripts and not worry about SQL injection or escaping incoming data. Unfortunately it backfired and is actually being removed from the upcoming v6 release. The potential issue caused is that you will wind up with multiple escaping slashes in your stored data. It should be disabled by default. If it isn't, you can use get_magic_quotes_gpc() to test for it's presence and work around it.

    This is a portable method to test for and undo magic_quotes:
    PHP Code:
    if ( get_magic_quotes_gpc() ) {
      
    $_REQUESTarray_map('stripslashes',$_REQUEST);

    Basically takes the entire $_REQUEST superglobal and removes the slashes that magic_quotes added behind the scenes.

    d) a very important point here, i have a feature where a user can check if a username is available or not. so while storing a username if the username is stored as john\smith in mysql and if the user is searching for johnsmith this would not match, so even in the table the username should be stored without slashes as i have to read the username and compare with what the user has typed to see if they both are same or different.
    I don't know why the string 'johnsmith' would require an escaping slash, but yes you're correct. You want to avoid storing slashes in your data, which I mention above. Using one of the innate escaping functions provided, and turning off magic_quotes (or using the workaround) will ensure this doesn't happen.

    Read through the links I provided, they should answer all your questions.
    Last edited by bdl; 05-16-2008 at 01:36 AM. Reason: Typos!

  • #3
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    as the PCRE functions are supposed to be more efficient and ultimately more flexible.
    Not to mention the ereg_* are no longer available in PHP 6.

    And remember that the cleanest (and possibly most effective) method of preventing SQL injection is prepared statements. But it does take some more advanced techniques to make this easy to use.
    Last edited by Inigoesdr; 05-16-2008 at 06:39 PM. Reason: Clarifying; It looked like aedrin was saying the preg_* functions would be removed

  • #4
    Regular Coder
    Join Date
    Sep 2007
    Posts
    137
    Thanks
    1
    Thanked 0 Times in 0 Posts
    with the following validation

    $username = $_POST["username"];
    if( $username == "" || !eregi("^[a-zA-Z0-9_]+$", $username) )
    {
    $error.="User name cannot be blank or has special characters";
    }

    this works fine however
    1) if a user types "underscore" more than once the validation is accepting 2 underscores which i do not want. i want the user to enter only 1 underscore and letters and numbers can be repeated.
    2) also if a user enters "underscore" as the first character it is still accepting. ex: _username. i do not want the underscore to be the first character

    how can i rewite the above validation to mee my requirement.

  • #5
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    There are plenty of regular expression tutorials and tools online to help you build and test your regex. If we start down the "do it for me" road, you'll never learn how to do it properly, and we'll be here all day going back and forth on "well, that doesn't work, how can I change it to do this".

  • #6
    New Coder
    Join Date
    Apr 2007
    Posts
    48
    Thanks
    4
    Thanked 1 Time in 1 Post
    Let me give you an example; this is a tried and true method to escape data for the standard MySQL extension:
    PHP Code:
    if ( !empty($_POST['username']) ) {
    $cleanData['username']= mysql_real_escape_string($_POST['username']);
    }
    Very simple; we take some incoming data via POST, escape the data using the innate MySQL extension function mysql_real_escape_string() (that uses the actual MySQL library to properly escape data targeted towards MySQL, naturally) and reassign that value to a 'clean' array.
    bump.. Is mysql_real_escape_string sufficient to remove injection techniques such as line feeds? What are your thoughts on doing something like this? There is some email-type checking in here, but it gives the general idea.

    Code:
    if($_REQUEST)
     {
    //  SANITIZE $_REQUEST..............
    $find = array("/bcc\:/i","/Content\-Type\:/i","/cc\:/i", 
              "/to\:/i", "/%0a/i", "/%0d/i", "/MIME\-Version\:/i");
    $replacements = array("[Blind Cx.Cx.]",
                    "[possibly harmful content removed...]",
                        "[Cx.Cx.]",
                         "[possibly harmful content removed...]",
                            "[possibly harmful content removed...]",
                              "[possibly harmful content removed...]",
                                 "[possibly harmful content removed...]");
    
    ksort($find);
    ksort($replacements);
    $_REQUEST =  preg_replace($find, $replacements, $_REQUEST);
      }


  •  

    Posting Permissions

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