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 database injection

    i am helping a friend to build a forum website which uses php and mysql database.

    i am working on the registeration page for the forum website and its validation. i am using php 5.2.5

    i am able to validate and do other tasks, however i really need help as i am stuck with regards to database injection.

    please answer the following questions. any help will be greatly appreciated.

    1. USER NAME VALIDATION

    username = eregi("^[a-zA-Z0-9_ ]+$", $username)

    with the above validation, a user can enter letters uppercase, lowercase and numbers and underscore with spaces ONLY ex=

    9abc_def OR _abc123 = this IS INCORRECT

    however i would like the username to be Letters First(upper or lowercase), followed by numbers and underscore and spaces in the username.

    ex= abcd1234 OR ABcd1234 OR Ab_12 OR ab 12_cd OR 123456 OR 123abc = this IS CORRECT

    i have used with preg_match as => if( $username == "" || !preg_match('/^[a-zA-Z0-9_]+$/x', $username) ) however its the same as eregi

    QUESTION = how can i rewrite username = eregi("^[a-zA-Z0-9_ ]+$", $username) to match the following requirement.
    username = abcd1234 OR ABcd1234 OR Ab_12 OR ab 12_cd OR 123456 OR 123abc also with eregi("^[a-zA-Z0-9_ ]+$", $username) as there is a space if a user has more than 1 space ex= "ab cd 12" it is still accepting is there a way to restrict to ONE space only
    ex = "ab cd12"

    2. USING mysql_real_escape_string() METHOD

    i am able to validate username, first name, phone numbers based on preg_match for these individual ones, however the form consists of some optional fields which i am not validating so if a user enters invalid characters in these optional fields i need to protect from sql injection, presently my code for mysql_real_escape_string() is as follows and the special characters
    are still appearing in the database. i have not used mysql_real_escape_string() before so i guess i am missing something

    $conn = mysql_connect($hostname, $user, $dbpassword);

    $insertquery = sprintf("INSERT INTO tablename (`username`, `password`, `firstname`) VALUES ('%s', '%s', '%s')",

    mysql_real_escape_string($username, $conn), mysql_real_escape_string($password, $conn), mysql_real_escape_string($firstname, $conn));

    should i be checking for if(get_magic_quotes_gpc()) { } first.

    NOTE = by using this mysql_real_escape_string() method php should NOT add slashes or other characters if this happens then the username will be stored in the table differently ex= john`smith instead it should be johnsmith the slashes can be done for other fields like firstname etc as this username and password will be used by a user to login to the forum please advice about the procedure for mysql_real_escape_string() method

    3. QUESTION ABOUT SQL INJECTION

    presently if i enter special characters in the form these values are being inserted to the database as it is which is not good. out of the following methods htmlentities(), addslashes(), trim(), mysql-real-escape-string() which is the best method to use to avoid sql injection
    i think mysql-real-escape-string() is the best method.
    NOTE = in my php settings magic_quotes_gpc is ON, magic_quotes_runtime is OFF, magic_quotes_sybase is OFF

    4. STORING PASSWORDS

    as part of the registration for the forum the username and password that the user enters in the registration page will be used as their username and password to login to the forum. presently when i execute the sql insert statement along with other fields for the registration page the value of the password stored in the mysql table is the actual characters that a user entered in the form. in the form the element is defined as <input type="password" name="password"> however in the table the password is stored as the actual characters the user entered in the form. is this a right way of storing the password field from the form.

    NOTE = i believe with websites that are forum based using php and mysql, there is a way to pass information to the php file which will automatically pick up the username and password from the table that i have created where i am storing the username and password.

    Please comment on storing the password in mysql table and how i can find the php file to which i can pass the value of username and password as a variable by using a function to that php and by including that php file in which i am processing the registration form.

    Thanks a lot for reading my post. Any help will be greatly appreciated.

  • #2
    New Coder
    Join Date
    Dec 2005
    Posts
    35
    Thanks
    1
    Thanked 1 Time in 1 Post
    Bit too tired to answer them all for now but for 4. use md5 to encrypt the passwords, then on login, you encrypt that they have entered then compare what the user typed, with what is stored.

  • #3
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    Late here as well, but let's have a go at it...



    1. USER NAME VALIDATION

    This will demand a beginning letter, then any combination of letters, numbers, underscores, and spaces thereafter.
    PHP Code:

    preg_match
    '/^[a-z]+[\s\w]*$/i'$username ); 


    2. USING mysql_real_escape_string() METHOD
    PHP Code:
    // Near the top of script, before any $_POST value is processed
    if ( get_magic_quotes_gpc() )
    {
        foreach ( 
    $_POST as $k => $v )
        {
            
    $_POST[$k] = is_array$v )
                ? 
    array_map'stripslashes'$v )
                : 
    stripslashes$v );
        }

    Now $_POST values will contain the original submitted data, no escaping characters.



    3. QUESTION ABOUT SQL INJECTION

    mysql_real_escape_string() is the most complete and fail-safe method, but always validate types, lengths, characters, etc, to be sure you are accepting the types of info you want. If you want a number, check with ctype_digit(), and then cast it as an integer.. no need for mysql_real_escape_string() on numbers. If you are saving a state or country abbreviation, check for a string that is only two letters -- preg_match( '/^[a-z]{2}$/i', $state ) -- and nothing else. No need for mysql_real_escape_string() here either if you've identified the string as containing only letters.

    Don't store data after running it through htmlentities(). Save it as-is, then display it with htmlentites(). If you save it with the converted entites, you will use much more space in the long-run as an encoded entity can be 3-6 times larger than the original character, which means a larger database size.

    addslashes() is worthless when saving info to the database... even more-so than trim() as it adds characters to input that is supposedly already validated, making the stored info larger. trim() is probably not bad if you feel like it but it will not make it any more secure or insecure.



    4. STORING PASSWORDS

    As mention, use md5(), or sha1().

    If you use md5(), make the password field type CHAR( 32 ). If using sha1(), make it CHAR( 40 )... as they will always be those respective lengths... no more, no less.

    There is no need to run the values through mysql_real_escape_string() as these encryptions will only return alphanumerals.
    Last edited by kbluhm; 03-17-2008 at 03:04 PM. Reason: Fixed some typos resulting from a late night of typing.

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,038
    Thanks
    2
    Thanked 316 Times in 308 Posts
    Prepend or append a nonsense salt string to the password before performing any md5() or sha1() hash on it to prevent any table lookup/brute force method from giving back an original value that can be used to log in with.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    Regular Coder
    Join Date
    Sep 2007
    Posts
    137
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks for letting me know about the password. i have presently defined the password field in the mysql table as varchar and specified 250 as the length.

    1. is it ok to define the password field as varchar or should it be a specific datatype
    2. is there any specific restriction on the character length for password field irrespective of the datatype.

    please advice.

    thanks.

  • #6
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Quote Originally Posted by sudhakararaog View Post
    1. is it ok to define the password field as varchar or should it be a specific datatype
    2. is there any specific restriction on the character length for password field irrespective of the datatype.

    please advice.

    thanks.
    Quote Originally Posted by kbluhm View Post
    4. STORING PASSWORDS

    As mention, use md5(), or sha1().

    If you use md5(), make the password field type CHAR( 32 ). If using sha1(), make it CHAR( 40 )... as they will always be those respective lengths... no more, no less.

    There is no need to run the values through mysql_real_escape_string() as these encryptions will only return alphanumerals.
    This has already been answered here.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.


  •  

    Posting Permissions

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