question about validation and sql injection

[sudhakararaog]

A) validating username in php

as part of a registration form a user fills there desired username and this is stored in a mysql. there are certain conditions for the username.

a) the username should only begin either letters or numbers, and Underscore character
example = user123, 123user, u_ser123, user_123 = completely case insensitive

b) a user may choose not to have an underscore or numbers sometimes. example = username

presently my validation for username is

PHP: Select all

$username = $_POST["username"];
if( $username == "" || !eregi("^[a-zA-Z0-9_]+$", $username) )
{
$error.="User name cannot be blank or has special characters";
} 

Question = how can i rewrite this php validation for username to meet the above criteria or is my validation correct

B) preventing sql injection

till now i have been capturing the form values and directly inserting into the table without considering sql injection however for this project as it is for a forum i would like to implement prevention of sql injection. from what i have read about preventing sql injection there are several steps that need to be followed,

htmlentities
addslashes
trim
mysql-real-escape-string
magic_quotes_gpc is ON
magic_quotes_runtime is OFF
magic_quotes_sybase is OFF

as i have not done preventing sql injection i am not sure what is the correct process.

Question =

a) please advice a step by step process of how to go about avoiding the sql injection before the insert sql query is executed starting from
$username = $_POST["username"]; till the
insert into tablename(field1, field2) values($value1, $value2) SQL query is executed which will prevent sql injection even if the user enters any special characters while filling the form.

b) should i consider the setting of magic quotes as in should it be ON or OFF or should i ignore it if so should it be
ON or OFF

c) also with the prevention methods if a user types a special character in the data will that character be written in the table as a escaped character or how does it store those special characters

d) a very important point here, i have a feature where a user can check if a username is available or not. so while storing a username if the username is stored as john\smith in mysql and if the user is searching for johnsmith this would not match, so even in the table the username should be stored without slashes as i have to read the username and compare with what the user has typed to see if they both are same or different.

please advice if i have missed any other steps to prevent sql injection.

thanks a lot for your help.

[CloudedVision]

for a, can't you just test it out to see if it works?

and for b, magic quotes can cause problems, but if its off use mysql_real_escape_string(). if its on, turn it off and use mysql_real_escape_string().

[sudhakararaog]

with the following validation

$username = $_POST["username"];
if( $username == "" || !eregi("^[a-zA-Z0-9_]+$", $username) )
{
$error.="User name cannot be blank or has special characters";
}
this works fine however

1) if a user types "underscore" more than once the validation is accepting 2 underscores which i do not want. i want the user to enter only 1 underscore and letters and numbers can be repeated.

2) also if a user enters "underscore" as the first character it is still accepting. ex: _username. i do not want the underscore to be the first character

how can i rewite the above validation to mee my requirement.

[CloudedVision]

the problem with that is that regular expressions are not PHP, and we don't have a forum for regex, so the Webforumz Cafe would be the best place. Would you like me to move it there?

[ccandeland]

I have read its ok just to have alpha numeric input making it much less complicated. It will stop any sql injection and bots trying to find the password will have to search each word in there dictionary hundreds of times

[hschmitz]

Hi

As CloudedVision said this is a regex problem.

I would modify the line

Code: Select all

if( $username == "" || !eregi("^[a-zA-Z0-9_]+$", $username) )

to this

Code: Select all

if( $username == "" || !eregi("^[a-zA-Z0-9]+_?[a-zA-Z0-9]*$", $username) )

This checks for at least one alphanumeric character followed by an optional underscore followed by an arbitrary number of alphanumeric characters.

Let me know if it works