Re: question about database injection
- From: Michael Fesser <netizen@xxxxxx>
- Date: Sat, 15 Mar 2008 16:18:12 +0100
..oO(Sudhakar)
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)
ereg_* is dead and will be removed in PHP 6. You should use the preg_*
functions, which is also what I use in the example patterns below.
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
/^[a-z][a-z\d_ ]*$/i
Starts with a letter, followed by any arbitrary number of letters,
digits, underscores or spaces, all case-insensitive.
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"
/^[a-z](?:[a-z\d_]| [a-z\d_])*$/i
But I would probably just trim down all whitespace to single spaces with
preg_replace():
$username = preg_replace('/\s+/', ' ', trim($username));
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
If you can't disable magic quotes, check get_magic_quotes_gpc() first
and call stripslashes() on all the inputs if they are are enabled to get
the raw data. Then just use mysql_real_escape_string() like above.
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.
Correct. Or learn how to use PDO and prepared statements.
NOTE = in my php settings magic_quotes_gpc is ON,
magic_quotes_runtime is OFF, magic_quotes_sybase is OFF
Turn them off completely. Magic quotes are dead.
Another thing is to use htmlspecialchars() when you ouput your data
again to an HTML page. There are some chars that have a special meaning
in HTML and might break your markup if they appear in your text. Using
this functions also avoids another kind of attack (XSS, cross-site-
scripting).
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.
No. Passwords should never be stored as plain text, but as a hash
instead, for example as MD5 (google for "salted hash" for improved
security if necessary). Then when a user tries to login, take their
password, hash it and compare it with the stored hash in the DB.
Micha
.
- Follow-Ups:
- Re: question about database injection
- From: Rik Wasmus
- Re: question about database injection
- References:
- question about database injection
- From: Sudhakar
- question about database injection
- Prev by Date: newbie: configuration of MySQL in PHP5 (Windows XP)
- Next by Date: Re: How Can i Connect More then one database
- Previous by thread: question about database injection
- Next by thread: Re: question about database injection
- Index(es):
Relevant Pages
|
