RE: [PHP] question about validation and sql injection

["Boyd, Todd M." <tmboyd1@xxxxxxxx>]

Sudhakar,

Bundling your parameters and using "prepared statements" will prevent
any and all SQL Injection from taking place, as the parameters
themselves will NEVER (repeat, NEVER) be considered a "part" of the
query. They are considered only to be data to be used in the query.

Example:

[code]
$dbc = mysqli_connect($host, $user, $pass, $db) or die("Couldn't
connect: " . mysqli_error());
$username = $_POST['user'];
$query = "insert into `mytable` values(?)"; // the ? represents where
your parameter will be bundled
$stmt = mysqli_stmt_init($dbc);

if(mysqli_stmt_prepare($stmt, $query))
{
	mysqli_stmt_bind_param($stmt, 's', $username); // the 's' means
"string value"
	mysqli_stmt_execute($stmt);
}
[/code]

Hope this helps! Here's a tutorial on prepared statements using
PHP/MySQL:
http://www.databasejournal.com/features/mysql/article.php/3599166 


Todd Boyd
Web Programmer


> -----Original Message-----
> From: Sudhakar [mailto:sudhakararaog@xxxxxxxxx]
> Sent: Thursday, May 15, 2008 5:26 PM
> To: php-general@xxxxxxxxxxxxx
> Subject: [PHP] question about validation and sql injection
> 
> 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_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.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php