SQL injection protection

[Tezread]

I was wondering will this:

<?php 
include("include/session.php"); 
$username = $_SESSION['username'];
$totalscore = $_SESSION['totalscore'];
if(isset($_POST['submit'])){ 

$query = "SELECT * FROM qualityuser WHERE username = '".$username."'";         

        $result = mysql_query($query); 
        $num = mysql_num_rows ($result); 
        if ($num == 1) { ......
else
 if ($num == 0) { ......


$sql = "INSERT INTO qualityuser SET qualityuser_id =  NULL ";
	foreach ($_SESSION['formdata1'] as $col =>$val ){
		if($col <> 'submit'){
		$sql .=", $col = '$val' ";
		}

be protected from SQL injection attacks if I simply add:

<?php 
include("include/session.php"); 
$username = $_SESSION['username'];
$totalscore = $_SESSION['totalscore'];
if(isset($_POST['submit'])){ 

$query = "SELECT * FROM qualityuser WHERE username = '".$username."'";         

        $result = mysql_query($query); 
        mysql_real_escape_string($username);

    $num = mysql_num_rows ($result); 
    if ($num == 1) { ......
else
 if ($num == 0) { ......


$sql = "INSERT INTO qualityuser SET qualityuser_id =  NULL ";
	foreach ($_SESSION['formdata1'] as $col =>$val ){
		if($col <> 'submit'){
		$sql .=", $col = '$val' ";
		}
mysql_real_escape_string($username);

[Piranha]

No. The code is run with the first row first, then the second and so on. What you do here is to set the query, run the query and THEN (when the harm is already done) you use mysql_real_escape_string. And you are not storing the value from mysql_real_escape_string anywhere. Google, look in the manual and search these forums to see how to use it.

[Tezread]

Thankyou for the hiunt their Piranha. I had a look on php.net as well as the archives and I think I see a away if having an include fle that could help out:

include connect.php

$user = "********";
$host = "*******";
$password = "*******";
$connection = @mysql_connect($host, $user, $password)
or die ("Couldn't connect to server. Contact the Administrator on [e-mail]");
$database = "*********";
$db = @mysql_select_db($database, $connection)
or die ("Couldn't select database. Contact the Administrator on [e-mail]");

function quote_smart($value) {
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
if (!is_numeric($value)) {
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}
?> 

and then...

<?php 
include("connect.php");
include("include/session.php"); 
$username = $_SESSION['username'];
$totalscore = $_SESSION['totalscore'];
if(isset($_POST['submit'])){ 

include("connect.php"); 


$message = NULL; 
$score = 0; 
$score_pol_proc = 0; 
//validate your form data 

if (empty($_POST['firstname'])) { 

    $message .= '<p>You forgot to enter your first name!</p>'; 
} 

// Check for an second name. 
if (empty($_POST['secondname'])) { 

    $message .= '<p>You forgot to enter your second name</p>'; 
} 

if (empty($_POST['organisation'])) { 

    $message .= '<p>You forgot to enter your organisation</p>'; 
} 

if(count($message) > 0){ 
      //hold the error in session so you can display the error in your form page. 
      $_SESSION['message'] = $message; 


//redirect to the form 
header("Location: http://www.mindseyemidlands.co.uk/notts_quality/info_resource/selfassess_part1a_v1.php");        
} 

else { 
$query = "SELECT * FROM qualityuser WHERE username = '".$username."'";         

        $result = mysql_query($query); 
        $num = mysql_num_rows ($result); 
        if ($num == 1) { 

$title = $_POST['title']; 
$firstname = $_POST['firstname']; 
.....etc


$totalscore = $score - $score_pol_proc;
$_SESSION['totalscore'] = $totalscore;

header("Location: http://www.mindseyemidlands.co.uk/notts_quality/info_resource/selfassess_part1b_v1.php"); 
}


	if ($num == 0) { 
$_SESSION['formdata1']=$_POST;
$username = $_SESSION['username'];

// build insert query from session formdata
	$sql = "INSERT INTO qualityuser SET qualityuser_id =  NULL ";
	foreach ($_SESSION['formdata1'] as $col =>$val ){
		if($col <> 'submit'){
		$sql .=", $col = '$val' ";
		}

}
mysql_query($sql, $connection) or die(mysql_error()); 

[ClarkF1]

you aren't using quote_smart anywhere in the second file so it is still vulnerable.

You need to do something like:

$username = quote_smart($username);

before the first query and

$sql .=", $col = '".quote_smart($val)."' ";

in the loop

[Tezread]

is this protected from sql injection attacks now??
include connect.php

$user = "********";
$host = "*******";
$password = "*******";
$connection = @mysql_connect($host, $user, $password)
or die ("Couldn't connect to server. Contact the Administrator on [e-mail]");
$database = "*********";
$db = @mysql_select_db($database, $connection)
or die ("Couldn't select database. Contact the Administrator on [e-mail]");

function quote_smart($value) {
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
if (!is_numeric($value)) {
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}
?> 

and then...

<?php 
include("connect.php");
include("include/session.php"); 
$username = quote_smart($_POST['username']); 
$totalscore = quote_smart($_POST['totalscore']); 
if(isset($_POST['submit'])){ 

include("connect.php"); 


$message = NULL; 
$score = 0; 
$score_pol_proc = 0; 
//validate your form data 

if (empty($_POST['firstname'])) { 

    $message .= '<p>You forgot to enter your first name!</p>'; 
} 

// Check for an second name. 
if (empty($_POST['secondname'])) { 

    $message .= '<p>You forgot to enter your second name</p>'; 
} 

if (empty($_POST['organisation'])) { 

    $message .= '<p>You forgot to enter your organisation</p>'; 
} 

if(count($message) > 0){ 
      //hold the error in session so you can display the error in your form page. 
      $_SESSION['message'] = $message; 


//redirect to the form 
header("Location: http://www.mindseyemidlands.co.uk/notts_quality/info_resource/selfassess_part1a_v1.php");        
} 

else { 
$query = "SELECT * FROM qualityuser WHERE username = '".$username."'";         

        $result = mysql_query($query); 
        $num = mysql_num_rows ($result); 
        if ($num == 1) { 

$title= quote_smart($_POST[title]); 
$firstname= quote_smart($_POST[firstname]); 
$address1= quote_smart($_POST[address1]); 
////.....etc


$totalscore = $score - $score_pol_proc;
$_SESSION['totalscore'] = $totalscore;

header("Location: http://www.mindseyemidlands.co.uk/notts_quality/info_resource/selfassess_part1b_v1.php"); 
}


	if ($num == 0) { 
$_SESSION['formdata1']=$_POST;
$username = $_SESSION['username'];

// build insert query from session formdata
	$sql = "INSERT INTO qualityuser SET qualityuser_id =  NULL ";
	foreach ($_SESSION['formdata1'] as $col =>$val ){
		if($col <> 'submit'){
	                $sql .=", $col = '".quote_smart($val)."' "; 
		}

}
mysql_query($sql, $connection) or die(mysql_error()); 

[/QUOTE]

[ClarkF1]

It should be as far as I can tell

[Tezread]

Thankyou clarkf1-

[Tezread]

I have tried this on a scoring session system and it doesn't work out - the query doesn't insert data into the database:

form.php

<?php
include("include/session.php");
include("common_functions.php");
$totalscore = TRUE;
$_SESSION['formdata1']=''; 
$username = quote_smart($_SESSION['username']); 
$totalscore = $_SESSION['totalscore'];
include("connect.php"); 

// query 
$sql = "SELECT * FROM qualityuser WHERE username = '".$username."'"; 
$result = mysql_query($sql); 
// if we have rows, fetch them & prepopulate the form
if(mysql_num_rows($result) > 0) { 
   $row = mysql_fetch_array($result); 
} else { 
   $row = array('firstname'=>'','secondname'=>'','organisation'=>'','telephone'=>'','address1'=>'','address2'=>'','town'=>''
   ,'postcode'=>'' ,'registeredcharity'=>'','incorporated'=>'','regionalassociation'=>'','regionalcharity'=>'','project'=>''
   ,'nature_selfhelp'=>'','nature_frontline'=>'','nature_intermediary'=>'','nature_infrastructure'=>'','nature_partnership'=>''
   ,'involved_commitee'=>'','involved_volunteers'=>'','involved_paid'=>'','funded_unrestrictedfunds'=>'','funded_localcouncil'=>''
   ,'funded_undercontract'=>'','funded_grants_fees'=>'','funded_socialenterprise'=>'','activity_care'=>'','activity_advice_individual'=>''
   ,'activity_advice_organisations'=>'','activity_specialist'=>'','activity_community'=>'','activity_awareness'=>'','activity_sport'=>''
   ,'activity_mutual_support'=>'','have_policies_procedures'=>'','have_management_structure'=>'','have_communication'=>'','have_supervision'=>''
   ,'have_strategy'=>'','have_aims'=>'','have_review'=>'');
} 
?>

processor.php

<?php 
error_reporting(E_ALL); 
include("include/session.php"); 
include("common_functions.php"); 
$username = quote_smart($_SESSION['username']);
$totalscore = $_SESSION['totalscore'];
if(isset($_POST['submit'])){ 

include("connect.php"); 

$message = NULL; 
$score = 0; 
$score_pol_proc = 0; 
//validate your form data 


	if ($num == 0) { 
$_SESSION['formdata1']=$_POST;
$username = quote_smart($_SESSION['username']);

// build insert query from session formdata
	$sql = "INSERT INTO qualityuser SET qualityuser_id =  NULL ";
	foreach ($_SESSION['formdata1'] as $col =>$value ){
		if($col <> 'submit'){
		$sql .=", $col = '".quote_smart($value)."' "; 

	}

}
mysql_query($sql, $connection) or die(mysql_error()); 

//remove the form_error session 
unset($_SESSION['message']); 

//set up an array for answers to 'organisation types' 
$orgtype = array(); 

// add values, if they exist 
if(isset($_POST['registeredcharity'])) { $orgtype[1] = $_POST['registeredcharity']; } 
if(isset($_POST['incorporated'])) { $orgtype[2] = $_POST['incorporated']; } 
if(isset($_POST['regionalassociation'])) { $orgtype[3] = $_POST
////etc/////

// Create a grand total by taking $pol_proc_score away from $scorePrint the number of points: 
$totalscore = $score - $score_pol_proc;
$_SESSION['totalscore'] = $totalscore;


//redirect to result display page 
header("Location: http://www.mindseyemidlands.co.uk/notts_quality/info_resource/result.php");   

//define variables

}

?> 

result.php

<?php
include("include/session.php"); 
include("common_functions.php"); 
$username = quote_smart($_SESSION['username']); 
$totalscore = $_SESSION['totalscore'];  

?>

<?php
echo 'Thankyou <strong>'. $username . '</strong><br>'; 
echo 'You scored <strong>'. $totalscore . '</strong> points!'; 
echo '<br><br>';

if ($totalscore >= 26) echo 'xxxx';

////etc////;?>

common_functions.php

error_reporting(E_ALL ^ E_NOTICE); 
// Quote variable to make safe
function quote_smart($value)
{
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not integer
   if (!is_numeric($value)) {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}

i get no error messages but the record is not inserted so the score stays at 0