sql injection prevention

[oliverjohnstone]

hi im really new to sql and php but i was told that i needed to prevnt mysql injection and i dont really want to get it wrong i have done some reserch and aparently mysql_real_esca pe_string() should prevent it but im not quite sure were to put it if anyone could help me pls do thank you

here are my scripts

login
[code=php]
<?php
include 'dbc.php';

$full_name = mysql_real_esca pe_string($_POS T['email']);

if ($_POST['Submit']=='Login')
{
$md5pass = md5($_POST['pwd']);

$sql = "SELECT id,full_name FROM users WHERE
full_name = '$full_name.eas ehosting.co.uk' AND
user_pwd = '$md5pass' AND user_activated= '1'";

$result = mysql_query($sq l) or die (mysql_error()) ;
$num = mysql_num_rows( $result);
if ( $num != 0 ) {
session_start() ;
list($user_id,$ full_name) = mysql_fetch_row ($result);
$_SESSION['user']= $full_name;
if (isset($_GET['ret']) && !empty($_GET['ret']))
{
header("Locatio n: $_GET[ret]");
} else
{
header("Locatio n: /~$full_name/index.php");
}
//echo "Logged in...";
exit();
}

header("Locatio n: invalid.html");
//echo "Error:";
exit();
}

?> [/code]

register
[code=php]
<?php
session_start() ;


include ('dbc.php');


if ($_POST['Submit'] == 'Register')
{
if (strlen($_POST['email']) < 5)
{
die ("Incorrect email. Please enter valid email address..");
}
if (strcmp($_POST['pass1'],$_POST['pass2']) || empty($_POST['pass1']) )
{
//die ("Password does not match");
die("ERROR: Password does not match or empty..");

}
if (strcmp(md5($_P OST['user_code']),$_SESSION['ckey']))
{
die("Invalid code entered. Please enter the correct code as shown in the Image");
}
$rs_duplicates = mysql_query("se lect id from users where full_name='$_PO ST[full_name].easehosting.co .uk'");
$duplicates = mysql_num_rows( $rs_duplicates) ;

if ($duplicates > 0)
{
//die ("ERROR: Account already exists.");
header("Locatio n: register.php?ms g=ERROR: Account already exists..");
exit();
}




$md5pass = md5($_POST['pass2']);
$activ_code = rand(1000,9999) ;
$server = $_SERVER['HTTP_HOST'];
$host = ereg_replace('w ww.','',$server );
mysql_query("IN SERT INTO users
(`user_email`,` user_pwd`,`coun try`,`joined`,` activation_code `,`full_name`)
VALUES
('$_POST[email]','$md5pass','$ _POST[country]',now(),'$activ _code','$_POST[full_name].easehosting.co .uk')") or die(mysql_error ());
mkdir ("/var/www/hosts/$_POST[full_name].easehosting.co .uk", 0700);
mkdir ("/var/www/hosts/$_POST[full_name].easehosting.co .uk/docs", 0700);
mkdir ("/var/www/hosts/$_POST[full_name].easehosting.co .uk/CGI-BIN");
copy("/var/www/hosts/template/dbc.php","/var/www/hosts/$_POST[full_name].easehosting.co .uk/dbc.php");
copy("/var/www/hosts/template/index.php","/var/www/hosts/$_POST[full_name].easehosting.co .uk/index.php");
copy("/var/www/hosts/template/manage.php","/var/www/hosts/$_POST[full_name].easehosting.co .uk/manage.php");
copy("/var/www/hosts/template/docs/index.html","/var/www/hosts/$_POST[full_name].easehosting.co .uk/docs/index.html");
$file = fopen("/var/named/chroot/etc/named.conf", "a");
fwrite($file,"
zone '$_POST[full_name].easehosting.co .uk' {\n type master;\n file '/var/named/$_POST[full_name].easehosting.co .uk.hosts';\n };");
fclose($file);

$message =
"Thank you $_POST[full_name] for registering an account with $server. Here are your login details...\n\n
############### ############### ############### ############### ############### \n
Your login name is: $_POST[full_name] \n
Your domain name is: http://$_POST[full_name].easehosting.co .uk\n
This email is to confirm that you have created an account with $server\n
you can now login at http://www.easehosting .co.uk/login.html
############### ############### ############### ############### ############### \n

Thank you. This is an automated response. PLEASE DO NOT REPLY.
";

mail($_POST['email'] , "Login details", $message,
"From: \"Auto-Response\" <notifications@ $host>\r\n" .
"X-Mailer: PHP/" . phpversion());
unset($_SESSION['ckey']);
echo("Thank you $_POST[full_name], Registration was Successful! Your domain name is $_POST[full_name].easehosting.co .uk\n you can now login at www.easehosting .co.uk...");

exit;
}

?>[/code]

forgot
[code=php]
<?php
include 'dbc.php';
if ($_POST['Submit']=='Send')
{
$host = $_SERVER['HTTP_HOST'];
$rs_search = mysql_query("se lect user_email from users where user_email='$_P OST[email]'");
$user_count = mysql_num_rows( $rs_search);

if ($user_count != 0)
{
$newpwd = rand(1000,9999) ;
$host = $_SERVER['HTTP_HOST'];
$newmd5pwd = md5($newpwd);
mysql_query("UP DATE users set user_pwd='$newm d5pwd' where user_email='$_P OST[email]'");
$message =
"You have requested new login details from $host. Here are the login details...\n\n
User login: $_POST[full_name] \n
Password: $newpwd \n
_______________ _______________ ______________
*** LOGIN ***** \n
To Login: http://$host/login.php \n\n
_______________ _______________ _______________
Thank you. This is an automated response. PLEASE DO NOT REPLY.
";

mail($_POST['email'], "New Login Details", $message,
"From: \"Auto-Response\" <robot@$host>\r \n" .
"X-Mailer: PHP/" . phpversion());

die("Thank you. New Login details has been sent to your email address");
} else die("Account with given email does not exist");

}
?>[/code]

thank you
oliver

[Atli]

Hi.

Generally, to avoid SQL Injection, you need to validate any piece of data that is going to go into a dynamically created SQL query.

For example, if I were to accept a username and a password from a HTML form to be checked against a database of users, you may be tempted to do this:
[code=php]
<?php
$name = $_POST['username'];
$pwd = $_POST['password'];

$sql = "SELECT `UserID` FROM `User`
WHERE `UserName` = '{$name}'
AND `Password` = '{$pwd}'";
$result = $mysql_query($s ql) or die(mysql_error ());

if($result && mysql_num_rows( $result) > 0) {
echo "User is valid!";
}
else {
echo "User is invalid";
}
?>[/code]
Fairly simple, right?
And this would work perfectly for typical users.

But consider what would happen if you were to pass this as the password:

' OR 1='1

Now your query becomes:
[code=mysql]SELECT `UserID` FROM `User`
WHERE `UserName` = 'something'
AND `Password` = '' OR 1='1'[/code]
Which would return every single row in the table and pass the validation, regardless of which username you passed.

The safest way to prevent this sort of tampering is to make sure the data passed does not contain any characters that might mess with the query.
Which is exactly what the mysql_real_esca pe_string function does.

If I were to change lines number 2 and 3 in the example above like so:
[code=php]
$name = mysql_real_esca pe_string($_POS T['username']);
$pwd = mysql_real_esca pe_string($_POS T['password']);[/code]
And pass it the same bogus data I did before, this is the query that would get executed (from PHP's point of view):
[code=mysql]SELECT `UserID` FROM `User`
WHERE `UserName` = 'something'
AND `Password` = '\' OR 1=\'1'[/code]
As you see, the quote-marks inside the Password field are now escaped, which means they are now a part of the value, rather than used to close the string and add an extra OR clause.

So, unless there is actually a user named 'something' using that password, the query would return 0 rows and the validation would fail.

Another simple way to lessen the risk of simple tampering like that is to group boolean expressions together. Consider if my query had looked like:
[code=php]
$sql = "SELECT `UserID` FROM `User`
WHERE (`UserName` = '{$name}')
AND (`Password` = '{$pwd}')";[/code]
Passing the bogus data, un-validated, into this would produce:
[code=mysql]SELECT `UserID` FROM `User`
WHERE (`UserName` = 'something')
AND (`Password` = '' OR 1='1')[/code]
Which would also fail if the username is invalid.
Granted, this is far from being adequate by itself, but every little bit helps :]

[oliverjohnstone]

thank you

thank you that was a big help its grate that you have taken the time to explain it to me and i know understand how it works

Thank you

[Atli]

I'm glad I could help :)