question about database injection

[runway27]

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.

[rohypnol]

1.
You make absolutely no sense. Please give a few examples of valid usernames, which should cover all possible situations (starting with letters/numbers/etc).

2.
mysql_real_escape_string() works only if you have a connection open to the db you're working on. That's because it will ask the MySQL server which characters aren't safe. If you see "weird" chars in the database, that doesn't mean they're weird for MySQL. mysql_real_escape_string() works in association with the MySQL server so they should be fine.

3.
Avoid MySQL injection: sanitize all values when performing queries using mysql_real_escape_string()
Avoid HTML (and JavaScript) injection: sanitize all values before you output them to the user using htmlentities()
You should also see the value of get_magic_quotes_gpc() and get_magic_quotes_runtime(), which should tell you if you need to call stripslashes() on ALL user input BEFORE you use it in any way.

4.
In the end you don't make any sense, again.
For storing passwords, you should always perform a hash with a salt. A very simple method is the following:
- you have a HTML form
- your form has an onsubmit event, which encrypts the password before it's sent
- usually the encryption is done by setting the value of a hidden input field with the hash
- the simplest way to do this is find an implementation of md5 or (preferably) sha1 for javascript, append a salt to the password, perform the encryption and move the result to the hidden field[php]
<form .. onclick="return encryptPass()">
<input type="hidden" id="salt" value="some_random_string">
<input type="hidden" id="encrypted_pass" name="encrypted_pass">
<input type="password" id="pass">
</form>
<script type="text/javascript">
function sha1(text)
{ .. }
function encryptPass()
{
document.getElementById('encrypted_pass').value = sha1(document.getElementById('pass').value + document.getElementById('salt').value);
document.getElementById('pass').value = '';
return true;
}
</script>[/php]
In this, "some_random_string" should be a random string which you will never change again. For example, you could use "kjhg94um093u." This is done to protect the users' password in case you website gets cracked, so the cracker will not be able to find out what the password is and try to use it on other websites where the users might have an account. Also, this ensures the user's real password isn't sniffed on it's way to your server. There's not much you can do on your side, but there are some things you can do for the users, to prevent any major damage from being done to them if your servers are ever cracked.
It is also a good idea to perform another hash on the server side, so if anyone somehow manages to read the contents of the table, they still won't be able to use it. So on the server, when you check user's identity or adding him to the database, you use sha1($_REQUEST['encrypted_pass'] . 'kghwerhg').

All this offers just a little protection, but believe me, it's a lot better than nothing. For the best protection, you store a hash of the password and use https when it comes to registering/authentication, but only few sites actually do this.

[ronverdonk]

rohypnol

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR