question about validation and sql injection

[Sudhakar]

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

$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_gp c is ON
magic_quotes_ru ntime is OFF
magic_quotes_sy base 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(field 1, 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.

[Jerry Stuckle]

Sudhakar wrote:

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

$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

You should be using the preg functions instead. I'm not a regex expert,
by any means, but something like:

if ($username== "" || preg_match("/[^a-zA-Z0-9_]/", $username) 0 )
$error.="User name cannot be blank or has special characters";

>
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_gp c is ON
magic_quotes_ru ntime is OFF
magic_quotes_sy base 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(field 1, 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.

You need to validate all input. Numeric values need to be validated to
ensure they are really numeric. String values need to be primed with
mysql_real_esca pe_string().

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

Forget magic quotes. It's now more of a hassle then a help. It should
be 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

That's what mysql_real_esca pe_string() does.

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.

If you've parsed the username above correctly, it won't have a backslash
in it.

>
please advice if i have missed any other steps to prevent sql
injection.
thanks a lot for your help.

You need to understand SQL injection - which is really too much for a
newsgroup posting. Google for "SQL Injection". There are several good
hits out there.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[=?ISO-8859-1?Q?=22=C1lvaro_G=2E_Vicario=22?=]

Sudhakar escribió:

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

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

You'd better get used to preg_* functions, their old ereg_* equivalent
will be deprecated in PHP 6. Not tested but...

if(
// Check non-empty, only valid chars and 1 to 16 chars
!preg_match('/^[a-z0-9_]{1,16}$/i', $username) ||
// Check at least one letter
!preg_match('/[a-z]/i', $username)
){
$error .= '......';
}

I've added an extra check. Replace 16 with your database field length.
Probably, it could all be done in only one expression but...

B) preventing sql injection

htmlentities

This is used to convert plain text into HTML, it has nothing to do with
SQL injection.

addslashes

This may help to avoid _some_ types of injection in some database
servers like MySQL, but it wouldn't help in Oracle or SQL Server.

trim

I won't even comment on this.

mysql-real-escape-string

This is a valid option for MySQL. Prepared statements are the other way.

magic_quotes_gp c is ON

The same as addslashes(), except that you'll get \'s inserted all around
your input and you'll have to remove it again when not using it for a
SQL query:

<p>Welcome, John O\'Hara!</p>

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

Why does it matter how the DBMS stores data internally?

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.

You said users can only have letters, numbers and underscores... If you
also allow backslashes then of course "john\smith " is different to
"johnsmith" or "john_smith " or "john___smi th". My suggestion is that you
add an extra column to the table (possibly indexed) and check against it
as well:

Username StrippedUsernam e
============= =============== =
john_smith123 johnsmith
___123x___ x

$stripper_usern ame = strtolower(preg _replace('/[^a-z]+/', '', $username));


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--