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"

  1. 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

  1. 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

  1. 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.

    however i would like the username to be Letters First(upper or lowercase), followed by numbers and underscore and spaces in the username.
    ...
    is there a way to restrict to ONE space only ex = "ab cd12"

    Tentatively, I would suggest:

    preg_match('/[a-z]\w* ?\w+/i', $username)

    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.

    Those special characters are supposed to appear in the database - you are not validating the input, remember? SQL injection happens when the input is not stored into the database, but instead is an SQL statement that is executed by the database server. mysql_real_escape_string() and other escaping mechanisms escape the input such that this unintended SQL statement execution is avoided.

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

    Yes. If magic_quotes_gpc() is on, you either do not use mysql_real_escape_string(), or you simply abort the script and insist that magic_quotes_gpc be turned off. The latter is more draconian, but potentially safer.

    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.

    Yes, mysql_real_escape_string() is the best method for use with the MySQL extension. htmlentities() is useful when you want to print HTML code.

    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.

    It is acceptable, but if your database is compromised, your users' passwords will also be compromised. Since users often reuse username and password combinations (or an attacker can figure a user out by a public email address), this could lead to users' accounts in other websites being compromised. As an extra layer of protection for your users, you should store a cryptographic hash of their passwords instead. Read this article on Password Hashing for implementing the minimum protection required. Here is another article that you may want to read: Enough With The Rainbow Tables: What You Need To Know About Secure Password Schemes.

    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.

    Instead of comparing the original password with the password supplied on login, you would calculate the hash of the password supplied, and compare it with the stored hash of the original password.

      laserlight wrote:

      ...
      Yes. If magic_quotes_gpc() is on, you either do not use mysql_real_escape_string(), or you simply abort the script and insist that magic_quotes_gpc be turned off. The latter is more draconian, but potentially safer.
      ...

      If turning magic_quotes_gpc off is not an option (even on a shared host you can turn it off locally via .htaccess if on an Apache server), or if you need your application to be portable to any environment, simply check to see if it is turned on via get_magic_quotes_gpc(), and if it is then undo the damage via stripslashes() and then apply your SQL escaping mechanism (e.g. mysql_real_escape_string()).

        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.

          You should determine minimum/maximum lengths, characters allowed, etc. and validate the user's input, rejecting it if it does not meet that criteria. The size and type of column will then depend on how you will be hashing or encrypting it, and possibly the above-mentioned size limitations. Different hashing algorithms will typically generate a fixed-length string of characters, so you just need to read up on whichever method you are using to find out how long a field you need. Some other methods (in particular encryption algorithms) are better stored as a BLOB, as they generate what is in effect a binary value which does not work well as a character type field.

          For instance, the MySQL MD5() function returns a 32-character string of hexadecimal digits, so can be stored as CHAR(32), while the AES_ENCRYPT() function returns a binary string, so should be stored in a BLOB. (The link includes a formula to determine what the max size will be, so you can use that to determine what type of BLOB you will need.)

            Write a Reply...