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 ysql_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.
Somebody else will answer your other questions, but I'll deal with #4 regarding passwords. Never never store passwords as plain text. If somebody compromises your code, they'll be able to download those passwords. You should always use some sort of encryption mechanism. A really common one is encode(), as you can use decode() with it. More advanced websites will use md5 encryption, hashing, or even 64 or 128 bit encryption methods.
To clarify your first question: What exactly do you want to allow? You want the first character to be a letter (upper or lower case) or a number but not an underscore, correct? You want users to be able to have a space (but only one) in the whole username? Do they have to have a space? 2. You want to escape the variables individually. See the examples in the manual: $query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'", mysql_real_escape_string($user), mysql_real_escape_string($password)); ?> PHP: You should not create the query string and then try to escape it. Escape the data as you are putting it into the query string. 3. Use mysql_real_escape_string() if you're using a mysql database. htmlentities() is for displaying. addslashes() is general purpose escaping. trim() is for removing characters at the beginning and end of a string. 4. As mentioned by the above posts, never store passwords in plain text. As you store them, use md5 or sha1 or similar with a salt. Then, when you are checking if they are logging in with the correct password, you use the exact same process to end up with the same hash for comparison.
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.
varchar 250 will work just make sure to eventually add a md5 encryption to your passwords when they're put into the database. example: $sql->add_field('user_password','md5'); also dont forget to check to make sure doubles dont exist. Otherwise you'll have users with the same email/user name and password combinations or, users signing up to multiple accounts with different user names and yet same email.
thanks for letting me know about the password. i have used md5 to encrypt the password and i am inserting the encrypted password into the mysql table. presently what i am doing is, before inserting any information in the table i am checking to see if the username and email already exists, if so the user cannot complete the registration form and has to choose a different username. this way i am able to have unique usernames and email address. my question is should the password also be unique like username and email address or can the password be same as atleast the username and email are unique. please advice. thanks.
The usernames and email addresses should be unique. The password should not be checked for uniqueness. What are you going to say, "I'm sorry, the password you entered is already in use by someone. Please do not try to hack the account..."