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